Most efficient method of getting min values for a set

Question

Most efficient method of getting min values for a set

Viewing 3 posts - 16 through 18 (of 18 total)

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

Craig Farrell (11/1/2010)
That's some huge differences, in both our timing tests... same box. Yes. No, honestly. That's the same box.

Craig,

Between each of our codes, put a "GO 5", and then run it, and use the last numbers for each group. I think what you're seeing is the second+ time you run the code, some of the data is already cached.

The execution plans have changed, well, at least mine has. Wayne's code didn't change much execution plan wise, so I'm highly confused as to the results of the timing test.

They look like what I saw on my larger test.

I've attached the resultant sqlplans. That filter is apparently incredibly expensive, Wayne. I had figured the larger Segment would be worse. We end up with the same reads, too.

Very expensive, since there are so many records and no index that it could utilize. Your use of matching to a subquery with MIN is sargable, and it shows with the resulting increase in performance over my code.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95461 More actions · Answer 2

Don't forget: the rank() function requires every row to be evaluated in order to determine the rank for all records, and then I get the 1st one.

MIN just has to read the index for the lowest one.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

Craig Farrell (11/1/2010)
jamesniesewand (11/1/2010)
Personally I felt I was missing something with CTEs, and that "felt" like a Eureka moment for me (I've only just moved up to 2008 from 2000, and CTEs have been a bit of an "omg" moment - as have the geospatial datatypes and HeirarchyIDs) - but I'll definitely check both methods against each other on the actual data and see where I get with what I've got.
My personal omg moment with ctes was the recursive nature. Everything else is just a pretty subquery organizer to me, but those recursions... those are the true power! (Well, in my head, anyway...)

Like everything else, "It Depends". Some recursive CTE's can be the berries. Others are hidden RBAR on steroids. "Must look eye." 😉

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