SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need some suggestions on query execution plan


Need some suggestions on query execution plan

Author
Message
vsamantha35
vsamantha35
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7376 Visits: 1161
Hi All,

Need some advise on the query plan analysis. There is stored proc within which a dynamic sql is formed and executed and it is taking more time when executed with concurrent user load testing. checking if any suggestions/guidelines/index recommendations or best practices around refactoring the code.

Attaching the query plan and stored proc source code.

Thanks,

Sam
Attachments
files.zip (22 views, 83.00 KB)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)

Group: General Forum Members
Points: 350573 Visits: 41656
First, why are you executing the stored procedure using dynamic SQL?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)

Group: General Forum Members
Points: 350573 Visits: 41656
Second, why are you using dynamic SQL inside the stored procedure? I am not seeing ANY reason for dynamic SQL to be used. I am seeing a lot a poor coding and difficult to read because of the formatting of the code. SQL Prompt errors trying to format the code.

A big chunk of this is a catch all query but you aren't using dynamic SQL to eliminate this aspect of the code.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)

Group: General Forum Members
Points: 350573 Visits: 41656

This snippet doesn't even make sense:
AND (NOT EXISTS (SELECT 1 FROM @SampleStatusId) OR samp.SampleCondition IN (SELECT id FROM @SampleStatusId))

EDIT: Nevermind, it does now.



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)

Group: General Forum Members
Points: 350573 Visits: 41656
I stand corrected, I got to the end of code and found why it is dynamic SQL, your ORDER BY clause can use a different column on each instantiation of the procedure.

Even with this, you could probably put it all in one procedure.
Still, the query will result in table scans on several tables where you are using LIKE '%' _ some variable + '%'.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
vsamantha35
vsamantha35
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7376 Visits: 1161
Hi Lynn,

Code has been written some 3rd party consultant. Is there a way to convert it into normal sql?

Thanks ,
Sam
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)

Group: General Forum Members
Points: 350573 Visits: 41656
vsamantha35 - Thursday, December 21, 2017 2:14 PM
Hi Lynn,

Code has been written some 3rd party consultant. Is there a way to convert it into normal sql?

Thanks ,
Sam


First, due to the dynamic nature of the final code (i.e. the sort column can change), it still must be dynamic. Second, yes, it could be rewritten but it will take effort to accomplish and not something I could work on until I am off work. Even then, do I want to expend the effort for free? Maybe, but it is the last weekend before Christmas and I have a little shopping to complete and that will take priority as will going to the movies this weekend (Payday and Bonus!).

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (830K reputation)SSC Guru (830K reputation)SSC Guru (830K reputation)SSC Guru (830K reputation)SSC Guru (830K reputation)SSC Guru (830K reputation)SSC Guru (830K reputation)SSC Guru (830K reputation)

Group: General Forum Members
Points: 830976 Visits: 48498
You can do a dynamic sort without dynamic SQL. Not going to be fast, but that's the price you pay for such code.

ORDER BY
CASE @Ordering WHEN 'Column1' THEN Column1 .....

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


Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)

Group: General Forum Members
Points: 350573 Visits: 41656
GilaMonster - Thursday, December 21, 2017 2:24 PM
You can do a dynamic sort without dynamic SQL. Not going to be fast, but that's the price you pay for such code.

ORDER BY
CASE @Ordering WHEN 'Column1' THEN Column1 .....


Ah, yes, but I was definitely not thinking that way. That could get messy depending on how many different columns might be used. And then having to update the code when a new column or columns are needed.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jason A. Long
Jason A. Long
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16516 Visits: 6824
This looks a lot like the thread you started here... https://www.sqlservercentral.com/Forums/1911062/Help-to-reduce-stored-procedure-execution
I'm not sure why anyone would want to continue answering questions for you when you bail out on the threads you've already started and ignore the answers you've been given...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search