Multiple stored procedures,same set of tables

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

first identified the common parameters from All Queries. and create a new Query and move all data in a Temp table.

and apply where clause on that temp table. this will reduce your query time.

Example.

select *

into #temp

from table1 inner join table2 on table1.t1 = table2.t1

where x= 'abc'

select * from #temp where a='aaa'

select * from #temp where a='aad'

select * from #temp where a='aaf'

select * from #temp where a='aag'

select * from #temp where a='aagg' and b ='aaa'

drop table #temp

from that way you hit database only one time and rest of the queries will be perform on small temparary data table.

|||

As long as concurrency isn't a problem, I would consider having one stored procedure build up a permanent table in tempdb, and then have the subsequent calls use that object. It will require timing to make sure that your child stored procedures don't start before the first one gets started, but it will give you the set of data (with indexes, if you need them) that can be accessed across any number of processes.

Of course, since the temp table has a given name, you can't have multiple processes running the report at the same time, You could use dynamic SQL if you had to and use a GUID for the reporting table's name in that case, though it would significantly ugly up your code.