-
Slow query
I have a fairly simple query that joins a table (ScreenContents) to a view (vw_ISN) like so:
FROM ScreenContents WITH (NOLOCK)
INNER JOIN vw_ISN ar WITH (NOLOCK) ON ScreenContents.LabelCounter = ar.SystemItemCounter
AND (ar.SystemItemTypeCounter = 12)
AND (ar.JurisdictionCounter = 93)
AND (ar.LanguageCounter = 1002)
Now if I run this query I get 30000 results back in 1 second. If however I change the last line to
AND (ar.LanguageCounter = 1003)
it takes 12 minutes for approximately the same number of rows. There are around 15000 rows in the table referenced by vw_ISN (ISN) for both languagecounters.
The table has a primary key and one other index. This other index is composed of
SystemItemTypeCounter
SystemItemCounter
LanguageCounter
JurisdictionCounter
I rebuilt the primary key but this made no difference. I checked the statistics and they appeared to be up to date with good selectivity.
I then ran the following statement
UPDATE ISN set languagecounter=languagecounter where languagecounter=1003
This solved the problem and the query runs in 1 second for languagecounter = 1003. I rechecked the statistics and this update did not affect them. I then restored a backup and ran a similar update query against a column that is not included in the index and another that is included in the index. Neither made any difference.
How did updating the languagecounter field solve the problem?
-
Update to this:
Adding a new language (1004) which would add another 15000 records to the ISN table had the same effect (i.e. the query is fast for 1003).
-
Did you compare execution plans?
-
I did and one way (the fast way) was using a clustered index scan and the other (slow) one was using an index seek and a row lookup. The question is why (when the data was more or less uniform) did it change when I ran that update when the statistics were unchanged.
-
Since data distribution changed after you updating the table, sql will generate new plan accordingly.
-
In the normal course of events, I would agree but there was no change of data. The query I ran :
UPDATE ISN set languagecounter=languagecounter where languagecounter=1003
did not actually change anything.
-
What's sp level on that server?
-
SQL 2005 SP3
9.0.4035
Developer Ed
-
