Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

facing serious performance issues, please see the scenario below need urgent help - Please chk the code below Expand / Collapse
Posted Sunday, January 27, 2013 12:19 PM



Group: General Forum Members
Last Login: Yesterday @ 8:07 PM
Points: 14,543, Visits: 38,400
chris@home gave you teh best advice so far: if you can rewrite teh query, redo it so that it is 5 unions, using the agregate example he already put to gether for you;

you are looking at at least an order of magnitude improvement doing it that way(ie 6 seconds instead of 60)


help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1412143
Posted Sunday, January 27, 2013 1:29 PM



Group: General Forum Members
Last Login: Tuesday, October 25, 2016 8:37 AM
Points: 6,822, Visits: 8,830
just my 2ct, to add to previous responders replies:

In your sproc, chances are filter arguments aren't sniffed to your advantage at query plan generation time.
Reason for that is you are parsing and completing the filter arguments within the sproc itself.
Try handing there parameters over as needed by the sproc itself.
i.s. try writing your sproc so it accepts the needed filter data as input paramters and see how that influences the query plan.
It may even be enough to add 'with recompile' to the detail query ( please implement the grouping and union all advice )


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution"

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1412147
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse