CODEWITHSUNDEEP
sql-server
richard seung
richard seung

Reputation: 1

Speeding up [select * into table] across linked servers

I'm trying to copy a table with size ~50 million rows into another database on a link server. It does not have any indexes (although i wouldn't think that should make a difference). I've used the following query:

select * into [db2].[schema].[table_name] 
from 
openquery([linked_server_name],
'select * from [db1].[schema].[table_name]')

This took approximately 7 minutes.

This seems suspiciously long for what I intended to be a simple copy and paste. Am I missing something?

I need to run this on a regular basis and would ideally like to keep it as automated as possible (no manual copying tables across servers using SISS would be ideal)

Any ideas would be highly appreciated!

Thanks a bunch

Upvotes: 0

Views: 216

Answers (2)

DaveBally
DaveBally

Reputation: 113

There could be lots of different reasons at play here each having a cumulative effect on the 'slowness'.

Looking at the wait states will be the first port of call.

Indexing ( at least on the select side ) isnt an issue here , there is no predicate used ( at to a lesser extent using all the columns ) therefore how would you expect an index to be helpful ??

I would say that number of rows is not a helpful metric... How big in terms of MB / GB is the source data set ? Use the "Include Client Statistics" in SSMS to get an accurate nummber. Now, if that is 'big' how log does it take to drag a .zip file of the same size over the network ?

Upvotes: 1

Shikhar Maheshwari
Shikhar Maheshwari

Reputation: 409

Without the indexing of the table, the SELECT query is bound to be slow. Indexes help in improving the performance of the select query. The indexes slow down the INSERT queries as they have to add the data as well as index it simultaneously. On the contrary, the index creation boosts up the SELECT query.

Also, is your data copied transactional in nature or you can do away with dirty read? If dirty read is fine then you can use WITH(NOLOCK) in the SELECT query to avoid and transaction issues.

Upvotes: 0

Related Questions