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
