Hi Shawn: Just wanted to publicly thank you for the time you took to help me. I think that it is important that guys like yourself who take time out from your busy work be appreciated when their solution made a huge difference.
I was developing an ASP application along with Crystal reports for a customer which was reporting from million of rows of data and my original approach resulted in very poor performance .. reports being VERY sluggish (45 mins to run some). I followed your concept and the same report that took 45 mins to run took 40 seconds!!!! Unfortunately I could not implement it using MySql because the current version of MySql ODBC (3.51) did not expose the stored procedures to CR and I could not find an OleDb provider that will work for MySql. However, I ended up using Sql Server but I followed you suggestion and gained tremendous performance improvements. Keep up the good work. Best regards Imran ---------------------------------------- Solution ------------------------------- Let me see if I can translate what you want in a query into regular language. I think you would like to see, grouped by date, customer, and product, the total cost and total sales for each (date,customer,product) triple along with each product's description ,code, and the customer's number and name. All of that will be limited to activity on or before midnight of a certain date. If I rephrased that correctly, here is how I would build your query. Step 1 is to perform the (date,customer,product) summations. By minimizing the number of rows, columns, and/or tables we need to summarize against, we improve performance. So I do this part of the analysis before I join in the other tables. Note: Date, time, and datetime literals are represented by single-quoted strings. You do not need the DATE() function to create a date literal. CREATE TEMPORARY TABLE tmpTotals ( key(CustNo) , key(ProdNo) ) SELECT PostingDate , CustNo , ProdNo , sum(Cost) as costs , sum(Sales) as sales FROM salesmaster WHERE PostingDate <= '2005-09-01 00:00:00' GROUP BY PostingDate, CustNo, ProdNo; Step 2: collect the rest of the information for the report. SELECT CustNo , c.Name as custname , ProdNo , p.Name as prodname , costs , sales , PostingDate FROM tmpTotals tt LEFT JOIN customerintermediate c ON c.CustNo = tt.CustNo LEFT JOIN productintermediate p ON p.ProdNo = tt.ProdNo ORDER BY ... your choice... ; Step 3: The database is not your momma. Always clean up after yourself. DROP TEMPORARY TABLE tmpTotals; And you are done! The only trick to doing a sequence of statements in a row (like this) is that they all have to go through the same connection. As long as you do not close and re-open the connection between statements, any temp tables or @-variables you create or define remain in existence for the life of the connection. Depending on your connection library, you might be able to execute all three statements from a single request. Most likely, you will need to send them in one-at-a-time. Does this help you to organize your thoughts? Shawn Green Database Administrator Unimin Corporation - Spruce Pine