Removing only contiguous/adjacent duplicate records from a rowset

Question

Removing only contiguous/adjacent duplicate records from a rowset

Viewing post 16 (of 15 total)

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

Want a cool sig (10/26/2012)
Jeff: I see what you mean by the 3 scans vs 2 scans. I ran the script on a VM server here at work, since that's where I am now, and the results came back mixed. I ran it 5 times and 2 of the 5 times the execution time for my script was actually faster(1,3) and 4th run came out the same.
I'm assuming it's the faster machine compensating for any additional overhead but I didn't expect it to execute faster. I do like your script better, it's easier to read. I didn't know you can delete with CTE, learn something new every day. 🙂
(100000 row(s) affected)
(100000 row(s) affected)
==============================================================================
========== Want a cool sig's solution ==========
==============================================================================
SQL Server parse and compile time:
CPU time = 356 ms, elapsed time = 356 ms.
SQL Server Execution Times:
CPU time = 266 ms, elapsed time = 515 ms.
(3702 row(s) affected)
==============================================================================
========== Jeff's solution ==========
==============================================================================
SQL Server parse and compile time:
CPU time = 386 ms, elapsed time = 386 ms.
SQL Server Execution Times:
CPU time = 282 ms, elapsed time = 521 ms.
(3702 row(s) affected)
==============================================================================
(100000 row(s) affected)
(100000 row(s) affected)
==============================================================================
========== Want a cool sig's solution ==========
==============================================================================
SQL Server parse and compile time:
CPU time = 339 ms, elapsed time = 339 ms.
SQL Server Execution Times:
CPU time = 297 ms, elapsed time = 533 ms.
(3670 row(s) affected)
==============================================================================
========== Jeff's solution ==========
==============================================================================
SQL Server parse and compile time:
CPU time = 331 ms, elapsed time = 331 ms.
SQL Server Execution Times:
CPU time = 266 ms, elapsed time = 553 ms.
(3670 row(s) affected)
==============================================================================
(100000 row(s) affected)
(100000 row(s) affected)
==============================================================================
========== Want a cool sig's solution ==========
==============================================================================
SQL Server parse and compile time:
CPU time = 341 ms, elapsed time = 341 ms.
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 579 ms.
(3765 row(s) affected)
==============================================================================
========== Jeff's solution ==========
==============================================================================
SQL Server parse and compile time:
CPU time = 331 ms, elapsed time = 331 ms.
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 556 ms.
(3765 row(s) affected)
==============================================================================
(100000 row(s) affected)
(100000 row(s) affected)
==============================================================================
========== Want a cool sig's solution ==========
==============================================================================
SQL Server parse and compile time:
CPU time = 342 ms, elapsed time = 342 ms.
SQL Server Execution Times:
CPU time = 266 ms, elapsed time = 538 ms.
(3700 row(s) affected)
==============================================================================
========== Jeff's solution ==========
==============================================================================
SQL Server parse and compile time:
CPU time = 340 ms, elapsed time = 340 ms.
SQL Server Execution Times:
CPU time = 297 ms, elapsed time = 529 ms.
(3700 row(s) affected)
==============================================================================
(100000 row(s) affected)
(100000 row(s) affected)
==============================================================================
========== Want a cool sig's solution ==========
==============================================================================
SQL Server parse and compile time:
CPU time = 342 ms, elapsed time = 342 ms.
SQL Server Execution Times:
CPU time = 297 ms, elapsed time = 574 ms.
(3724 row(s) affected)
==============================================================================
========== Jeff's solution ==========
==============================================================================
SQL Server parse and compile time:
CPU time = 331 ms, elapsed time = 331 ms.
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 495 ms.
(3724 row(s) affected)
==============================================================================

Thank you very much for the feedback. I suspect that you're machine has closer times because it has better disks tha the IDE drives on my desktop. It does show how important testing is and I thank you for the tests you ran.

Glad I could teach someone something new. You can actually operate on the underlying tables using 3 or 4 cascading CTEs. It comes in real handy at times.

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