Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
Posted Sunday, January 27, 2013 12:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 12,906, Visits: 31,984
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)


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1412143
Posted Sunday, January 27, 2013 1:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:38 AM
Points: 7,004, Visits: 8,448
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 )



Johan


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