SQl Stored Procedure - View Tuning

Question

SQl Stored Procedure - View Tuning

Viewing 5 posts - 16 through 19 (of 19 total)

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

pdsqsql wrote:

Thank you Frederico.
Completely agreed with that view will be the issue as joining multiple tables and using some function which slows down the process.
But didn't not much fully understood as someone written the query and procedure who is no longer, i am new and whenever they need they have modified.
Instead of rewriting the query and i am not much expert so i have tried tuning level to clean up fragmented indexes, updating stats, adding new indexes based on query, also added covering index but didn't quite help.
Couldn't get it when you say "Multiple accesses to the same table"?
Could you please pointed out?

All of that might not help if the stored procedure is either called with "ALL" or allowed to default to "ALL". The query optimizer might decide that it's cheaper to do the Clustered Index Scans.

The key appears to be the Ord table. Since it's doing a Clustered Index Scan, I can only assume that the stored procedure was passed or allowed to default to "ALL"... is that correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

according to the plan it was called with

Parameter Compiled Value Runtime Value

[@ONum] '31856258' '31856258'

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

and I've just noticed - the view code above does not correspond to the one that the explain plan is referring to.

JD.vw_Orders is the view used on the explain plan and you supplied the code for view vw_ORDTun

can you please supply the correct view code

pdsqsql Old Hand Points: 358 More actions · Answer 4

Jeff,

Right now i am testing query with only one "ordNo" and based on timing trying to see how i can improve it but I think you are pointing to SET @OrdNo = COALESCE(@OrdNo,'ALL').

What's the other better way to use it that might help?

pdsqsql Old Hand Points: 358 More actions · Answer 5

Frederico,

Thank you!

For your comments "Parameter Compiled Value Runtime Value [@ONum] '31856258' '31856258'"

I have used EXEC procedure WITH RECOMPILE to use the newer plan using only [@ONum] '31856258'

It's the same view definition but i have just changed some of the column names and view name that's why it's show different, sorry about any inconvenience.