Am using SQL server. I've rejigged things now so that i don't have to do that horrid query, but i'm still running into a few problems with speed and other queries.
I don't know much at all about stored procedures, temporary tables or views.. but i think that they might be useful. My main question with creating a view or something similar is: when is the view re-calculated? every time you use it? i guess that can be changed. Know any good places to get started with those concepts? thanks heaps, tim -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Mark M Sent: Friday, 25 July 2003 3:59 PM To: CFAussie Mailing List Subject: [cfaussie] RE: OT - SQL Mindbender Tim - What DB you using? You could also look at using stored procedures, temporary tables and/or views to help you along and get your data coming out faster. Also have a good look at what you are indexing in your tables. Maybe indexing things like 'year' may make things a bit faster. HTH Mark > Adam Chapman <[EMAIL PROTECTED]> wrote: > > 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/ ----------------------------------- [EMAIL PROTECTED] ICQ: 3094740 Safe From Bees [www.safefrombees.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/
