SQL Server Performance

SELECT INTO or INSERT INTO

Discussion in 'General Developer Questions' started by wandrag, Jun 27, 2006.

  1. wandrag New Member

    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[?]
  2. MichaelB Member

    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
  3. Madhivanan Moderator

    Use Insert into than Select * into
    If you use Select * into then the table is locked preventing other resources to use it

    Madhivanan

    Failing to plan is Planning to fail
  4. wandrag New Member

    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?
  5. Adriaan New Member

    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.
  6. MichaelB Member

    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
  7. ramkumar.mu New Member

    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..."
  8. Adriaan New Member

    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?
  9. ramkumar.mu New Member

    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..."
  10. Adriaan New Member

    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.
  11. Adriaan New Member

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

    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
  13. Adriaan New Member

    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.
  14. mmarovic Active Member

    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.
  15. MichaelB Member

    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
  16. mmarovic Active Member

    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.
  17. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by wandrag</i><br /><br />Hi there guys,<br />What is the best practice to use: SELECT INTO or INSERT INTO?<br />I'm working on a large database, and the amount of records often exceeds millions of rows. <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />This sounds like reporting and/or analysing stuff to me. Right?<br /><br />Personally I think nobody (even the dumbest common user) expects a subsecond return from a query that involves the processing of millions of rows. And if they do, then someone should gently explain to them that an ad-hoc report of monthly sales over the last ten years might take some time to finish. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Anyway, another aspect with SELECT INTO hasn't been mentioned yet. It might lead to recompilation of stored procedures. Check this out and see if it might apply to you:<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb;EN-US;243586>http://support.microsoft.com/default.aspx?scid=kb;EN-US;243586</a><br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  18. MichaelB Member

    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
  19. ramkumar.mu New Member

    <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..."
  20. FrankKalis Moderator

    <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 />All good things take time. [<img src='/community/emoticons/emotion-3.gif' alt=':eek:' />)]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  21. MichaelB Member

    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
  22. FrankKalis Moderator

    There you are. [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Share This Page