Reporting from a DataWarehouse

Hi.
I'm creating reports from a datawarehouse, I hope that term doesn't
mean something different to every reader.
Anyway...I have a section of the report that gets information by
executing 63 separate sql statements that populate 63 cells in an Excel
spreadsheet.
The queries started out being separate queries, then I changed to
separate stored procedures.
The queries get their data from essentially 1 table with 2 other
suporting tables, such that the query has 2 joins. It also has 3-5
pieces in the where clause.
So I took out many of the where clause fields, put them in the select
section, and made a table out of the results.
Now I have an sp that runs 63 separate queries "inside" 1 sp and it
returns all the results I need. The queries get data from 1 table and
have far fewer parts to the where clause. Also I get to index to my
hearts content. So I'm getting a huge improvement in performance.
(Sorry I'm being so deliberate here.)
Finally! Here's my question.
Instead of making a table should I use a temporary table? Would I get
even more performance from a temp table? What advantages do temp tables
have?You will probably have better results if you can reduce the number of
queries. If you can get the 63 elements in fewer queries by combining the
common elements (JOIN , WHERE) you can probably get down to 10, 5, 2, or
maybe even 1 query. THAT is where I would expect you to see an improvement
in performance.
Keith
"Bob" <Go1369@.Yahoo.Com> wrote in message
news:1111502187.310046.5970@.g14g2000cwa.googlegroups.com...
> Hi.
> I'm creating reports from a datawarehouse, I hope that term doesn't
> mean something different to every reader.
> Anyway...I have a section of the report that gets information by
> executing 63 separate sql statements that populate 63 cells in an Excel
> spreadsheet.
> The queries started out being separate queries, then I changed to
> separate stored procedures.
> The queries get their data from essentially 1 table with 2 other
> suporting tables, such that the query has 2 joins. It also has 3-5
> pieces in the where clause.
> So I took out many of the where clause fields, put them in the select
> section, and made a table out of the results.
> Now I have an sp that runs 63 separate queries "inside" 1 sp and it
> returns all the results I need. The queries get data from 1 table and
> have far fewer parts to the where clause. Also I get to index to my
> hearts content. So I'm getting a huge improvement in performance.
> (Sorry I'm being so deliberate here.)
> Finally! Here's my question.
> Instead of making a table should I use a temporary table? Would I get
> even more performance from a temp table? What advantages do temp tables
> have?
>