Click here to Skip to main content
14,766,265 members
Please Sign up or sign in to vote.
3.71/5 (3 votes)
See more:
I have one table contains huge data(millions of records) simple insert statement taking time in execution.HOw to improve the speed of insertion.i have created index on that table still slow.

INSERT Slows down for table with Large data in sql server

Thanks,
-RG
Posted
Comments
Richard C Bishop 15-Apr-14 10:11am
   
Reduce the amount of records you are querying, that is the only way.
GuyThiebaut 15-Apr-14 10:33am
   
Is there a clustered index on the table or is the table partitioned.?
If there is then an insert will potentially slow things down because of the way a clustered index and partition works.

1 solution

1. Better to avoid index for improving the performance when INSERT.
2. Confirm the datatype of columns from existing table and new values in the INSERT statment are EXACTLY matching. Inserting different dataformats will take time.
3. Hope the table has a Primary key. Make/create a coulumn as Primary Key
4. Disable triggers if any
5. If possible, try BULK INSERT rather than adding single row.
   
Comments
Ramug10 16-Apr-14 3:23am
   
Thanks for giving reply I don't have any triggers on my table and also have primary key column with identity. if I ignore idex select clause will become slow.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900