Performance differences between the DRS and native stages

sbass1 · Post by **sbass1** » Sun Mar 15, 2009 5:00 am

Are there performance differences between:

1) DRS (Sybase database) and Sybase OC stage?
2) DRS (SQL Server database) and SQL Server stage?

Is the DRS stage just a wrapper around the native stages, i.e. if I chose the Sybase database in the DRS stage, does it use the same drivers as the Sybase stage?

Questions apply to both source (reads) and targets (writes)

Thanks...

chulett · Post by **chulett** » Sun Mar 15, 2009 8:09 am

To the best of my knowledge and from various discussions here (like this one which talks about why the stage came into existence) there really shouldn't be any difference in performance. It still uses the same ODBC or native drivers under the covers that the dedicated stages would use for that connection / dbms type.

sbass1 · Post by **sbass1** » Sun Mar 15, 2009 4:49 pm

Thanks Craig. And my apologies for not doing a better job of searching before posting.

I'm trying to develop my own best practice for which stage to use when connecting to a database.

To summarize your reply and the thread you linked to, I intend to:

* Use the DRS stage most of the time. There shouldn't be any performance difference between this and the native stage. [Pro: It's a consistent stage across all (well most) jobs. Con: the database type does not show in the UI.]
* Use the native database type (Sybase, SQL Server, etc.) in the DRS stage unless the source or target database only supports ODBC. IOW favour native over ODBC.
* Use ODBC on those rare occasions I need

eostic wrote:...the opportunity to write a job that spreads a single unit of work across a parent rdbms table, it's children, and perhaps 2 other related tables.......with a single commit

chulett · Post by **chulett** » Sun Mar 15, 2009 9:51 pm

When dealing with Oracle, I still prefer the OCI stage over DRS (even though they both use a native connection) for a couple of reasons. One is it has more 'update action' choices and two, it uses numbered bind parameters rather than the pita positional question marks like ODBC. But that's just me.

But as Ernie notes, the ODBC stage can be used for that 'single commit' because it doesn't enforce the 'all columns must be bound' rule that OCI insists on and can thus be used for a link that just does a commit. It was a indispensable trick he taught me to make an RTS enabled job actually work properly.

ray.wurlod · Post by **ray.wurlod** » Sun Mar 15, 2009 11:19 pm

Isn't pita a kind of bread?

sbass1 · Post by **sbass1** » Mon Mar 16, 2009 12:57 am

http://www.acronyms.ch/

http://www.acronyms.ch/searchResults;js ... hText=pita

Perhaps http://www.acronyms.ch/ can be added to the FAQ, which of course everyone reads before ever posting.

Yes, pita is a homonym for the acronymn p.i.t.a.

chulett · Post by **chulett** » Mon Mar 16, 2009 7:14 am

Ray is being... funny. You'll get used to his sense of humor. Eventually.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 16, 2009 3:08 pm

Bread in a suppository configuration?