SELECT INTO or INSERT INTO

Hi there guys,
What is the best practice to use: SELECT INTO or INSERT INTO?
I'm working on a large database, and the amount of records often exceeds millions of rows.

Thanks[?]

Select into is great for large sets of data due to it is not logged and so does not write to the tran log which slows you down. Insert into is more clean but slower.


Michael B
Sr. DBA

"The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

Lets say that the locked table is ok.
I'm more interested in the overall impact that the query will have on the server (especially the queries that contains millions of rows), and the other users(besides the locking of the table).
Ex - which query is the most recource intensive?

Assuming your target table is a temp table, you're also dealing with contention and locking issues on tempdb - especially if you're talking about millions of rows.

To avoid such issues, just don't use SELECT INTO syntax, use CREATE TABLE and INSERT INTO.

Another benefit is that you have complete control over the exact specifications of the table that you're creating.

Use select into for speed<img src='/community/emoticons/emotion-1.gif' alt='' /> just try and compare and make a decision<img src='/community/emoticons/emotion-1.gif' alt='' /> Never do select * but name the fields.<br /><br />Michael B<br />Sr. DBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />but fools despise wisdom and instruction." Proverbs 1:7

one more example to support SELECT INTO...
Iteration1 Iteration2 Iteration3 Average (sec)
--------------------------------------------------------------------
select into 33 30 21 28

insert 128 110 87 106.66

the query used...

the consideratiosn were as follows...

Steps Followed for CREATE_INSERT
--------------------------------
1. Created a table #performance_create_insert with the table structure of Individual.

2. Inserted into the table using the above query.

3. This was done three times and average taken.

Steps Followed for SELECT_INTO
------------------------------

1. Used the above query ie SELECT INTO for inserting data from individual.

2. Dropped the table #performance_selectinto each time and ran select into query three times and average taken.

NOTE: No of rows Individual considered -> 9863692



Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

Better performance in an isolated test still doesn't make it a best practice ...

Also, did you do ...

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

... before each iteration?

yup! we cleared the cache before each iteration and for each run.

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

You mention that for CREATE/INSERT, you created "a table #performance_create_insert with the table structure of Individual.". I assume this also included the primary key, defaults, constraints, and indexes? Perhaps even triggers?

That would probably account for most of the extra time required when inserting data.

In that case, you should also be looking at what you're doing later on with the data in the temp table - the 'complete' table may well offer better speed for JOINs, WHERE clauses, etc. etc.

If you don't need the extras, then drop them from the CREATE script, and see how the two compare.

Plus you may find that it takes less time to add the 'extras' after inserting the data.

With databases, best performance trumps best practices every time<img src='/community/emoticons/emotion-1.gif' alt='' /> Ask any user what they want.. they want faster access. Best practices should only be best if they support and do not hinder best performance. nuf said.<br /><br />Michael B<br />Sr. DBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />but fools despise wisdom and instruction." Proverbs 1:7

Agreed on what users want - no problem with that. But a phrase like "best performance trumps best practices every time" is too easy ...

My idea of 'best practice' is that you try to anticipate less-than-ideal circumstances, and different SQL Server installations where your DB will be living, outside of the comfort of your dev server.

Select into #temp will prevent any other connection to use temporary table untill all data are written in a table you are creating and populating at the same time. So while you may speed-up the single execution you are testing, you are blocking at the same time many other users.

I am unsure where you got your info on blocking with temp tables. Can you provide? or are you refering to issues 6.5 had with select into locking objects? This is no longer the case. I looked for info on the SQL Mag Master CD and can only find the following as reference (note the last line):

Why is my application locking up in SQL Server?

Publication: FAQ
Issue: Web Exclusive
Author(s)
Neil Pike

Topic(s)
SQL Server 6.5

SQL Server 7.0

SQL Server and Database

Windows OSs


A. This is due to a documented change in SQL 6.5 because tables created by using SELECT INTO hold to the ACID (atomicity, consistency, isolation, durability) transaction properties. This also means that system resources, such as pages, extents, and locks, are held for the duration of the SELECT INTO statement. With larger system objects, this leads to the condition where many internal tasks can be blocked by other users performing SELECT INTO statements. For example, on high-activity servers, many users running the SQL Enterprise Manager tool to monitor system processes can block on each other, which leads to a condition where the SEM application appears to stop responding. (This happens on tempdb which is the biggest problem with this new feature for most users)

You can revert to the old 6.0 and below behaviour where these locks are not held by applying at least SQL 6.5 ServicePack 1 and then setting traceflag 5302 on startup.

It is recommended that you amend your application to not use select into, or if you do, create the table using "SELECT ... INTO .... WHERE 1=0" to create the table and then use standard inserts to populate the table.

(This problem does not occur in SQL 7.0 and above as row-level locks are taken and so system table pages aren't unnecessarily blocked)


Michael B
Sr. DBA

"The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

Interesting. I was not able to find Microsoft article (msdn, bol, ...) confirming blocking. I found many articles from other sites (included this one) but that doesn't prove anything. I think the best way is to test it. Just try to select into temp table million rows into temp table and in another window try to create (or even select... into ... where 1=0) another temp table. Right now I can't access any comp with sql server installed to try. I guess you may be right and this is just another 'no longer valid' myth that is reprinted but never retested. So, if anyone can test before I can, I am curious to know result.

How can I get above "Junior Member"?<img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Michael B<br />Sr. DBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />but fools despise wisdom and instruction." Proverbs 1:7

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by MikeEBS</i><br /><br />How can I get above "Junior Member"?<img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Michael B<br />Sr. DBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />but fools despise wisdom and instruction." Proverbs 1:7<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />post 8 more posts.<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."

Ok.. posting I go<img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Michael B<br />Sr. DBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />but fools despise wisdom and instruction." Proverbs 1:7