Poor performance with MIN and MAX functions

Question

Post reply

Poor performance with MIN and MAX functions

haz33

Old Hand

Points: 363
More actions
December 14, 2007 at 2:39 pm

#119361

Hi, I'm hoping someone can help me with this.
I have a view that combines multiple tables. There are about 36,000 rows in that view. One of the fields is an integer. If I select all of that field's values from the view, the data is returned in under a second. Likewise, if I do a COUNT or an AVG function on that field, the row is returned in under a second.
Now, if I attempt to use the MIN or MAX function, all of a sudden, the query takes 22 seconds to run and return an answer. Why would this occur? Why are the COUNT and AVG functions so fast and the MIN and MAX functions so slow?
Thanks in advance!
-Erik

Viewing 5 posts - 1 through 5 (of 5 total)

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

Jo Pattyn SSC-Dedicated Points: 32442 More actions · Answer 1

Odd, perhaps a change in the executionplan? You can see the actual plan in the query analyzer.

I guess count(*) or avg(*) give an index scan when min,max use index seek(should be faster)?

Have you updated the statistics?

haz33 Old Hand Points: 363 More actions · Answer 2

Yep, I updated the stats.

How do you change the execution plan? I can see the execution plan (looks like a flowchart) but how do you change it?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

The execution plan shows the steps SQL Server takes to resolve a query and return the results. The way to "change" and execution plan is to re-write the query or to add\remove\change indexes on the tables\views accessed by the query. Typically the fastest way for SQL Server to retrieve data is through Index Seeks, then Index Scans, then Table Scans. What you want to do is run the Count or Avg query in one Query Analyzer window and then run the Min\Max Query in another Query Analyzer window and compare the execution plans. Odds are the Min and Max functions are causing while the Count and Avg functions are doing seeks. You want to make sure you have a non-clustered index on the column(s) which are using min\Max functions.

If you post your code we can be more specific.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

haz33 Old Hand Points: 363 More actions · Answer 4

It looks like I found it in the execution plan. I was calling a particular table more times than I needed to. Once I fixed the query, then the performance greatly improved.

Thanks for the help!

Poor performance with MIN and MAX functions

Cookies on SQLServerCentral