Optimize Temp tables

Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
With that much code running just to generate a report, you might consider
having a background process (or a data warehouse) pre-aggregate the data.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||In addition to Aaron's response , create a temp tables at the beginning of
the stored procedure (reduce recompile).
Also, it may help to improve performance if you add NC/CI indexes depending
on your requrements.
Look at an execution plan of the stored procedure to ensure that optimizer
is available to use indexes.
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
> Hi All,
> My application makes heavy use of temp tables because we have a few
> thousands of stored proc running every day to renerate reports.
> What are the positive and negative impact to add clustered index on the
> temp table?
> what is the negative impact if I use derived table instead temp table?
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
> Thanks so much,
> Silaphet,
>|||Another possibility would be to consider an INDEXED VIEW.
Typically, you want to keep your OLTP and DSS/OLAP operations and data
seperated. It might be wise to reconsider the cohosting of this data in the
same database and reconsider creating something new elsewhere that better
fits your architecture.
Sincerely,
Anthony Thomas
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,