April 26, 2005 at 4:08 am
I have a query that selects data from a SQL 7.0 database and inserts it into a SQL 2K database using SELECT INTO methodology.
I am executing this query by making SQL 7.0 as a Linked Server in SQL 2K and using 4 part naming convention. The problem is that the query takes more than 3.5 hours to execute. However, if I execute the query on SQL 7.0 and then do a DTS to SQL 2K database, the whole process takes about 6-7 minutes. This eliminates the network as a possible bottleneck.
I want to use Linked Server approach with DTC, but the response time is not acceptable. Any pointers as to why the query on Linked Server approach is taking so long and how can the performance be made better ?
Thanks in advance.
Prateek.
~~~~~~~~
"Remember: 80% of bugs are just undocumented features waiting to be discovered!"
April 26, 2005 at 5:30 am
I think the problem may the fact that the linked server is NOT using the indexes that you are anticipating. Is it possible to 1st build a VIEW or a TABLE with the data that you want SQL 2K to gather from 7.0 via a job on 7.0 and THEN have SQL 2k pick it up?
Good Hunting!
AJ Ahrens
webmaster@kritter.net
April 27, 2005 at 1:47 am
Have you considered OPENQUERY() ? If you can 'pull' the data onto the target server using OPENQUERY, everything will run on its own server, that is, the data selection will happen entirely on the source server and the insert will happen entirely on the target server - only the actual data to be transferred will cross the network. Otherwise, even if you are using the correct indexes, they may be being queried across the network with a consequent performance hit.
Incidentally, my understanding is that you should always use INSERT INTO ... SELECT rather than SELECT INTO. This is because the former only needs to lock the target table after deciding what to insert, while the latter takes its locks for the full query ... but I stand to be corrected on this!
April 27, 2005 at 1:54 pm
another recomendation:
try to set lazy_schema_validation "on" for that linked server using sp_serveroption
* Noel
April 27, 2005 at 3:33 pm
Another recommendation:
Don't use "select into". Or use:
Select top 0 Into Dest from Source
Insert Dest
select * from Source
Signature is NULL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply