trying to get rid of clustered index scan

Question

trying to get rid of clustered index scan

Viewing 15 posts - 16 through 30 (of 30 total)

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 1

exec sp_executesql N'select TherapyAdmin_ID, VisitObservation_ID, 1 as RowVersionNumber from hcs.AdminObservation
where TherapyAdmin_ID in (select TherapyAdmin_ID from hcs.PatientOrder,hcs.TherapyAdmin where PatientOrder.Order_ID=TherapyAdmin.Order_ID
and PatientOrder.PatientVisit_ID=@Parameter1) order by VisitObservation_ID',N'@Parameter1 bigint',@Parameter1=264377212

Do you have any index on the hcs.PatientOrder table for column PatientVisit_ID? That might help out, as a variable makes use of this field. Aside from that the optimiser will choose the best plan based in part at least on the number of rows the subquery will return . If a bunch might as well scan the whole hcs.AdminObservation table. If a few then having the index that Luis suggested might give you a seek instead. There is not a real reason to be insisting that it be a certain way once you have all your index pieces in place. The optimiser handles the rest.

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

ScottPletcher SSC Guru Points: 101109 More actions · Answer 2

Create the index below. Uncomment anything you can:

CREATE /*UNIQUE*/ NONCLUSTERED INDEX AdminObservation__IX_TherapyAdmin_ID

ON hcs.AdminObservation ( TherapyAdmin_ID )

INCLUDE ( VisitObservation_ID )

WITH ( FILLFACTOR = 99, /*ONLINE = ON,*/ SORT_IN_TEMPDB = ON );

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 3

tried yours and I had tried something similar will not give up the scan

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

tcronin 95651 (12/13/2016)
tried yours and I had tried something similar will not give up the scan

People are guessing. There are at least five requests for you to post up the execution plan...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 5

tcronin 95651

SSCertifiable

Points: 6735

December 13, 2016 at 7:03 am

#1917585

just uploaded

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

tcronin 95651 (12/13/2016)
just uploaded

Thanks. Can you run this please - and post up the plan?

SELECT TherapyAdmin_ID, VisitObservation_ID, 1 as RowVersionNumber

FROM hcs.AdminObservation ao

WHERE EXISTS (

SELECT 1

FROM hcs.PatientOrder po

INNER JOIN hcs.TherapyAdmin t

ON po.Order_ID = t.Order_ID

WHERE po.PatientVisit_ID = @Parameter1

AND t.TherapyAdmin_ID = ao.TherapyAdmin_ID

)

ORDER BY VisitObservation_ID

Edit:

-- best with an index on hcs.AdminObservation:

-- CREATE INDEX ix_Stuff ON hcs.AdminObservation (TherapyAdmin_ID) INCLUDE (VisitObservation_ID)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 7

tcronin 95651

SSCertifiable

Points: 6735

December 13, 2016 at 7:19 am

#1917593

same plan and results

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

tcronin 95651 (12/13/2016)
same plan and results

Okay so we try the brute force method and relax it from there:

SELECT ao.TherapyAdmin_ID, ao.VisitObservation_ID, 1 as RowVersionNumber

FROM (

SELECT t.TherapyAdmin_ID

FROM hcs.PatientOrder po

INNER JOIN hcs.TherapyAdmin t

ON po.Order_ID = t.Order_ID

WHERE po.PatientVisit_ID = @Parameter1

GROUP BY t.TherapyAdmin_ID

) p

INNER LOOP JOIN hcs.AdminObservation ao

ON t.TherapyAdmin_ID = ao.TherapyAdmin_ID

ORDER BY ao.VisitObservation_ID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 9

Appreciate all the input, it did get rid of the scan, however below are the stats, top set is with index scan below is the seek.

(71 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'AdminObservation'. Scan count 1, logical reads 7827, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TherapyAdmin'. Scan count 99, logical reads 498, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PatientOrder'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(71 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'AdminObservation'. Scan count 7257, logical reads 23169, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TherapyAdmin'. Scan count 99, logical reads 498, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PatientOrder'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

We still need DDL for the tables and indexes.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

One, is there a difference cpu/elapsed time between the queries?

Two, scans are not always bad.

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 12

yes scan was quicker I guess the engine is smarter than the average bear

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

tcronin 95651 (12/13/2016)
Appreciate all the input, it did get rid of the scan, however below are the stats, top set is with index scan below is the seek.
(71 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AdminObservation'. Scan count 1, logical reads 7827, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TherapyAdmin'. Scan count 99, logical reads 498, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PatientOrder'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(71 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AdminObservation'. Scan count 7257, logical reads 23169, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TherapyAdmin'. Scan count 99, logical reads 498, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PatientOrder'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Can you post the plan for the loop join query please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

joeroshan SSChampion Points: 10377 More actions · Answer 14

tcronin 95651 (12/13/2016)
yes scan was quicker I guess the engine is smarter than the average bear

If your statistics are up to date the plan generated by SQL is most of the time optimal, unless some one writes really bad query.

But if this is a frequent query, its bad news. The data model may need to be reviewed since you need to touch 2800000 rows to get an answer of 77 rows.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

This is what is going to kill you in trying to improve the code:

tcronin 95651 (12/6/2016)
3rd party app no choice can't change code can change indexes

Changing indexes is not always the answer.