Creating Summary Table - from multiple tables

I have a report database server which is accessed by thousands of
customers. I am wanting to make a summary table of some of our most
accessed information. I was wanting to create a indexed view but it
has so many limitations that I am unable to use it (no unions,
subqueries, outer joins...)
I was able take a stored procedure with a bunch of temp tables and
rewrite this summary procedure into one big sql statement. However, as
I found out indexed views would not allow me to use all the sql
features which made this query possible.
I thought about using a trigger but I don't think I could use it
because the summary table is based off of 5 tables and all of the
information must exists before the summary can take place. This is
because it uses a weighted value of information from 3 of the 5 times
to get the final answers needed.
I have posted the query below. I changed this query so many times
trying to get the indexed view to work (before learning all the
limitations of it). This query can be shortened with the use of a
union statement. Also, the query is not finalized, but it has enough
information there that you can see the complex mess. The date range is
just in the query to make it run faster while trying to find something
that will work.
I am interested in any ideas that you may have.
Thanks
select customerid, ordid, cycle, orderid, eventid,
subtotal*allocatepercent as subtotal,
shiptotal*allocatepercent as shiptotal,
taxtotal*allocatepercent as taxtotal,
discount*allocatepercent as discount,
adjustment*allocatepercent as adjustment,
tendered*allocatepercent as tendered
from (
select customerid, ordid, cycle, a.orderid, a.eventid,
coalesce(subtotal, 0) as subtotal, coalesce(shiptotal, 0) as shiptotal,
coalesce(taxtotal, 0) as taxtotal, coalesce(discount, 0) as discount,
coalesce(adjustment, 0) as adjustment, coalesce(tendered, 0) as
tendered, b.retailvalue, c.retailvalue as totalretailvalue, case when
c.retailvalue=0 then b.retailvalue/events else
b.retailvalue/c.retailvalue end as allocatepercent
from
( select distinct customerid, ordid, cycle,
a.orderid, eventid, coalesce(subtotal, 0) as subtotal,
coalesce(shiptotal, 0) as shiptotal, coalesce(taxtotal, 0) as taxtotal,
coalesce(discount, 0) as discount, coalesce(adjustment, 0) as
adjustment, coalesce(tendered, 0) as tendered
from dbo.[order] a
inner join dbo.orderdetail b on
a.orderid=b.orderid
where recvdate>'1/20/06'
group by customerid, ordid, cycle, a.orderid,
eventid, coalesce(subtotal, 0), coalesce(shiptotal, 0),
coalesce(taxtotal, 0), coalesce(discount, 0), coalesce(adjustment, 0),
coalesce(tendered, 0)
) a
inner join (
select a.orderid, a.eventid,
sum(coalesce(b.retailvalue, 0) + coalesce(c.retailvalue, 0)) as
retailvalue
from (
select distinct
a.customerid, a.ordid, a.cycle, a.orderid, b.eventid,
coalesce(subtotal, 0) as subtotal, coalesce(shiptotal, 0) as shiptotal,
coalesce(taxtotal, 0) as taxtotal, coalesce(discount, 0) as discount,
coalesce(adjustment, 0) as adjustment, coalesce(tendered, 0) as
tendered
from dbo.[order] a
inner join
dbo.orderdetail b on a.orderid=b.orderid
where
recvdate>'1/20/06'
) a
left outer join (
select a.orderid, eventid, sum(coalesce(retailvalue, 0)) as retailvalue
from dbo.[order] a
inner join dbo.orderdetail b on a.orderid=b.orderid
where recvdate>'1/20/06' and orderpackagesid is null
group by a.orderid, eventid
) b on a.orderid=b.orderid and a.eventid=b.eventid
left outer join (
select orderid, eventid, sum(retailvalue) as retailvalue
from (
select orderid, eventid, coalesce(retailvalue, 0) as
retailvalue
from (
select distinct customerid, ordid, cycle,
a.orderid, eventid, orderpackagesid
from dbo.[order] a
inner join dbo.orderdetail b on
a.orderid=b.orderid
where recvdate>'1/20/06' and orderpackagesid is
not null
) a
inner join dbo.orderpackages b on
a.orderpackagesid=b.orderpackagesid
) a
group by orderid, eventid
) c on a.orderid=c.orderid and a.eventid=c.eventid
group by a.orderid, a.eventid
) b on a.orderid=b.orderid and
a.eventid=b.eventid
inner join (
select a.orderid, count(a.eventid)
as events, sum(coalesce(b.retailvalue, 0) + coalesce(c.retailvalue, 0))
as retailvalue
from (
select distinct
a.customerid, a.ordid, a.cycle, a.orderid, b.eventid,
coalesce(subtotal, 0) as subtotal, coalesce(shiptotal, 0) as shiptotal,
coalesce(taxtotal, 0) as taxtotal, coalesce(discount, 0) as discount,
coalesce(adjustment, 0) as adjustment, coalesce(tendered, 0) as
tendered
from dbo.[order] a
inner join
dbo.orderdetail b on a.orderid=b.orderid
where
recvdate>'1/20/06'
) a
left outer join (
select a.orderid, eventid, sum(coalesce(retailvalue, 0)) as retailvalue
from dbo.[order] a
inner join dbo.orderdetail b on a.orderid=b.orderid
where recvdate>'1/20/06' and orderpackagesid is null
group by a.orderid, eventid
) b on a.orderid=b.orderid and a.eventid=b.eventid
left outer join (
select orderid, eventid, sum(retailvalue) as retailvalue
from (
select orderid, eventid, coalesce(retailvalue, 0) as
retailvalue
from (
select distinct customerid, ordid, cycle,
a.orderid, eventid, orderpackagesid
from dbo.[order] a
inner join dbo.orderdetail b on
a.orderid=b.orderid
where recvdate>'1/20/06' and orderpackagesid is
not null
) a
inner join dbo.orderpackages b on
a.orderpackagesid=b.orderpackagesid
) a
group by orderid, eventid
) c on a.orderid=c.orderid and a.eventid=c.eventid
group by a.orderid
) c on a.orderid=c.orderid
) aLook up how DB2 implements MQTs with incremental refresh, and how
Oracle's materialized views are refreshed on commit. You can do it
yourself, like this:
create table customer(CustomerId int)
insert into customer select 1 union select 2 union select 3
go
create table orders(CustomerId int, amount float)
insert into orders select 1, 15.0 union select 2, 30.0
go
-- populate the summary initially
select CustomerID,
coalesce((select sum(amount) from orders o where o.CustomerID =
c.CustomerID), 0) SumOrder
into customer_summary
from customer c
go
-- start tracking relevant changes
select CustomerId, CustomerId InsOrDel into customer_log from customer
where (0=1)
select CustomerId, amount into orders_log from orders where (0=1)
go
create trigger customer_ins
on customer after insert
as
begin
insert into customer_log
select CustomerId, 1
from inserted
end
go
create trigger customer_del
on customer after delete
as
begin
insert into customer_log
select CustomerId, -1
from deleted
end
go
create trigger orders_ins
on orders after insert, update, delete
as
begin
insert into orders_log
select CustomerId, sum(amount)
from inserted
group by CustomerId
insert into orders_log
select CustomerId, -sum(amount)
from deleted
group by CustomerId
end
go
insert into orders select 1, 10.0 union select 3, 20.0
select * from orders_log
update orders set amount = amount + 1 where customerId = 1
select * from orders_log
delete from orders where amount between 10.0 and 12.0
select * from orders_log
insert into customer select 4 union select 5
insert into orders select 4, 1.0 union select 5, 1.0
delete from orders where customerId = 2
delete from customer where customerId = 2
go
-- refresh the summary
select * from customer_summary
go
insert into customer_summary
select CustomerId, 0 from Customer_log where InsOrDel=1
delete from customer_summary where CustomerId in(
select CustomerId from Customer_log where InsOrDel=-1)
update customer_summary set SumOrder = customer_summary.SumOrder +
ChgLog.SumOrder
from customer_summary,
(select customerId, sum(amount) SumOrder from orders_log
group by customerId) ChgLog
where customer_summary.customerId = ChgLog.customerId
go
select * from customer_summary
-- clear the logs
truncate table orders_log
truncate table customer_log
go
-- verify that the summary is up-to-date
select CustomerID,
(select sum(amount) from orders o where o.CustomerID = c.CustomerID)
SumOrder
from customer c
go
drop table customer
drop table orders
drop table orders_log
drop table customer_log
drop table customer_summary
go

