June 23, 2005 at 11:50 am
Here is a query that runs perfectly (about 1-2 second) every time the variable has a value of 1-9. However, when I change the value to 10, it takes over 3 minutes to return data. There are only a few records that match this last criteria. Here is the approx row count for the tables:
Release: 5,000
Production_Unit: 600,000
Production_Detail: 3,000,000
- - - - - - - - - - -
declare @Line int
select @Line = 10
SELECT TOP 10 u.Serial, r.Model, u.Release, d.EventDate
FROM Production_Unit u LEFT JOIN Production_Detail d ON u.Serial = d.Serial LEFT JOIN Release r
ON u.Release = r.Release
WHERE r.Line = @Line and d.EventID = 'PO' And d.EventPF = 1
ORDER BY d.EventDate desc
- - - - - - - - -
If I remove the order by, it returns results immediately in all cases. Unfortunately, it is needed.
There are indexes on all the join columns as well as the ones in the where clause, except for EventPF, which has a bit datatype. The query plan shows an index seek on all 3 tables.
Any thoughts on why it would be slower with a 10?
June 23, 2005 at 11:57 am
Cause value 10 fetches more rows in ProductionUnit?
try to select how many rows are with this value in ProductionUnit... comparing to values from 1 to 9
When you remove the order by only first matching rows are retrieved and the rest are not taken into consideration so is faster But when you put ORDER By he has to retrieve everything arrange and retrieve the result
Vasc
June 23, 2005 at 12:02 pm
Also left join ...
WHERE r.Line = @Line and d.EventID = 'PO' And d.EventPF = 1
= inner join (because null = 'po' is never true)
Have you considered changing the join type to inner instead of outer? Might give better results also, although I suspect the optimizer to do it already.
June 23, 2005 at 12:06 pm
Thanks Vasc for your quick reply. There is currently only one matching row in Production_Unit, two in Release, and 7 in Production_Detail. The end result is one row.
There was a bit more data yesterday, it returned two rows in the same amount of time.
Yes, the order by is a factor, but the other line values produce thousands of rows in just a few seconds.
I ran the index tuning wizard on the query, but no changes were suggested.
Jean
June 23, 2005 at 12:09 pm
Hi Remi,
Replacing LEFT JOIN with INNER JOIN doesn't help either.
Jean
June 23, 2005 at 12:20 pm
Do you get different speed/plans if you execute the stored proc and the same query with hardcoded values from QA?
June 23, 2005 at 12:20 pm
SELECT TOP 10 u.Serial, r.Model, u.Release, d.EventDate
FROM Production_Unit u LEFT JOIN Production_Detail d ON u.Serial = d.Serial LEFT JOIN Release r
ON u.Release = r.Release
WHERE r.Line = @Line and d.EventID = 'PO' And d.EventPF = 1
ORDER BY d.EventDate desc
try this maybe the plan will be improved
SELECT TOP 10 u.Serial, r.Model, u.Release, d.EventDate FORCE ORDER
FROM Production_Detail d RIGHT JOIN Production_Unit u ON u.Serial = d.Serial LEFT JOIN Release r ON u.Release = r.Release
WHERE r.Line = @Line and d.EventID = 'PO' And d.EventPF = 1
ORDER BY d.EventDate desc
OPTION (FORCE ORDER )
Vasc
June 23, 2005 at 12:26 pm
Have the statistics been updated and the proc re-compiled?
At one place I worked, we ran into a bug that had to do with referencing values on the end of a statistical histogram and the optimizer not using the average density statistics when a value beyong the the ending value was referenced in a 'where' clause. We'd load today's data into a table, and then attempt to retrieve that just-loaded data. The query ran very slowly. If we referenced yesterday's data or we updated the statistics between the operations, everything was fine. MS had a hotfix for it that is, I believe, included in SP4.
June 23, 2005 at 12:37 pm
Thanks everyone for your replies.
I did update statistics this morning, so that should not be an issue. That was my initial reaction, but I was disappointed when the query still behaved the same way.
I tried this forced order query, and it does help reduce it down to under 10 seconds for all line values, but it is still not as fast as I would like it. Release being the smallest table, I figured I'd start with it.
SELECT TOP 10 u.Serial, r.Model, u.Release, d.EventDate
FROM Release r INNER JOIN Production_Unit u ON u.Release = r.Release
INNER JOIN Production_Detail d ON u.Serial = d.Serial
WHERE r.Line = @Line and d.EventID = 'PO' And d.EventPF = 1
ORDER BY d.EventDate desc
option (FORCE ORDER)
Now, every line value makes for a 6-8 second query. This is a setback for lines 1-9...
Jean
June 23, 2005 at 12:51 pm
Yes is the smallest but is not the one who dictate the result ... and from here your high time for certain values ...
What you should do now is to check the indexes
Vasc
June 23, 2005 at 12:51 pm
You didn't answer this one.
This issue is called parameter sniffing and could very well be what's causing you grief.
June 23, 2005 at 2:26 pm
It is much faster when I hardcode the value...what is parameter sniffing? and how can I get around it?
Jean
June 23, 2005 at 2:30 pm
You can read both the article and the discussion, the information is extremely relevent in both cases.
June 23, 2005 at 2:52 pm
Well, I added 'with recompile' at the beginning of the stored procedure, and the problem is gone!
It is now using a much more efficient query plan when it is provided with a value of 10.
Thanks for your help,
Jean
June 23, 2005 at 2:56 pm
Just hope you don't run that proc too often. Recompilation is CPU intensive when done frequently
* Noel
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply