I would break it down and have alook at how long each query section is
taking
have a look at how long
SELECT TOP 100 CustomerID,
FROM Transactions
WHERE DATEPART(month,transactionDate)=DATEPART(month,DATEADD(month, -1,
GETDATE()))
AND DATEPART(year,transactionDate)=DATEPART(year,
DATEADD(month, -1,GETDATE()))
AND ClubID = t1.ClubID
GROUP BY CustomerID
ORDER BY SUM(transactionValue) DESC
takes to process
also it looks like your doubling up on alot of stuff like your date
comparisons
You could try something like this
============================================
SELECT ClubID,
CustomerID,
COUNT(*) AS Transactions,
SUM(transactionValue) AS TotalValue
FROM Transactions t1
WHERE MemberID IS IN (
SELECT TOP 100 CustomerID,
FROM Transactions
WHERE transactionDate BETWEEN
DATEPART(month,DATEADD(year, -1,
GETDATE())) + '-'
DATEPART(month,DATEADD(month, -1, GETDATE())) + '-1'
AND
DateAdd(month,1,DATEPART(year,DATEADD(month, -1, GETDATE())) + '-'
DATEPART(month,DATEADD(month, -1, GETDATE())) + '-1')
)
GROUP BY ClubID, CustomerID
ORDER BY ClubID, SUM(transactionValue) DESC
=============================================
Steve
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Michael
Lambino
Sent: Sunday, December 21, 2003 11:13 PM
To: CFAussie Mailing List
Subject: [cfaussie] RE: OT - SQL Mindbender
----- Original Message -----
From: "Adam Chapman" <[EMAIL PROTECTED]>
To: "CFAussie Mailing List" <[EMAIL PROTECTED]>
Sent: Friday, July 25, 2003 3:05 PM
Subject: [cfaussie] RE: OT - SQL Mindbender
Hi Tim,
I had a query similar to yours.. And it took a very
Long time to run.. The culprit was the sub-query.
Try replacing the subquery with an inner join.. Also
Change count(*) to count one particular field..
Eg: COUNT(TransactionID). Some table indexes might also
Speed things up somewhat..
Hope this is of some use..
Adam
-----Original Message-----
From: Tim Rox [mailto:[EMAIL PROTECTED]
Sent: Friday, July 25, 2003 1:31 PM
To: CFAussie Mailing List
Subject: [cfaussie] OT - SQL Mindbender
hi there,
Am having some SQL headaches with quite a big database.
I want to find the top 100 spenders during the previous month for each
of a number of 'clubs'.
I have a table full of transactions with a clubID, customerID, date,
amount of the transaction.
Unfortunately I can't use cold fusion for this one, so all the hard work
has to be done in SQL.
My attempt below seems to take an indefinate amount of time. Any
suggestions on how it could be done more efficiently? I feel like the
subquery is duplicating a lot of work that the main query is doing.
SELECT ClubID, CustomerID, COUNT(*) AS Transactions,
SUM(transactionValue)
AS TotalValue
FROM Transactions t1
WHERE DATEPART(month, transactionDate)=DATEPART(month, DATEADD(month,
-1,
GETDATE())
AND DATEPART(year, transactionDate)=DATEPART(year,
DATEADD(month, -1,
GETDATE())
AND MemberID IS IN (SELECT TOP 100 CustomerID,
FROM Transactions
WHERE DATEPART(month,
transactionDate)=DATEPART(month,
DATEADD(month, -1, GETDATE()))
AND DATEPART(year,
transactionDate)=DATEPART(year, DATEADD(month, -1,
GETDATE()))
AND ClubID = t1.ClubID
GROUP BY CustomerID
ORDER BY SUM(transactionValue) DESC)
GROUP BY ClubID, CustomerID
ORDER BY ClubID, totalValue DESC
----------------------
Tim Rox
Newgency Pty Ltd
2a Broughton St
Paddington 2021
Sydney, Australia
Ph (02) 9331 2133
Fax (02) 9331 5199
Mobile: 0411 512 454
http://www.newgency.com/
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
[EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
