Hi,
I have a stored procedure and the purpose of it is to insert 15million records in to a temp table. The number of records that we have to insert in to the temp table will increase by 16K every day. It takes 12 min right now to populate this temp table.
I am calling this stored procedure to generate a dataset in SSRS 2008. Is there a way I can use this same Temp table in a different data set in the same report? I can call the same stored procedure in my second data set but I am afraid that the total execution time of the report is going to be close to 24+ min (12+12). Also I am concerned about the future execution of this report as I have 16K records growth every day in tothe temp table.
What I have done:
I enabled USE SINGLE TRANSACTION WHEN PROCESSING THE QUERIES check box but I dont think it worked. I try to make this data source as a shared data source but it throws me an error saying SQL 2008 will not support shared data source.
I thought of using regular table instead of a temp table and truncate this every time I run this report but my manager is not happy with using a regular table. So, looking for an alternative.
Anyhelp is greatly appreciated.
Thanks in advance
Ram