April 9, 2008 at 12:51 pm
Im trying to insert some records into server B from server A via a linked server.
(eg. Insert into ServerB.DBName.dbo.TargetTable Select A,B,C From SourceTable.....)
Inserting locally takes about 1 minutes for 1million records.
Inserting into server B takes 16 minutes for 1 million records with the example above.
Interestingly it only take about 1 minute to insert into server B with DTS. Is there a way to get the same DTS performance with native T-SQL?
Paul
April 9, 2008 at 2:06 pm
In my experience, procs that move data from one server to another tend to be slow. It's the overhead for managing large transactions across the network.
I've generally had much better success with SSIS/DTS.
The one way I've found to get around this is to first move the data into a table variable, then move it to the other server. Since table variables bypass logging and transactions, they do seem to be faster for this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2008 at 2:40 pm
We've found the fastest way is to export from one database out to an SSIS RAW file and then back into the next database in the next server. Storing the RAW file on the destination server seems to work best.
If you really need to use TSQL, you can look at the BULK INSERT statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2008 at 7:10 pm
The one way I've found to get around this is to first move the data into a table variable, then move it to the other server. Since table variables bypass logging and transactions, they do seem to be faster for this.
is it advisable to store large dataset into table variables. I am of a view (may be wrong) that we should use table variable only when dataset is small.
April 9, 2008 at 7:14 pm
if you are moving millions or rows around, use a bulk method: BCP/Bulk Insert/SqlBulkCopy/SSIS. don't mess around with table variables.
---------------------------------------
elsasoft.org
April 10, 2008 at 7:07 am
But how do you copy data from TABLE to TABLE on a remote server
as mentioned in the original post?
BULK INSERT / BCP seem to load OS files only, am I correct?
April 10, 2008 at 7:12 am
helloanam (4/9/2008)
The one way I've found to get around this is to first move the data into a table variable, then move it to the other server. Since table variables bypass logging and transactions, they do seem to be faster for this.
is it advisable to store large dataset into table variables. I am of a view (may be wrong) that we should use table variable only when dataset is small.
Generally true. The reason is that you can't build indexes (other than the clustered index and PK) on them, and they don't have statistics.
For a simple data transfer, neither of those things matters.
For any more complex query, they both do matter.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 10, 2008 at 7:14 am
ae299 (4/10/2008)
But how do you copy data from TABLE to TABLE on a remote serveras mentioned in the original post?
BULK INSERT / BCP seem to load OS files only, am I correct?
The problem is that direct table to table copying takes a long time.
Exporting the data to a .txt file, then bulk inserting it on the other server, will be faster that a table to table copy.
You're right that these need to work on external files (not directly from the database), but that's why you do the export step first.
Basically, it's a way to avoid distributed transactions, which slow down large transfers of data between servers.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply