Which is the performance killer: SELECT INTO or INSERT INTO?

Insert Into / Select Into
Insert Into / Select Into
There are many ways to kill performance in a script or stored procedure.  However, not many think about the possibility that adding columns to a temporary table can kill performance.  Here, I’m going to show you how a simple design decision – using INSERT vs ALTER TABLE – can have a huge impact on your procedure performance.

This comes from a procedure we wrote for Minion Reindex to greatly improve your index maintenance by allowing you to have some impressive dynamic routines without any extra jobs.

INSERT vs ALTER TABLE – SELECT INTO and ALTER TABLE

We recently wrote a helper procedure to allow you to limit the rows for your reindex runs with Minion Reindex. We got it working just like we wanted it, and it returned in just a couple seconds. The problem is that sometimes it would take a couple seconds, and others it would take a couple minutes. Then it started always taking two minutes, and we weren’t able to get the performance back down at all.

We added indexes, we ran with recompile, we limited our result sets, etc. We did everything you usually do to fix the performance of a stored procedure. Then I noticed that it was loading data with a SELECT INTO instead of INSERT/SELECT. What the code actually did was, it created a table with some rows and then added a couple columns and then updated those rows.

Here’s a look at the actual code:

So the synopsis is: Select Into, add two columns, update those two columns. That’s it. And most of the time was spent on the alter table statements.

I’ll go ahead and say that each of these tables only has about 13,000 rows in them, so it’s not like we’re dealing with millions of rows here. This is the part of the procedure that was taking so long and what needed to be corrected.

INSERT vs ALTER TABLE – INSERT

Now that we’ve seen the original poor-performing code, let’s take a look at the fixed code.

Continue reading at MinionWare.net.

Continue reading on MinionWare.net.

54321
(1 vote. Average 5 of 5)