Professional Web Applications Themes

select into blocking - Microsoft SQL / MS SQL Server

i am using sql 2k. as far as i know, in previous versions doing a select... into could cause blocks in sysobjects and maybe on syscomments, sysindexes for the duration of the whole select statement and then the insert statement. besides you had an extra overhead because sql server has to determine the column type after the select statement is finished. is this still true for sql 2k? are there any articles that explains the way that select into works in the new version? thanks!...

  1. #1

    Default select into blocking

    i am using sql 2k.

    as far as i know, in previous versions doing a select... into could cause
    blocks in sysobjects and maybe on syscomments, sysindexes for the duration
    of the whole select statement and then the insert statement. besides you
    had an extra overhead because sql server has to determine the column type
    after the select statement is finished.

    is this still true for sql 2k? are there any articles that explains the way
    that select into works in the new version?

    thanks!


    hector quintanilla Guest

  2. #2

    Default Re: select into blocking

    To add to Arun's response, SELECT ... INTO blocking on system tables is
    mitigated in SQL 7 and above due to row-level locking. To illustrate:

    --session 1
    USE Northwind
    GO
    BEGIN TRAN
    SELECT * INTO MyTable FROM Orders
    GO
    --ROLLBACK


    --session 2
    USE Northwind
    GO
    EXEC sp_help 'Orders' --no blocking
    EXEC sp_help --blocked until session 1 COMMIT or ROLLBACK
    GO

    The overhead of the table create is usually insignificant compared with
    the data insert. SELECT ... INTO has the performance advantage of being
    minimally logged in the FULL or BULK_LOGGED recovery model.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    [url]http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800[/url]
    [url]http://www.sqlserverfaq.com[/url]
    [url]http://www.mssqlserver.com/faq[/url]
    -----------------------

    "hector quintanilla" <hquintanillacyberworks.com.mx> wrote in message
    news:%238C%230KLRDHA.1988TK2MSFTNGP12.phx.gbl...
    > i am using sql 2k.
    >
    > as far as i know, in previous versions doing a select... into could
    cause
    > blocks in sysobjects and maybe on syscomments, sysindexes for the
    duration
    > of the whole select statement and then the insert statement. besides
    you
    > had an extra overhead because sql server has to determine the column
    type
    > after the select statement is finished.
    >
    > is this still true for sql 2k? are there any articles that explains
    the way
    > that select into works in the new version?
    >
    > thanks!
    >
    >

    Dan Guzman Guest

  3. #3

    Default Re: select into blocking

    Correction to my response:

    SELECT ... INTO has the performance advantage of being minimally logged
    in the *SIMPLE* or BULK_LOGGED recovery model.


    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP


    Dan Guzman Guest

  4. #4

    Default Re: select into blocking

    thanks for the help, this clarifies a lot of things.

    another question. is it better to use explicitly create the temp table
    (create table()) or to use a select into? i know that maintenance for an
    explicit create table is higher (you change your db you may have to change
    your sproc) and that logging is not a big issue using select into.

    assuming that my db doesn't change would i get better performance with a
    "create table & insert select" than with a "select into"? i am working with
    a 40gb db so you can imagine that i am working with tables with millions of
    rows. so i am afraid that blocking sys tables can block other sprocs.

    thanks in advance.

    "Dan Guzman" <danguzmannospam-earthlink.net> wrote in message
    news:OTfnarURDHA.2196TK2MSFTNGP11.phx.gbl...
    > To add to Arun's response, SELECT ... INTO blocking on system tables is
    > mitigated in SQL 7 and above due to row-level locking. To illustrate:
    >
    > --session 1
    > USE Northwind
    > GO
    > BEGIN TRAN
    > SELECT * INTO MyTable FROM Orders
    > GO
    > --ROLLBACK
    >
    >
    > --session 2
    > USE Northwind
    > GO
    > EXEC sp_help 'Orders' --no blocking
    > EXEC sp_help --blocked until session 1 COMMIT or ROLLBACK
    > GO
    >
    > The overhead of the table create is usually insignificant compared with
    > the data insert. SELECT ... INTO has the performance advantage of being
    > minimally logged in the FULL or BULK_LOGGED recovery model.
    >
    > --
    > Hope this helps.
    >
    > Dan Guzman
    > SQL Server MVP
    >
    > -----------------------
    > SQL FAQ links (courtesy Neil Pike):
    >
    > [url]http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800[/url]
    > [url]http://www.sqlserverfaq.com[/url]
    > [url]http://www.mssqlserver.com/faq[/url]
    > -----------------------
    >
    > "hector quintanilla" <hquintanillacyberworks.com.mx> wrote in message
    > news:%238C%230KLRDHA.1988TK2MSFTNGP12.phx.gbl...
    > > i am using sql 2k.
    > >
    > > as far as i know, in previous versions doing a select... into could
    > cause
    > > blocks in sysobjects and maybe on syscomments, sysindexes for the
    > duration
    > > of the whole select statement and then the insert statement. besides
    > you
    > > had an extra overhead because sql server has to determine the column
    > type
    > > after the select statement is finished.
    > >
    > > is this still true for sql 2k? are there any articles that explains
    > the way
    > > that select into works in the new version?
    > >
    > > thanks!
    > >
    > >
    >
    >

    hector quintanilla Guest

  5. #5

    Default Re: select into blocking

    arun, dan, thanks for the tips and for clearing this up. they are sure
    gonna help me.


    "Dan Guzman" <danguzmannospam-earthlink.net> wrote in message
    news:uN7xoRdRDHA.2056TK2MSFTNGP12.phx.gbl...
    > We use SELECT ... INTO quite a bit to load temp tables and haven't
    > experienced blocking issues.
    >
    > --
    > Hope this helps.
    >
    > Dan Guzman
    > SQL Server MVP
    >
    >
    > "hector quintanilla" <hquintanillacyberworks.com.mx> wrote in message
    > news:uZEqzVWRDHA.2008TK2MSFTNGP11.phx.gbl...
    > > thanks for the help, this clarifies a lot of things.
    > >
    > > another question. is it better to use explicitly create the temp
    > table
    > > (create table()) or to use a select into? i know that maintenance for
    > an
    > > explicit create table is higher (you change your db you may have to
    > change
    > > your sproc) and that logging is not a big issue using select into.
    > >
    > > assuming that my db doesn't change would i get better performance with
    > a
    > > "create table & insert select" than with a "select into"? i am
    > working with
    > > a 40gb db so you can imagine that i am working with tables with
    > millions of
    > > rows. so i am afraid that blocking sys tables can block other sprocs.
    > >
    > > thanks in advance.
    > >
    > > "Dan Guzman" <danguzmannospam-earthlink.net> wrote in message
    > > news:OTfnarURDHA.2196TK2MSFTNGP11.phx.gbl...
    > > > To add to Arun's response, SELECT ... INTO blocking on system tables
    > is
    > > > mitigated in SQL 7 and above due to row-level locking. To
    > illustrate:
    > > >
    > > > --session 1
    > > > USE Northwind
    > > > GO
    > > > BEGIN TRAN
    > > > SELECT * INTO MyTable FROM Orders
    > > > GO
    > > > --ROLLBACK
    > > >
    > > >
    > > > --session 2
    > > > USE Northwind
    > > > GO
    > > > EXEC sp_help 'Orders' --no blocking
    > > > EXEC sp_help --blocked until session 1 COMMIT or ROLLBACK
    > > > GO
    > > >
    > > > The overhead of the table create is usually insignificant compared
    > with
    > > > the data insert. SELECT ... INTO has the performance advantage of
    > being
    > > > minimally logged in the FULL or BULK_LOGGED recovery model.
    > > >
    > > > --
    > > > Hope this helps.
    > > >
    > > > Dan Guzman
    > > > SQL Server MVP
    > > >
    > > > -----------------------
    > > > SQL FAQ links (courtesy Neil Pike):
    > > >
    > > > [url]http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800[/url]
    > > > [url]http://www.sqlserverfaq.com[/url]
    > > > [url]http://www.mssqlserver.com/faq[/url]
    > > > -----------------------
    > > >
    > > > "hector quintanilla" <hquintanillacyberworks.com.mx> wrote in
    > message
    > > > news:%238C%230KLRDHA.1988TK2MSFTNGP12.phx.gbl...
    > > > > i am using sql 2k.
    > > > >
    > > > > as far as i know, in previous versions doing a select... into
    > could
    > > > cause
    > > > > blocks in sysobjects and maybe on syscomments, sysindexes for the
    > > > duration
    > > > > of the whole select statement and then the insert statement.
    > besides
    > > > you
    > > > > had an extra overhead because sql server has to determine the
    > column
    > > > type
    > > > > after the select statement is finished.
    > > > >
    > > > > is this still true for sql 2k? are there any articles that
    > explains
    > > > the way
    > > > > that select into works in the new version?
    > > > >
    > > > > thanks!
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    hector quintanilla Guest

Similar Threads

  1. Replies: 0
    Last Post: September 24th, 03:24 AM
  2. Replies: 0
    Last Post: September 11th, 11:26 AM
  3. Replies: 0
    Last Post: September 11th, 12:19 AM
  4. Will Thread blocking in read() leads to process blocking?
    By Loic Domaigne in forum UNIX Programming
    Replies: 4
    Last Post: July 23rd, 12:36 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139