Anyway other faster way to do this?

Question

Anyway other faster way to do this?

Francesco Chu

Grasshopper

Points: 12
More actions
May 28, 2004 at 7:14 am

#162592

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Look at your current execution plan, could be index could help or are you meaning is there a faste way to build query itself?

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 2

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

Francesco Chu Grasshopper Points: 12 More actions · Answer 3

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.

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 4

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

Francesco Chu Grasshopper Points: 12 More actions · Answer 5

Thanx rockmoose, I should have thought of this...sum first then crosstab. This works much better than my current solution.