Post reply

cursors and order by

  • December 17, 2009 at 10:19 am

    #1093599

    chris.dunn-795452 (12/17/2009)

    Hey,

    I noticed you're using a cursor! isn't that forbidden? that a cursor is bad news for performance!

    If I had the same task I'd use something like the following solution (wrote it free hand, didnt parse it):

    DECLARE @MachineMA Table (Ident INT IDENTITY(1,1), MachineID INT, MachineType INT)

    DECLARE @RecordCount INT, @RowNumber INT

    INSERT INTO @MachineMA (MachineID, MachineType)

    SELECT machineID, machineType FROM machinema A

    LEFT JOIN modelma B ON B.startmachine = A.machineID

    WHERE A.modelID = @modelID

    ORDER BY startmachine DESC

    SELECT @RowNumber = 1, @RecordCount = COUNT(Ident) FROM @MachineMA

    WHILE @RowNumber <= @RecordCount

    BEGIN

    SET @newmachineID = NEWID()

    SELECT @machineID = MachineID, @machineType = MachineType FROM @MachineMA WHERE Ident = @RowNumber

    INSERT INTO XMLmodelMap(sessionID, oldmodelID, newmodelID, oldmachineID, newmachineID, machineType) VALUES

    (@mySessionID, @modelID, @newmodelID, @machineID, @newmachineID, @machineType)

    SET @RowNumber = @RowNumber + 1

    END

    Actually, still not much better as you are still doing things RBAR using a WHILE loop.

  • December 18, 2009 at 9:45 am

    #1094129

    First you need to eradicate the cursor by doing your insert something like this:

    INSERT XMLmodelMap(machineID, machineType, [.... etc ...])

    SELECT

    machineID,

    machineType ,

    [... etc. ...]

    FROM

    machinema A

    LEFT JOIN

    modelma B ON B.startmachine = A.machineID

    WHERE

    A.modelID = @modelID

    ORDER BY

    startmachine DESC

    And add an IDENTITY column to your table which you can then use to select the rows in the order they were inserted.

    I tried it and it seems to work, but I fully expect someone to come along and blow this idea out of the water and tell me why it won't work in all situations (or am I just paranoid?):-)

    Nigel
    How to post questions

  • December 18, 2009 at 11:25 am

    #1094214

    Lynn Pettis (12/15/2009)

    Magy (12/15/2009)

    But it didn't order the data according to my ORDER BY clause...that's why I'm confused.

    When I run the cursor, and then check the table, it is not in the order as the original query.

    Thanks!

    How do you know? After you ran your script with the cursor, did you then do this:

    select * from dbo.XMLmodelMap

    Without an ORDER BY clause on the SELECT there is NO GUARANTTEE in the order of the data. Your cursor was declared with an ORDER BY, therefore it processed that data in the order you wanted. There is NO GUARANTTEE, however, that the data is stored in the table dbo.XMLmodelMap in that order.

    Spot on.

    --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)

  • December 18, 2009 at 11:35 am

    #1094222

    Jeff Moden (12/18/2009)

    Lynn Pettis (12/15/2009)

    Magy (12/15/2009)

    But it didn't order the data according to my ORDER BY clause...that's why I'm confused.

    When I run the cursor, and then check the table, it is not in the order as the original query.

    Thanks!

    How do you know? After you ran your script with the cursor, did you then do this:

    select * from dbo.XMLmodelMap

    Without an ORDER BY clause on the SELECT there is NO GUARANTTEE in the order of the data. Your cursor was declared with an ORDER BY, therefore it processed that data in the order you wanted. There is NO GUARANTTEE, however, that the data is stored in the table dbo.XMLmodelMap in that order.

    Spot on.

    Only said it 4 times through out the thread and I still think the OP doesn't understand. Go figure. 😛

  • December 21, 2009 at 2:06 am

    #1094620

    Here's another idea to add to the pot. Add an ordering column to your table and use ROW_NUMBER to populate it, like this:

    INSERT XMLmodelMap(ordering,machineID, machineType, [.... etc ...])

    SELECT

    ROW_NUMBER() OVER(ORDER BY startmachine DESC),

    machineID,

    machineType ,

    [... etc. ...]

    FROM

    machinema A

    LEFT JOIN

    modelma B ON B.startmachine = A.machineID

    WHERE

    A.modelID = @modelID

    ORDER BY

    startmachine DESC

    Might help, might not. Without further input from the OP, who knows? Seems he's gone all shy on us!

    Nigel
    How to post questions

  • December 21, 2009 at 8:31 am

    #1094797

    I think I misunderstood how cursors work...

    I figured if I used a SELECT statement and ORDER BY clause with the cursor to populate tmpTable, that the table would appear in that order when doing SELECT * FROM tmpTable...

    So I guess I will need to add another column to tmpTable so I can use ORDER BY on it

    thanks!

  • December 21, 2009 at 8:46 am

    #1094809

    The problem isn't with how cursors work... the problem is with how databases work. For SELECT statements, the only way to guarantee an order of the result set is to use an ORDER BY.

    --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)

  • December 21, 2009 at 8:49 am

    #1094812

    Based on your original post, all you need to order by is the oldmachineId; ORDER BY oldmachineID DESC.

    Unless I am missing something?

  • December 21, 2009 at 8:57 am

    #1094820

    Magy (12/21/2009)

    I think I misunderstood how cursors work...

    I figured if I used a SELECT statement and ORDER BY clause with the cursor to populate tmpTable, that the table would appear in that order when doing SELECT * FROM tmpTable...

    So I guess I will need to add another column to tmpTable so I can use ORDER BY on it

    thanks!

    Your ORDER BY on your cursor WORKS! That isn't your problem. As Jeff and I have been telling you, there is no guaranttee that the data is stored in the order entered into the table. That is the nature of SQL tables, they are UNORDER sets of related (hopefully) data. If you want order when selecting the data, you HAVE TO supply an ORDER BY clause in the SELECT statement.

  • December 21, 2009 at 12:20 pm

    #1094993

    Magy (12/15/2009)

    Yeah, I did that...and the order was not the same.

    I was under the impression that it would be...

    Thanks!

    I wouldn't waste my time with an order by on INSERT, that is with your cursor. SQL Server does not guarantee order in a select statement unless you specify an Order By. Here is the issue you are having. Unless you Order by in your select statement, it comes out in whatever random fashion SQL Server determines (based upon threading and a bunch of internal stuff). Even clustered indexes are no longer guaranteed to extract your data in index order (due to threading).

    Josef Richberg
    2009 Exceptional DBA
    http://www.josef-richberg.squarespace.com
    http://twitter.com/sqlrunner

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply