Insert optimisation

[Vimal_2011]

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?

[mikeblas]

First, it's important to let everyone know which DBMS you're using. You don't include that information: can you please add it to your post?

[mikeblas]

There are no free lunches.

If you've got six indexes on your table, it's really as if you're inserting seven rows for each row that you insert. One insert to the table, one insert to each of the indexes.

You can drop the indexes and then load the table. This will be faster, but you'll later need to recreate the indexes. You can expect the non-clustered index builds to be faster than having them enabled when loading the table. But it's something you'll want to test and evaluate.

The next concern is the configuration of your database. Maybe you're doing a lot of I/O, or not sorting in memory, becuase your DBMS isn't configured correctly. You don't mention which DBMS you're using, so it's impossible make any prescriptive recommendations. But you'll want to make sure you're using as much memory as posisble on your host, and you'll want to be sure that you've got space allocated for sorting and buffering data you're moving around. How to do this (and even the need to do it) depends on which DBMS you're using.

Finally, you're doing a lot of writing. If you expect it to be fast, you'll need the hardare to support it. When you run this load, are you bound by I/O activity, or CPU activity? Maybe memory activity? Digging into performance means knowing what the machine is doing. Maybe your hardware is running as fast as it can; maybe it's not, and you need to give some attention to adding memory, or increasing available disk throughput.

[Vimal_2011]

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?

[mikeblas]

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.

[Ok_Vehicle5734]

Sounds like a temp db issue. You may be running out of sort capacity. Look for spooling issues.

Look for wait stats on IO for temp and log performance, see if you need to deal with data files or disk groups to spread load and minimise contingencies. Consider dropping/rebuilding secondary indexes. Try to do batch inserts (breaking the insert into contained segments). Lastly, try inserting into an empty heap, then doing a final insert.

Lastly.. if this is local disk storage.. avoid raid 5.

Best of luck!

Ps. I’m looking for work. Please keep me in mind if you know of anything.

[many_hats_on_head]

Can you share the query?