I'm more of an oracle man - bt for sql server - http://www.sql-server-performance.com/ http://www.sqlteam.com/item.asp?ItemID=1015
Enjoy :o) Mark > Tim Rox <[EMAIL PROTECTED]> wrote: > > 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/ ----------------------------------- [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/
