SQL Query - 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 2005 Forums

Transact-SQL (2005)

SQL Query

Author

Topic

mauler05
Starting Member

17 Posts

Posted - 2008-01-07 : 10:40:51

My stored procedure contains following 4 queries. Later on in the code i am using the resultset and populating a table with the registrant count of all the cities and then the top 5 city.

The first 2 quries r similar to last 2 execpet the boolean Isregistration = true or false.

Is there some way i can optimize this procedure to run even faster ?

SELECT Count(City) As RegCntAllCities
FROM [GSK-IMAP].[dbo].[FactActivity] fa
JOIN dbo.DimBizFinUnit bfu ON bfu.[BFUKey] = fa.[BFUKey]
JOIN dbo.DimBrand b ON b.[BrandKey] = fa.[BrandKey]
JOIN dbo.DimCampaign c ON c.CampaignKey = fa.CampaignKey
JOIN dbo.DimUser du ON du.UserKey = fa.UserKey
JOIN dbo.DimDate d ON d.DateKey = fa.DateKey
WHERE bfu.BFUKey = @BFUKey
AND b.BrandKey = @BrandKey
AND c.CampaignKey = @CampaignKey
AND d.CalendarDate BETWEEN @StartDate and @EndDate
AND fa.RegistrationCt = 0

SELECT TOP 5 City, Count(City) AS RegCntByCity
FROM [GSK-IMAP].[dbo].[FactActivity] fa
JOIN dbo.DimBizFinUnit bfu ON bfu.[BFUKey] = fa.[BFUKey]
JOIN dbo.DimBrand b ON b.[BrandKey] = fa.[BrandKey]
JOIN dbo.DimCampaign c ON c.CampaignKey = fa.CampaignKey
JOIN dbo.DimUser du ON du.UserKey = fa.UserKey
JOIN dbo.DimDate d ON d.DateKey = fa.DateKey
WHERE bfu.BFUKey = @BFUKey
AND b.BrandKey = @BrandKey
AND c.CampaignKey = @CampaignKey
AND d.CalendarDate BETWEEN @StartDate and @EndDate
AND fa.RegistrationCt = 0
GROUP BY City
Order BY RegCntByCity desc

SELECT Count(City) As NewRegCntAllCities
FROM [GSK-IMAP].[dbo].[FactActivity] fa
JOIN dbo.DimBizFinUnit bfu ON bfu.[BFUKey] = fa.[BFUKey]
JOIN dbo.DimBrand b ON b.[BrandKey] = fa.[BrandKey]
JOIN dbo.DimCampaign c ON c.CampaignKey = fa.CampaignKey
JOIN dbo.DimUser du ON du.UserKey = fa.UserKey
JOIN dbo.DimDate d ON d.DateKey = fa.DateKey
WHERE bfu.BFUKey = @BFUKey
AND b.BrandKey = @BrandKey
AND c.CampaignKey = @CampaignKey
AND d.CalendarDate BETWEEN @StartDate and @EndDate
AND fa.RegistrationCt = 1

SELECT TOP 5 City, Count(City) AS NewRegCntByCity
FROM [GSK-IMAP].[dbo].[FactActivity] fa
JOIN dbo.DimBizFinUnit bfu ON bfu.[BFUKey] = fa.[BFUKey]
JOIN dbo.DimBrand b ON b.[BrandKey] = fa.[BrandKey]
JOIN dbo.DimCampaign c ON c.CampaignKey = fa.CampaignKey
JOIN dbo.DimUser du ON du.UserKey = fa.UserKey
JOIN dbo.DimDate d ON d.DateKey = fa.DateKey
WHERE bfu.BFUKey = @BFUKey
AND b.BrandKey = @BrandKey
AND c.CampaignKey = @CampaignKey
AND d.CalendarDate BETWEEN @StartDate and @EndDate
AND fa.RegistrationCt = 1
GROUP BY City
Order BY NewRegCntByCity desc

I appreciate any help in adavance.

Thanks...

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-07 : 11:01:33

What are the indexes on the tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-07 : 11:25:42

Try executing like this & compare performance:-



CREATE TABLE #Temp
(City varchar(20),
RegCnt int,
NewRegCnt int) 


INSERT INTO #Temp
SELECT 
City, 
Sum(CASE WHEN fa.RegistrationCt = 0 THEN 1 ELSE 0 END),
Sum(CASE WHEN fa.RegistrationCt = 1 THEN 1 ELSE 0 END)
FROM [GSK-IMAP].[dbo].[FactActivity] fa
JOIN dbo.DimBizFinUnit bfu ON bfu.[BFUKey] = fa.[BFUKey]
JOIN dbo.DimBrand b ON b.[BrandKey] = fa.[BrandKey]
JOIN dbo.DimCampaign c ON c.CampaignKey = fa.CampaignKey
JOIN dbo.DimUser du ON du.UserKey = fa.UserKey
JOIN dbo.DimDate d ON d.DateKey = fa.DateKey
WHERE bfu.BFUKey = @BFUKey
AND b.BrandKey = @BrandKey
AND c.CampaignKey = @CampaignKey
AND d.CalendarDate BETWEEN @StartDate and @EndDate
AND (fa.RegistrationCt = 0 OR fa.RegistrationCt =1)
GROUP BY City



SELECT SUM(RegCnt) AS 'RegCntAllCities',--analogous to 1st &3rd query
SUM(NewRegCnt) AS 'NewRegCntAllCities'
FROM #Temp

SELECT TOP 5 t.City,t.RegCnt AS 'RegCntByCity' --2nd query
FROM #Temp t
ORDER BY t.RegCnt DESC

SELECT t.City,t.NewRegCnt AS NewRegCntByCity --4th query
FROM #Temp t
ORDER BY t.NewRegCnt  DESC

mauler05
Starting Member

17 Posts

Posted - 2008-01-07 : 17:03:43

Thanks..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources