Re: Use of SELECT TOP x (Are you there, Kalen...heheh)
From: Steve Kass (skass_at_drew.edu)
Date: 02/01/05
- Next message: Lontae Jones: "Adding table to a FileGroup"
- Previous message: Hugo Kornelis: "Re: Please help with Select"
- In reply to: J. M. De Moor: "Use of SELECT TOP x (Are you there, Kalen...heheh)"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 01 Feb 2005 18:40:58 -0500
Joe,
My guess is that the stored procedure is using a plan
inappropriate for the particular parameters you are passing it.
This sometimes happens because of something called "parameter
sniffing" (search for that for some ideas). It can also happen when
the data is not well-described by statistics. In either case, it can
also be worth looking at your indexing. I see this more often
when there are a bunch of one-column indexes on a table,
but no multi-column indexes, which would make a big improvement.
In any case, removing a WHERE clause and adding TOP 100
will tell you pretty much nothing useful about what's going on.
Without the WHERE clause, or with the TOP, or with both
changes, more than likely an entirely different approach is
used to return rows. You are certainly unlikely to be seeing
rows that happen to be produced in the same order as any kind
of intermediate result in your sp.
You can see what's going on by comparing the rowcounts in the
actual and estimated execution plans. In query analyzer, type
CTRL-K and then run your troublesome procedure. The hover-over
popups in the graphical execution plan you see will show row counts
(third row or so) and estimated rowcounts (bottom row). That, as
well as reading the plan through, are ways to get a feel for what's
going wrong.
Look for a bookmark operator as the culprit in the query plan.
If it's the one with the bad rowcount, one of the suggestions
I made is more likely to be an effective road to follow.
Steve Kass
Drew University.
J. M. De Moor wrote:
>(I hope I don't have to provide a bunch of DDL, as it is a general
>question...but if I need to I will.)
>
>We have a rather complex SELECT statement in a STORED PROC that suddenly
>started to perform poorly. Suffice to say it builds a summary resultset
>from several tables, generally returning less than 6 or 7 rows. A couple of
>the base tables have 200,000 rows...not too big, anyway.
>
>In troubleshooting the sudden drop in performance (queries went from
>subsecond to 20 second response times), I removed the WHERE clause and added
>a SELECT TOP 100 just to see what the unfiltered data looked like. The
>response time was dropped to subsecond. When I added the WHERE clause back
>in, the response time remained subsecond. Once I removed the SELECT TOP
>100, the query again responded in around 20 seconds.
>
>Given that the resultset is certainly never more than 10 rows, there is
>obviously something I don't understand about the workings of SELECT TOP x.
>Can someone enlighten me?
>
>Thanks.
>
>Joe
>
>PS. I still believe we have to fix something, and we will as soon as we
>find it, we were just surprised that TOP x had this impact.
>
>
>
>
- Next message: Lontae Jones: "Adding table to a FileGroup"
- Previous message: Hugo Kornelis: "Re: Please help with Select"
- In reply to: J. M. De Moor: "Use of SELECT TOP x (Are you there, Kalen...heheh)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
