Multiple stored procedures,single report

Hi ,

I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.

So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.

what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable

can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.

please help me here,i am stuck

Thank you

This is somewhat interesting -- the usual complaint is "it ran okay in the query analyzer, why does it run so much slower in the report". Here you're saying "it pretty much runs exactly the same way in the report as the sum of the times in the query analyzer, and I don't like that" <g>.

So the first thing to do is to profile in the query analyzer (use the execution plans) and see what you need to optimize about that query. It sounds like whatever is in your multiple WHERE conditions needs indexes or something.

Another thing that comes to mind is checking the state of the database, especially if this db was up-converted from SQL 2000, using DBCC. I would especially look into indexes and if it is an up-convert use DBCC UPDATEUSAGE(0).

Be that as it may, this might be a situation in which we have a different way to handle it for reports. Ordinarily this is not recommended, but in your case it might work great. Here is what I have in mind:

* -- Instead of 8 procs, have one proc that returns all 8 columns and a where clause that (probably with ORs) returns the full set of data you need for your 8 tables. Now you're only going against the database once.

* -- now have your layout tables re-interpret your parameters or wherever you're getting your WHERE clauses, to filter each displayed set of data appropriately for the column they represent.

Note: proper indexes on the table, if they are not already present, will still help for that initial data pull and are still indicated. If you already have the proper indexes, try rebuilding them...

>L<