Performance of Select Into vs Insert

Question

Performance of Select Into vs Insert

dndaughtery

SSChampion

Points: 11290
More actions
April 13, 2018 at 11:37 am

#330383

Hey guys, I have a sproc where Im using select into to load a staging table used for creating flat files after it is loaded. Looking at the execution plan the load table has a cost of 35%. Would it result in better performance is I created the table and used INSERT instead of Select Into?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

dndaughtery - Friday, April 13, 2018 11:37 AM
Hey guys, I have a sproc where Im using select into to load a staging table used for creating flat files after it is loaded. Looking at the execution plan the load table has a cost of 35%. Would it result in better performance is I created the table and used INSERT instead of Select Into?

Any reason why you can't test it for yourself? Giving us a percentage cost does not help an awful lot, given that we do not know what else is contained in that plan.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 2

Are you using temp tables - or are these permanent tables? If permanent, then you are already dropping the table and recreating it every time...it probably would be better to create the table with appropriate indexes. Your process will then be a truncate and load process...

With that said - is it really necessary to create a table just so you can create output files? Can the queries just be called directly to output the data to the files?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ScottPletcher SSC Guru Points: 100948 More actions · Answer 3

Would it result in better performance is I created the table and used INSERT instead of Select Into?

For that INSERT, no. In fact, SELECT ... INTO dbo.new_table uses minimal logging by default whenever possible, which will speed up loading vs full logging.

Nevertheless, you should definitely pre-create the table (and the clus index, if the table will have one) before loading it, because that method causes internal system table latching/locking/blocking that can harm performance across your system.

Instead, do this:
SELECT TOP (0) INTO dbo.new_table FROM ...
to create the table structure, then this:
INSERT INTO dbo.new_table WITH (TABLOCK)
SELECT ...
to load the table.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

dndaughtery - Friday, April 13, 2018 11:37 AM
Hey guys, I have a sproc where Im using select into to load a staging table used for creating flat files after it is loaded. Looking at the execution plan the load table has a cost of 35%. Would it result in better performance is I created the table and used INSERT instead of Select Into?

Post your code because, without that, the answer is simply "It Depends".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)