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
