May 28, 2004 at 7:14 am
I have created a view that takes a details table and tally up and transform the counts into columns
table def OderItem
record id
orderid (FK)
ItemType
.
.
.
Select orderid,
SUM(CASE ItemType WHEN 'AR' THEN 1 ELSE 0 END) AS ARCount,
SUM(CASE ItemType WHEN 'CH' THEN 1 ELSE 0 END) AS CHCount,
SUM(CASE ItemType WHEN 'CF' THEN 1 ELSE 0 END) AS CFCount,
SUM(CASE ItemType WHEN 'BB' THEN 1 ELSE 0 END) AS BBCount ,
.
.
.
from OrderItem group by orderid
Trouble is I have 30+ of those sum statements and was wondering if there is another way to make this faster.
May 28, 2004 at 3:49 pm
Look at your current execution plan, could be index could help or are you meaning is there a faste way to build query itself?
May 31, 2004 at 2:13 am
There aren't many suggestions to give in this case. I have only two:
1. If you don't like writing the query by hand (and maintaining it when new types appear), you could use a procedure to build the crosstab for you:
http://www.winnetmag.com/SQLServer/Article/ArticleID/15608/15608.html
http://www.google.com/search?q=site%3Asqlservercentral.com+%22Script+Library%22+crosstab
2. If you need it to be really fast, and you don't mind a delay of 2-50ms at each modification of the base table, you could use triggers to maintain a separate table with the totals.
Razvan
May 31, 2004 at 7:18 am
I was looking for a faster query, but I guess that is asking for the sky. Table is already indexed on the type and the order id so no help on that front. I guess I can create a materialized view if I really have to. Thanx for the replies.
May 31, 2004 at 8:46 am
This is quicker I think
( roughly 20X faster on my system (tested on a random table) )
select counts.orderid,
MAX(CASE WHEN counts.ItemType = 'AR' THEN counts.cnt END) AS ARCount,
MAX(CASE WHEN counts.ItemType = 'CH' THEN counts.cnt END) AS CHCount,
MAX(CASE WHEN counts.ItemType = 'CF' THEN counts.cnt END) AS CFCount,
MAX(CASE WHEN counts.ItemType = 'BB' THEN counts.cnt END) AS BBCount
.
.
.
from
( select orderid,ItemType,count(*) as cnt from OrderItem group by orderid,ItemType ) as counts
group by counts.orderid
/rockmoose
You must unlearn what You have learnt
May 31, 2004 at 9:45 am
Thanx rockmoose, I should have thought of this...sum first then crosstab. This works much better than my current solution.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply