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: 246985 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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeffrey Williams SSC Guru Points: 90304 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: 101120 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004406 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)