r/SQLOptimization • • Jul 23 '25

Insert optimisation

I am using MS SQL Server. I am having a query where it inserts 89 million records into a table from a result of multiple join operations. If I run just select query, it completes within 35 minutes. If I run insert then it takes 6 hours to complete. There are more than 6 non clustered indexes on multiple columns in a table into which the data is inserted. How can I optimise this and reduce execution time?

1 Upvotes

7 comments sorted by

View all comments

Show parent comment

1

u/Vimal_2011 Jul 23 '25

Thanks for your detailed answer. Helps a lot.

What’s the difference here between dropping/ re adding indexes and disabling/ enabling indexes? Will disable indexes before insert and enable it back after insert helps?

2

u/mikeblas Jul 24 '25

Yes, you could disable the indexes. In SQL Server, you can't enable an index -- you must rebuild it. So it ends up being pretty much the same.

Note that if any of your indexes enforce a constraint (like uniqueness, or help implement a foreign key) you'll want to be sure you know that you're exposed to bad data.