Post reply

delete duplicates

  • July 22, 2007 at 2:16 pm

    #721401

    Ok... replaced the CROSS JOIN with INNER JOIN like it was supposed to be...

    DELETE AD1

    FROM   DBO.AILMENTDETAIL AS AD1

           INNER JOIN DBO.AILMENTDETAIL AS AD2

    ON  AD1.AILMENTID = AD2.AILMENTID

           AND AD1.DATAPOINTID = AD2.DATAPOINTID

           AND AD1.AILMENTDETAILID > AD2.AILMENTDETAILID

    ... results didn't change much (shouldn't have, either because the equalities made the CROSS JOIN more like an INNER JOIN)...

    Here's the results I get (again, can't run the 2k5 solution 'cause I don't have it)...

    ===== Jeff ======

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    (4998 row(s) affected)

          5156 Milliseconds

     

    ===== Peso ======

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    (4998 row(s) affected)

          7906 Milliseconds

    Not sure why Peter's 2k solution took so long on your machine, Serqiy.

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

  • July 22, 2007 at 3:00 pm

    #721406

    > Not sure why Peter's 2k solution took so long on your machine, Serqiy.

    It's probably because Peter's solution consumes much more memory and size of intermediate results exceeds available memory.

    Try to reduce amount of memory available to your SQL Server and see if this theory is right.

    I've got 512M here.

    _____________
    Code for TallyGenerator

  • July 22, 2007 at 3:51 pm

    #721407

    No effect... maybe size of TempDB?  Mine is set to 1 gig...

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

  • July 22, 2007 at 5:38 pm

    #721408

    Dunno.

    On another computer got this:

    ===== Jeff ======

    (4958 row(s) affected)

    7143 Milliseconds

    ===== Peso ======

    (4958 row(s) affected)

    9826 Milliseconds

    Tempdb here is 640M, 600M is free.

    _____________
    Code for TallyGenerator

  • July 22, 2007 at 6:00 pm

    #721410

    It's definitely memory.

    I went to my local server (to avoid other users influence) and played with memory size allocated to SQL Server.

    I ran test 3 times for every memory size to check consistency.

    Here what I've got:

    199M

    ===== Jeff ======

    (5058 row(s) affected)

    4093 Milliseconds

    ===== Peso ======

    (5058 row(s) affected)

    15686 Milliseconds

    ===== Jeff ======

    4063 Milliseconds

    ===== Peso ======

    15733 Milliseconds

    ===== Jeff ======

    4063 Milliseconds

    ===== Peso ======

    15736 Milliseconds

    250M

    ===== Jeff ======

    (5058 row(s) affected)

    4030 Milliseconds

    ===== Peso ======

    (5058 row(s) affected)

    10356 Milliseconds

    ===== Jeff ======

    3970 Milliseconds

    ===== Peso ======

    10470 Milliseconds

    ===== Jeff ======

    3953 Milliseconds

    ===== Peso ======

    10233 Milliseconds

    301M

    ===== Jeff ======

    (5058 row(s) affected)

    4013 Milliseconds

    ===== Peso ======

    (5058 row(s) affected)

    5266 Milliseconds

    ===== Jeff ======

    3983 Milliseconds

    ===== Peso ======

    5266 Milliseconds

    ===== Jeff ======

    4063 Milliseconds

    ===== Peso ======

    5316 Milliseconds

    352M

    ===== Jeff ======

    (5058 row(s) affected)

    4046 Milliseconds

    ===== Peso ======

    (5058 row(s) affected)

    5393 Milliseconds

    ===== Jeff ======

    4186 Milliseconds

    ===== Peso ======

    5300 Milliseconds

    ===== Jeff ======

    4000 Milliseconds

    ===== Peso ======

    5313 Milliseconds

    454M

    ===== Jeff ======

    (5058 row(s) affected)

    4186 Milliseconds

    ===== Peso ======

    (5058 row(s) affected)

    5296 Milliseconds

    ===== Jeff ======

    3966 Milliseconds

    ===== Peso ======

    5283 Milliseconds

    ===== Jeff ======

    4110 Milliseconds

    ===== Peso ======

    5360 Milliseconds

    _____________
    Code for TallyGenerator

  • July 22, 2007 at 6:04 pm

    #721411

    Jeff's query becomes affected when it's less than 128M of memory reserved.

    At 128M point it's about 6.5s vs 18.2s

    _____________
    Code for TallyGenerator

  • July 22, 2007 at 9:43 pm

    #721416

    Now, there's a lesson learned... Thanks, Serqiy.

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

Viewing 7 posts - 16 through 21 (of 21 total)

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