I have insert statement which is loading ~3 million records into a table from one server to another server and running very slow. What can be done to make it faster.
Break it into smaller chunks. With so little detail provided, that's all I can suggest at this point.
can you please explain further ?
I am inserting ~3M data from one table to another table . Both tables are on different server . Servers are connected thru Linked server.
This is the query
insert into linkedservername.databasename.dbo.temp_test_abc select * from test_abc;
It is running very slow. How to break them in chunks?
If you are using ssis, you can setup a commit size so it doesn't lock the table. To Scott's point though, there's not a lot of information here. Are you copying large data sets? What are you using to do it? How often? is it truncate and reload or just diffs??etc....
I have a insert query in the store procedure which is loading data from Source to Target table using the below -
insert into linkedservername.databasename.dbo.temp_test_abc select * from test_abc;
it is inserting ~3M data from one table to another table . Both tables are on different server . Servers are connected thru Linked server.
Also target table does not have any constraints . I am creating a temp_** table on server A first and then moving data with insert from server B to server A.
On what column(s) is table test_abc clustered?
Primary key column is clustered.
What is the column name(s)?
why the column name is needed ?
To write actual code to do the inserts in batches of less than 3M. A dummy column name(s) would have done if you can't use the actual names.
Good luck with the loads.
This problem cannot be resolved until you identify which part is actually slow. Once you have identified where the performance issue lies - then you can determine a course of action to improve that performance.
Is it in the select statement running over the linked server? Is the select statement being converted to a cursor and processing row by row? Is the select performing table scans instead of being optimized?
Is it the network between servers?
Is it the insert into the temp table?
Possible solutions:
Fix the select query so it performs better.
Execute the code using OPENQUERY instead of 4-part naming.
Batch the select and insert using a WHILE loop
Move to SSIS/BCP or other external command (SQLCMD, Powershell, etc...)
You might get a better response if you could provide more information about what you are attempting to accomplish. There may be better ways of solving the problem than to just copy millions of rows across a linked server.