| Author |
Topic |
|
porsche997
Starting Member
6 Posts |
 Posted - 2008-06-04 : 06:15:43
|
| Hi,I have a report which contains several queries - one of which is a stored procedure which creates a Temp table in the database. This temp table is then used by the other queries. The temp table provides the lowest granularity of data, and the subsequent queries aggregate it at different levels in order to produce the report charts and matrix's.The issue is that when I run the report, the stored procedure does not create the temp table before the other queries start using it. In fact, I need to run the report, and then refresh it in order to get the report to pull in the correct data (1st run populates the temp table, refresh then allows the queries to use it.).Is there a way to force the execution of the stored procedure before the other queries run?...I don't want to create stored procedures for each query, because the intitial creation of the Temp table is quite slow (5seconds), and to do this for each data subset would be very resource intensive.ThanksKevin. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-04 : 06:18:15
|
| So on what order are you calling the queries and sp? Are they source for same dataset? |
 |
|
|
porsche997
Starting Member
6 Posts |
Posted - 2008-06-04 : 06:29:40
|
| Hi,I have not been able to set the order for the stored procedure or the queries in SSRS, i.e. I have 5 datasets defined for the report - the first two are for enabling the user to choose paramters, then the Stored procdeure dataset is listed, then two queries that use the temp table (created by the stored procedure).So I have 5 datasets in all. I don't know if SSRS runs them in that order, but even if it did, the Stored procedure takes about 5 seconds to run, and the other queries could potntially be running before the temp table has been fully populated?Is this what you meant? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-04 : 06:51:30
|
quote:
Originally posted by porsche997
Hi,I have not been able to set the order for the stored procedure or the queries in SSRS, i.e. I have 5 datasets defined for the report - the first two are for enabling the user to choose paramters, then the Stored procdeure dataset is listed, then two queries that use the temp table (created by the stored procedure).So I have 5 datasets in all. I don't know if SSRS runs them in that order, but even if it did, the Stored procedure takes about 5 seconds to run, and the other queries could potntially be running before the temp table has been fully populated?Is this what you meant?
nope you cant guarantee order in which SSRS populates dataset. I was asking wheteher stored procedure dataset is not used by any dataregions and is only for population of temp table to be used by other queries? |
|
|
|
porsche997
Starting Member
6 Posts |
Posted - 2008-06-04 : 07:05:51
|
| O.k. got your drift now...The stored procedure temp table is not currently used within the report i.e. for charts etc, but it possibly will be.Cheers, |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-04 : 07:08:50
|
quote:
Originally posted by porsche997
O.k. got your drift now...The stored procedure temp table is not currently used within the report i.e. for charts etc, but it possibly will be.Cheers,
What i would have done is to wrap all the other queries within stored procedure and call Exec SPname inside them to prepopulate the temp table. |
|
|
|
porsche997
Starting Member
6 Posts |
Posted - 2008-06-04 : 07:32:15
|
| Hi,I thought about that, but would that mean that I am effectivley re-populating the temp table for each query? It is the creation of the temp table that takes time, and I was keen to avoid genreating that data more than once.Kev. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-04 : 07:54:34
|
quote:
Originally posted by porsche997
Hi,I thought about that, but would that mean that I am effectivley re-populating the temp table for each query? It is the creation of the temp table that takes time, and I was keen to avoid genreating that data more than once.Kev.
Will the data in the temp table be changing frequently? Or will it be static? |
|
|
|
porsche997
Starting Member
6 Posts |
Posted - 2008-06-04 : 08:09:07
|
| the data reflect a particular sites information for a 12month period ending on a date the user chooses in the report - so the site id, and the 12 month period are parameters in the stored procedure.The source table for the temp table SP is millions of rows - hence the need for the temp table. Once generated, the temp table services all of the remaining queries in the report.I think the only way to do this, might be to create temp tables for each query...not a problem, but not best use of the server resources?Kev. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-04 : 08:27:48
|
quote:
Originally posted by porsche997
the data reflect a particular sites information for a 12month period ending on a date the user chooses in the report - so the site id, and the 12 month period are parameters in the stored procedure.The source table for the temp table SP is millions of rows - hence the need for the temp table. Once generated, the temp table services all of the remaining queries in the report.I think the only way to do this, might be to create temp tables for each query...not a problem, but not best use of the server resources?Kev.
Is it possible to keep the details of all the sites for all available days in a table which will keep on adding new information one time each day? SO that you can use the table directly in the other queries and filter it based on parameters passed in query itself. this is just a suggestion. I dont know how feasible this is as i'm not sure how volatile the data would be. |
|
|
|
porsche997
Starting Member
6 Posts |
Posted - 2008-06-04 : 08:39:24
|
| Yeh, this is possible; in fact I used to run an overnight SSIS package to create the table and use that for this very report. However, users can load 'new' site data during the day, and are not happy to wait for the overnight process to take place before they run this report - hence I am re-vising how it works.The second problem with pr-populating the temp table, is that it effectively has to hold data for all sites and all periods so a report can be run for any of them - so no performance gain is to be had.I don't even think I can produce temp table for each of the queries when i think about it, because the report would still run before they had all been populated. There must be a way around this...i just can;t think of it!...Do you know if the custom code properties of the report can be used to force the stored procedure to run before the queries?. (I'm grasping at straws now!!)thanks for your help by the way.Kev. |
|
|
|
|
SSRS - Run Stored Procedure before other datasets