Creating indexes on temp tables in stored procedures

Help,

I have a complex stored procedure (>1000 lines) that uses multiple temp tables with thousands of rows. These temp tables are joined together, and selected from many times.

I tried to improve the performance of the procedure by createing the recommended indexes on my temp tables, but the query plan ignores the indexes and continues to use table scans.

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Developer Edition (Build 2600: Service Pack 2)

Can you provide some samples of the queries your issuing against these tables?
And maybe some examples of the tables...

|||

The temp tables are used in a cursor (trust me, there's no other way). So these tables of upwards of 200,000 rows each need to be joined, to pull out just a few rows at a time. The tables are fairly simple, around 10 columns each. My issue is that SQL Server doesn't pay attention to indexes created on temp tables because the execution plan is predetermined (I think) and it doesn't take into account an index that doesn't exist at compile-time.

I may have solved my issue by creating another stored procedure that I call from the first. this sub-stored procedure creates indexes on the temp tables (that's all it does) and it looks like the execution plan is no longer doing table scans to join two tables together... it appears to be using the indexes I've created. If you have an easier way to get SQL to use a just-created index, I'm all ears.

FYI: pet peeve of mine

your = something you own (posessive)

you're = conjunction form of "you are"

|||

First off, if you are going to punish people for grammer (yes, that is grammar then you are going to be sorely dissappointed. First off no spell check. Second, no pay. Third, well, come on it is just a bit of help.

Second: the biggest pet peeve of them all around here is not posting your code and DDL so we can look at what you are doing.

Third: "The temp tables are used in a cursor (trust me, there's no other way)." This is rarely true. Almost any cursor can be dealt with in set based code. Some order based accumulations are faster in cursors (or so I have heard , but I haven't written a cursor for a non-system function in years.

You could try adding a hint to your query to force index use. That might work. Try the WITH RECOMPILE hint on the proc too. If nothing else, you can try declaring the cursor in a dynamic SQL call:

declare @.cursorDeclare varchar(max)

set @.cursorDeclare = 'declare bob cursor global for select ''hi'' as hi

open bob'

exec (@.cursorDeclare)

fetch next from bob

That might do the trick. Or, if there are just a few rows to be returned, this might work to spool the dynamic query into a temp table:

create table #tempper

(

value varchar(10)

)

insert into #tempper

exec ('select ''value''')

select *

from #tempper

|||Here, here to the cursor advice; Cursors Are Loathesome.