Performance dts vs remote query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Performance dts vs remote query

Author

Topic

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-23 : 11:06:09

Overview
I have to download data from several (about 30) tables from an Oracle database daily.
When I came here it was dts packages with hard coded dates and lots of post processing steps.
I have changed this to a dts package for each download with the date as a parameter called from an sp which does the pre and post processing steps - the dts packages only do the data transfer.
This is a lot neater but I would like to get rid of the dts packages so that everything is self contained in the SP for which I can get change history and release control easily.
I'm not keen on dynamically changing the destination and transformations in a single package.

Question
Doing a couple of tests using openrowset against the transform data task with a query as source it seems that dts is quicker. I'm not sure though as I have no way of knowing what is happening on the Oracle server or the connecting network.
Does anyone have an opinion on this.
The transfer is into a staging database so doesn't need transaction control.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-23 : 11:12:03

Can you use the analyzer to see what's being executed when you run the dts and compare against the openrowset query.

Have you tried a linked server? Maybe the difference is the time it takes to setup the connection.

You probably already know that dts packages can be scripted as .bas files and put into source safe.

Also wondering if the DTS package is executing the queries on the Oracle server and the openrowset is bringing back the data then processing or vice versa.

Edited by - ValterBorges on 05/23/2003 11:14:43

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-23 : 11:29:59

I've run downloads that take a long time so the connection setup time will be minimal in comparison.

scripting - but creating the packages from scripts loses the graphics - might end up doing this though.

They are both executing on the oracle server - they must be because they run pl-sql not t-sql.

Haven't run the profiler yet to see what happens - I suspect that dts is delivering the data in 1000 row batches whereas the openrowset is buffering the complete recordset before the insert.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2003-05-23 : 14:34:02

I believe you are correct nr. I do a lot of work Oracle and SQL and it's been my observation that DTS seems to be faster because it pulls it in chunks.

I've tended to use linked servers with openquery to go get single rows when needed from Oracle and not duplicate the data in both systems. Performance is pretty good as long as the indexing is used right.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-23 : 14:58:56

Thanks.
I also use an SP to get stuff from oracle

create procedure sp_execOracle
@sql varchar(8000)
as
select @sql
select @sql =
'select * from
openrowset(''MSDASQL'',''DRIVER={Microsoft ODBC for Oracle};SERVER=OracleServer;UID=username;PWD=password'',
''' + replace(@sql,'''','''''') + ''')'
exec (@sql)
go

then
exec sp_execoracle 'select * from mytbl where charfld = ''hi'''
it displays the query sent followed by the resultset

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GreatInca
Posting Yak Master

102 Posts

Posted - 2003-05-23 : 22:29:38

Whether script or DTS, I would use staging tables to avoid redundant downloading of data from the remote data source and also have control of indexing (Remote data souce is ofen poorly indexed). SQL will oftn download the entire table (often with where clause and/or sort) and do the join locally if you use remote query. So its best to download it as a separate step unless you are not concernced about performance.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-24 : 20:40:04

I am using a staging dtabase of course.
It can't retrieve the data and execute the filter locally because it's a pl-sql query which sql server won't be able to execute.

If it is over a linked server then that might be a problem but not when it's just a select from an openrowset or from a dts datasource query. Both are always executed on the server connection.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources