June 24, 2011 at 12:58 am
I am on a database in sql2000.I have a small table with 300000 records and there is a small query that runs on it:
SELECT distinct(SynthesBatchID) as SynthesBatchID, ' ' as EmailMessage FROM Synthes_Ocr
where Checked is not null
AND ((IndexDef = 'CHCK' and Location_Code = 'SWC')
OR (IndexDef in ('MCNS','FAIR','PROC','GREQ') and
Location_Code IN ('BRW','JEN','DEV')))
There are 4 existing indexes and when i run the above query, it executes in 3-4 seconds with following statistics:
Table 'Synthes_OCR'. Scan count 4, logical reads 23011, physical reads 61, read-ahead reads 21532.
I am attaching the schema details for the table and indexes that already exist. Execution plan is attached in jpeg format since, the xml format is not supported in sql 2000 so not sure how to get the plan.
June 24, 2011 at 1:03 am
This was the situation when I did not touch anything. Now, to start with, I created an index:
CREATE NONCLUSTERED INDEX [IX_Synthes_OCR_Checked_IndexDef_Loc] ON [dbo].[Synthes_OCR]
(
[IndexDef] ASC,
[Location_Code] ASC,
[Checked] ASC,
[SynthesBatchID] ASC
)
Following are the io statistics
Table 'Synthes_OCR'. Scan count 1, logical reads 2501, physical reads 0, read-ahead reads 0.
You will agree that the number of pages read have come down drastically and looks better than before.
But there is a catch. In the execution plan, I still see and index scan on the index created above.
I am a bit satisfied with the result but need your expert opinion to avoid the scan and convert this into a possible seek. I know sometimes scan is better but still request you to look into this.
I am attaching sql plan for this old version in excel format as mentioned in http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ by Gail.
June 24, 2011 at 1:15 am
I tested something else too. I took a copy of this sql 2000 database and restored it in sql 2008 version. I created the same index as above and now it shows seek operation with roughly same number of io operations.
I am surprised. Same data and same index shows scan operation in 2000 and shows seek in 2008. 😀
June 24, 2011 at 6:21 am
Moving it to 2008 updated the statistics. Try doing that on your 2000 server and see how that affects the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 26, 2011 at 6:54 am
chandan_jha18 (6/24/2011)
Same data and same index shows scan operation in 2000 and shows seek in 2008.
That's actually not a huge surprise: SQL Server 2008 is a very different product from the 2000 version. With each version, the optimizer improves and can produce a seek in many more circumstances. Complex conjunctions and disjunctions are one of those cases where work was done to enable seeking, as you have seen (a seek doesn't necessarily mean the query will perform better, of course).
Looking at the textual form of your query (from the Excel attachment), the predicate expands to:
OBJECT:([BOS].[dbo].[Synthes_OCR].[IX_Synthes_OCR_Checked_IndexDef_Loc]),
WHERE:([Synthes_OCR].[Checked]<>NULL AND (([Synthes_OCR].[IndexDef]='CHCK' AND [Synthes_OCR].[Location_Code]='SWC') OR (((([Synthes_OCR].[IndexDef]='GREQ' OR [Synthes_OCR].[IndexDef]='PROC') OR [Synthes_OCR].[IndexDef]='FAIR') OR [Synthes_OCR].[IndexDef]='MCNS')
AND (([Synthes_OCR].[Location_Code]='DEV' OR [Synthes_OCR].[Location_Code]='JEN') OR [Synthes_OCR].[Location_Code]='BRW')))))
If that looks messy, that's because it is. Regardless of optimizer improvements, the best thing to do here would be to modify the table design to be more relational, pay close attention to data types and constraints (including NOT NULL constraints), and rewrite the query to simplify the logic against the new relational design.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply