-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|