Performance Issue - 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

General SQL Server Forums

New to SQL Server Programming

Performance Issue

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2010-02-11 : 14:04:18

Hi Guys,

I am trying to run a query to find all those person who has a gold qualify card. I must also show these gold customer total spend in 2009,2008,2007 and 2006. The issue is that it is taking 4ever to run. Can anyone give ideas on how to improve the speed of this query?

I'm using sql server 2000 and the database has millions of rows.


select distinct c.CardNumber
	,FirstName
	,LastName
	,AddressLine1
	,AddressLine2
	,AddressLine3
	,ZipCode
	,HomePhone
	,WorkPhone
	,CellPhone
	,Email
	,CardClass
	,CycleCode
	,SUM ( case 
			when TransactionDate >= '20090101' and TransactionDate < '20100101' 
			then DailyTransactionValue
			else 0
			end
	     )spend2009
	
	,SUM ( case 
			when TransactionDate >= '20080101' and TransactionDate < '20090101' 
			then DailyTransactionValue
			else 0
			end
	     )spend2008
	,SUM ( case 
			when TransactionDate >= '20070101' and TransactionDate < '20080101' 
			then DailyTransactionValue
			else 0
			end
	     )spend2007
	,SUM ( case 
			when TransactionDate >= '20060101' and TransactionDate < '20070101' 
			then DailyTransactionValue
			else 0
			end
	     )spend2006 --into requalifyingGold
from customer c inner join SubsidiaryByCustomerByClassTransactionDetail s on c.cardnumber=s.cardnumber
where cardclass  in ('GOLDQLFY') 
group by c.CardNumber
	,FirstName
	,LastName
	,AddressLine1
	,AddressLine2
	,AddressLine3
	,ZipCode
	,HomePhone
	,WorkPhone
	,CellPhone
	,Email
	,CardClass
	,CycleCode

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 14:12:13

Is this any better (assuming that I have guessed the columns / tables correctly)


select distinct c.CardNumber
	,FirstName
	,LastName
	,AddressLine1
	,AddressLine2
	,AddressLine3
	,ZipCode
	,HomePhone
	,WorkPhone
	,CellPhone
	,Email
	,CardClass
	,CycleCode
	,SUM (spend2009) AS spend2009
	,SUM (spend2008) AS spend2008
	,SUM (spend2007) AS spend2007
	,SUM (spend2006) AS spend2006
from 
(
	cardnumber
	,SUM ( case 
			when TransactionDate >= '20090101' and TransactionDate < '20100101' 
			then DailyTransactionValue
			else 0
			end
	     )spend2009
	
	,SUM ( case 
			when TransactionDate >= '20080101' and TransactionDate < '20090101' 
			then DailyTransactionValue
			else 0
			end
	     )spend2008
	,SUM ( case 
			when TransactionDate >= '20070101' and TransactionDate < '20080101' 
			then DailyTransactionValue
			else 0
			end
	     )spend2007
	,SUM ( case 
			when TransactionDate >= '20060101' and TransactionDate < '20070101' 
			then DailyTransactionValue
			else 0
			end
	     )spend2006 --into requalifyingGold
	FROM	SubsidiaryByCustomerByClassTransactionDetail s
	WHERE	    cardclass  in ('GOLDQLFY') 
		AND TransactionDate >= '20060101'	-- Provide a hint to limit transactions "considered"
		AND TransactionDate < '20100101'
	GROUP BY cardnumber
) AS S
	JOIN  customer AS c 
		on c.cardnumber=s.cardnumber
group by c.CardNumber
	,FirstName
	,LastName
	,AddressLine1
	,AddressLine2
	,AddressLine3
	,ZipCode
	,HomePhone
	,WorkPhone
	,CellPhone
	,Email
	,CardClass
	,CycleCode

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-02-12 : 04:28:28

what indices have you on the underlying tables?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources