Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Jul 2007
    Posts
    25

    Exclamation Unanswered: Is Order By affect The Query Speed

    hello,

    I have a query that insert insert into new table , and then i select from this table,

    if i add ORDER BY in the INSERT INTO script , does it affect the speed of the SELECT


    i have big table that take about 70 sec

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it will either be ignored, or it will slow the INSERT down
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I'm confused. ORDER BY is not allowed in INSERT statements, is it? Unless part of a sub-query or something along those lines. And why would you do an order-by on an insert, since it makes no logical sense to try to instill order on a table that is not ordered.

    The ORDER BY has to be on the select OUT from the table.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Jul 2007
    Posts
    25
    I use ORDER BY in INSERT INTO cause I use INSERT INTO to a SELECT Statment, i have SELECT nested into INSERT

    My Question is if i add the data in order in the table do i have better SELECT query performance

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    So your ORDER by is not in the INSERT itself, but rather in the SELECT that is gathering the input. That is what I meant.

    You're wasting your time and typing fingers. You cannot instill order when inserting to a SQL table, because there is no valid concept of that type of order in a SQL table.

    SO, the short answer is NO. It will not affect later query speed. It is also a direct affront to all things good and beautiful. Most probably it would cause a Smiting Blow from the SQL Gods were it to be implemented in a production database.

    If order is important, do it on your SELECT when you are pulling data out of the table.

    The only time I would put an ORDER BY in an insert statement is if it was necessary in the SUB-SELECT to assure that the right data is pulled from the source table to be slapped into the INSERT table. OR if you need to limit the input from the select...as in:
    Code:
    	INSERT dbo.MyDestinationTable
    		SELECT TOP 100
    			Myfield1,
    			Myfield2,
    			MyDate
    		FROM   MySourceTable
    		ORDER BY MyDate DESC
    and in this case, the ORDER BY is relative to the SELECT, not the INSERT.
    Last edited by TallCowboy0614; 08-07-07 at 16:03.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Jul 2007
    Posts
    25
    Thanks man

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    You're welcome, dude.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    cowboy,

    all of that is straight from the orthodox canon and that's fine and good, yet before I knew any better i wrote a server side pagination thingie that does an INSERT with a SELECT and an ORDER BY into a temp table and it does the row numbering with an identity column. It worked and still works and everytime I read this thing about it not being reliable, I have never seen it not work in practice.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TallCowboy0614
    SO, the short answer is NO. It will not affect later query speed.
    please, sir, i must disagree

    i think i gave the correct answer already

    this --

    INSERT INTO ... SELECT ... FROM ... ORDER BY ....

    is going to be slower than this --

    INSERT INTO ... SELECT ... FROM ...

    simply because the ORDER BY will take extra time

    (and, as we all know, the ORDER BY isn't guaranteed to actually insert them in the correct order anyway)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    OK Sean, I see your point, I knew I should have loaded my Orthodox Sidearm with birdshot so it scattered better. I still think that your example is another situation where the ORDER BY is related to the SELECT rather than the INSERT. Maybe it's semantics, but an order by in your situation is actually used to populate one of the INSERT table's columns, which is the ID column. Conceptually (in my mind, anyway) this applies to the gathering of the data to be inserted. NOT the ordering of the insert table.

    In your example, the insertion of the data has nothing (reliably) to do with the actual physical order of the data in the table, nor in and of itself anything to do with the speed of a subsequent select from the table, correct? Of course the speed to be gained, if any, on the select has to do with the construction of the SELECT statement, and any applicable indicies that may be in place. NOT, per se, by anything you did on the insert itself.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    But the original question was whether subsequent SELECTS from the table will be faster, which they are not. There was no question as to which method of inserting was going to be faster. Presumably that is beyond debate.

    If you have some free time, and a busy multi-CPU server, do a sp_helptext sp_who2, and scroll down to the bottom. I always loved the comment there:
    Code:
          -- (Seems always auto sorted.)   order by spid_sort
    On a busy server, parallelism takes over, and you get the results in every which way. I wonder how much processing time is shaved off by commenting out the order by statement. I mean, you might even have 200 or 500 rows to order!

  12. #12
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by r937
    please, sir, i must disagree

    i think i gave the correct answer already

    this --

    INSERT INTO ... SELECT ... FROM ... ORDER BY ....

    is going to be slower than this --

    INSERT INTO ... SELECT ... FROM ...

    simply because the ORDER BY will take extra time

    (and, as we all know, the ORDER BY isn't guaranteed to actually insert them in the correct order anyway)
    Well then, I guess you've forced my hand. Unfortunately, I've got to disagree with your disagreement then. Your answer was correct in your interpretation of the original question. That is, if he/she (or is that HeShe? I get that confused sometimes, which really has cause problems in the bar at times at closing) was asking if the ORDER BY would case the INSERT to be slower. If that is the case, I am behind you (at a respectable distance, of course) 100%.

    However, if heshe asked if using ORDER BY on the INSERT would have any effect on later and independent SELECTs, then I stand by my answer. Nope.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Daft I know but I have always* wondered whether ordering an insert by the clustered index definition is better\ no different to not ordering an insert. I have a feeling I have read somewhere one way or the other but I can't remember what the upshot was. The thing is - unless SS does implicitly order the insert anyway then it will be slower than an unordered insert as there will be page splits. When using BCP you can tell BCP if the order of data in the file & clustered index order are the same and the bulk insert is faster.

    It is this sort of thing that gets me to sleep at night.

    *not literally
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MCrowley
    But the original question was whether subsequent SELECTS from the table will be faster
    Oops.
    Quote Originally Posted by MCrowley
    There was no question as to which method of inserting was going to be faster. Presumably that is beyond debate.
    Double oops.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    I have a feeling I have read somewhere one way or the other but I can't remember what the upshot was.
    perhaps this may help:
    INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted

    -- Ordering guarantees in SQL Server...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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