Which plan is the most efficient one?

Question

Which plan is the most efficient one?

Unsure

SSC Enthusiast

Points: 160
More actions
July 16, 2008 at 12:33 pm

#189692

Hello,
I have been trying to fine tune a query that involves this one table. The table contains 725,027 rows.
Plan 1
The original query completes in 6.2 seconds. The optimizer makes use of the clustered index. Performs a clustered index scan. The primary key is not used in the query.
Physical operation: Clustered Index Scan
Row Count: 725,027
I/O cost: 6.73
CPU cost: 0.39
Number of executes: 2
Cost: 3%
Estimated row count: 723,244
Plan 2
I created a covering index. Completes in 1.72 seconds. Uses the covering index but performs an index scan.
Physical operation: Index Scan
Row Count: 725,027
I/O cost: 3.09
CPU cost: 0.79
Number of executes: 1
Cost: 11%
Estimated row count: 723,244
Plan 3
I used 'set forceplan'. Completes in 2.31 seconds. Uses the covering index. Performs an Index Seek.
Physical operation: Index Seek
Row Count: 20,215
I/O cost: 0.00320
CPU cost: 0.000080
Number of executes: 10449
Cost: 22%
Estimated row count: 1
Plan 3 performs a seek, I/O and CPU is good BUT the number of executes is too high. Reads are high.
Would like to know which plan is ideal with regard to this table?
Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Perhaps you would like to post the query, the table structure and the indexes?

For now, it looks like the plan that the optimiser picked (the coverign index with a scan) is the most optimal. It could possibly be improved further, but that requires an look at the query.

I'm not fond of hints. I usually advice people to avoid hints unless they know that thy know better than the optimiser. An index scan is not always bad and an index seek is not always good.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 2

I have a similar question so rather I post a new one I would like to add here.

I want to know if the following query is correct with respect to performance, we are working on performance; I am not sure that following T-SQL may participate in de-grading performance or its OK?

select media_title from tbl_media m, tbl_Schedule s where media_category_id =34 AND '7/14/2008' BETWEEN s.start_date AND s.end_date

I need advice from experts. My own openion is, I must correct such kind of all queries. and definitely may grain raise in performance. the above query retruns 40K rows, wherein requirement might be only to find title (1 record).

My suggestion is:

select media_title from dbo.tbl_media m inner join dbo.tbl_Schedule s on m.media_ID = s.Media_ID

where m.media_category_id =34

AND '7/14/2007' BETWEEN s.start_date AND s.end_date

BTW, i have still have concernt about date range style used above. How it would be corrected if i have to see @DateTimeInput between Program Start and End Time?

Shamshad Ali.

Unsure SSC Enthusiast Points: 160 More actions · Answer 3

Thank you.

I will not use 'forceplan'. Will work with just the covering index.

Thanks again.

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 4

And what about the style for selecting data between start and end?

----------------------------------------------------

AND 7/14/2008 BETWEEN s.start_date AND s.end_date

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Shamshad Ali (7/17/2008)
And what about the style for selecting data between start and end?
----------------------------------------------------
AND 7/14/2008 BETWEEN s.start_date AND s.end_date

Please start a separate thread for your questions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Unsure (7/17/2008)
Thank you.
I will not use 'forceplan'. Will work with just the covering index.
Thanks again.

If you want any suggestions on improving the query more, please post the query, along with the table and index definitions

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass