SQL rewriting help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL rewriting help

Author

Topic

dikob
Starting Member

2 Posts

Posted - 2006-08-18 : 00:53:16

Hi all i'm new here and just wanted to share if anyone could have any thoughts on my delima :((glad that i found this site :))

i have these tables below:

table Aff:
Aff_ID | SiteName
----------------------
001 ------> yahoo
002 ------> msn
003 ------> espn
... so on

table Affcamps:
Aff_C | Aff_ID | Aff_Type_ID
001 ---> 002 ------> 2
222 ---> 007 ------> 3
333 ---> 001 ------> 2
002 ---> 007 ------> 2
... so on

table AffAgg:
Aff_ID | ecount | signcount | Concount | clickcount
003 --------> 200 ------> 53 ------> 56 ------> 89
001 --------> 647 ------> 64 ------> 66 ------> 87
003 --------> 54 ------> 14 ------> 15 ------> 24
002 --------> 34 ------> 5 ------> 4 ------> 3
003 --------> 12 ------> 2 ------> 1 ------> 5
... so on

now i was advise to sum the following
per Aff_ID w/ Aff_Type_ID = 2:

1. sum(ecount) = ?
2. sum(signcount) = ?
3. sum(Concount) = ?
4. sum(clickvount = ?

my querry goes like this:
SELECT af.SiteName,
aa.Aff_ID,
aa.Ecount,
ab.Scount,
ac.Ccount,
ad.Kcount,
ae.Aff_Type_ID
FROM
Aff af
INNER JOIN
(SELECT Aff_ID, sum(ecount) AS Ecount
FROM AffAgg
GROUP BY Aff_ID) aa
ON
af.Aff_ID= aa.Aff_ID
INNER JOIN
(SELECT Aff_ID, sum(signcount) AS Scount
FROM AffAgg
GROUP BY Aff_ID) ab
ON
af.Aff_ID = ab.Aff_ID
INNER JOIN
(SELECT Aff_ID, sum(Concount) AS Ccount
FROM AffAgg
GROUP BY Aff_ID) ac
ON
af.Aff_ID = ac.Aff_ID
INNER JOIN
(SELECT Aff_ID, sum(clickcount) AS kcount
FROM AffAgg
GROUP BY Aff_ID) ad
ON
af.Aff_ID = ad.Aff_ID
INNER JOIN
(SELECT DISTINCT Aff_ID, Aff_Type_ID
FROM Affcamps where Aff_Type_ID=2) ae
ON
af.Aff_ID = ae.Aff_ID
ORDER BY aa.Aff_ID

i'm sorry if my text is long but i just wanted to show the details of my request. we got 3+ mil rec. on our database thats why i need to omptimize my querry as fine as possible. my querry above runs ok but the DBA advised me to shorten my sql. i have 4 inner joins made because most of the fields i wanted to look for the sum is not indexed. i think only Aff_ID is indexed. Is there anyaway i could shorten my SQL syntax with at least the same quickness as my above querry.

thanks your help is very much appreciated :)

regards,

dennis
Reply With Quote

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-18 : 03:40:17

Replace all of your query above with this one

SELECT		a.SiteName,
		a.Aff_ID,
		ac.Aff_Type_ID,
		SUM(aa.Ecount) / COUNT(ac.Aff_ID) ECount,
		SUM(aa.SignCount) / COUNT(ac.Aff_ID) SCount,
		SUM(aa.ConCount) / COUNT(ac.Aff_ID) CCount,
		SUM(aa.ClickCount) / COUNT(ac.Aff_ID) KCount
FROM		Aff a
INNER JOIN	AffCamps ac ON ac.Aff_ID = a.Aff_ID
INNER JOIN	AffAgg aa ON aa.Aff_ID = a.Aff_ID
WHERE		ac.Aff_Type_ID = 2
GROUP BY	a.SiteName,
		a.Aff_ID,
		ac.Aff_Type_ID
ORDER BY	a.Aff_ID

Please let us know if there was a speed improvement.

Peter Larsson
Helsingborg, Sweden

dikob
Starting Member

2 Posts

Posted - 2006-08-18 : 19:53:10

hi peter,

thanks for your reply; I tried to use your method and it seems i cant get same result with what my query above display. Also it takes a long time to get the data. maybe because of how you inner join those tables that makes the difference. but anyhow i aprreciate your support on this. :)

regards,

dennis

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-21 : 01:07:06

Read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources