|
| ------ =_NextPart_000_0019_
01C5DC2A.01DD88C0
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
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,produ
ct) =
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.=20
If I rephrased that correctly, here is how I would build your query. =
Step 1 is to perform the (date,customer,produ
ct) 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.=20
Note: Date, time, and datetime literals are represented by single-quoted =
strings. You do not need the DATE() function to create a date literal.=20
CREATE TEMPORARY TABLE tmpTotals (=20
key(CustNo)=20
, key(ProdNo)=20
)=20
SELECT PostingDate=20
, CustNo=20
, ProdNo=20
, sum(Cost) as costs=20
, sum(Sales) as sales=20
FROM salesmaster=20
WHERE PostingDate <=3D '2005-09-01 00:00:00'=20
GROUP BY PostingDate, CustNo, ProdNo;=20
Step 2: collect the rest of the information for the report.=20
SELECT CustNo=20
, c.Name as custname=20
, ProdNo=20
, p.Name as prodname=20
, costs=20
, sales=20
, PostingDate=20
FROM tmpTotals tt=20
LEFT JOIN customerintermediate
c=20
ON c.CustNo =3D tt.CustNo=20
LEFT JOIN productintermediate p=20
ON p.ProdNo =3D tt.ProdNo=20
ORDER BY ... your choice... ;=20
Step 3: The database is not your momma. Always clean up after yourself.=20
DROP TEMPORARY TABLE tmpTotals;=20
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.=20
Does this help you to organize your thoughts?=20
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
------ =_NextPart_000_0019_
01C5DC2A.01DD88C0--
|
|