Re: slow insert into select statement

nyem Thu, 27 May 2004 02:36:20 -0700

Thanks for all the feedback.  Here's my latest attempt:

SELECT @start:=NOW();
SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH);
SELECT @date2:=CURDATE();

LOCK TABLES  rptPricingTEST READ, rptPricingTEST a READ;

CREATE TEMPORARY TABLE tmpLatestDates
   SELECT
     commodity,
     MAX(PricingDt) as MaxDate
   FROM rptPricingTEST
   WHERE   PricingDt > @date1
     AND   PricingDt <= @date2
   GROUP BY commodity;

CREATE TEMPORARY TABLE tmpLatestPrices
   SELECT
     b.commodity,
     a.PricingDt,
     a.PricingHighPrice,
     a.PricingLowPrice,
     a.PricingAvgPrice
   FROM rptPricingTEST a
   INNER JOIN tmpLatestDates b
   ON b.commodity = a.commodity
       AND b.MaxDate = a.PricingDt;

CREATE TEMPORARY TABLE tmpPrevDates
   SELECT
     a.commodity,
     MAX(a.PricingDt) as PrevDate
   FROM rptPricingTEST a, tmpLatestPrices b
   WHERE   a.PricingDt < @date1
     AND a.commodity = b.commodity
   GROUP BY commodity;

CREATE TEMPORARY TABLE tmpPrevPrices
   SELECT
     a.commodity,
     a.PricingDt,
     a.PricingHighPrice,
     a.PricingLowPrice,
     a.PricingAvgPrice
   FROM rptPricingTEST a
   INNER JOIN tmpPrevDates b
   ON b.commodity = a.commodity
       AND b.PrevDate = a.PricingDt;

SELECT
   a.commodity,
   a.PricingDt                as PrevDate,
   a.PricingAvgPrice          as PrevAvg,
   b.PricingDt                as LatestDate,
   b.PricingAvgPrice          as LatestAvg,
   ((b.PricingAvgPrice - a.PricingAvgPrice)
   /a.PricingAvgPrice) * 100  as priceChange
FROM tmpPrevPrices a, tmpLatestPrices b
WHERE
   a.commodity = b.commodity
   AND a.PricingAvgPrice < b.PricingAvgPrice;

UNLOCK TABLES;

DROP TABLE IF EXISTS
   tmpLatestDates,
   tmpPrevDates,
   tmpLatestPrices,
   tmpPrevPrices;

SELECT TIMEDIFF(NOW(),@start);
+------------------------+
| TIMEDIFF(NOW(),@start) |
+------------------------+
| 00:00:08               |
+------------------------+
1 row in set (0.00 sec)


Now I get it all executed in 8 seconds.

[EMAIL PROTECTED] wrote:

.. snip


After reviewing your original post, I am not confident that you are
answering your question with this query. You said "I need to generate a
report of price change for all commodity for the date interval selected by
users." which to me implies a report something like:

+---------+-------------+-----------+--------------+------------+---------+---------+---------+
|commodity|starting date|ending date|starting price|ending price|max
price|min price|avg price|
+---------+-------------+-----------+--------------+------------+---------+---------+---------+

I am just not sure where you are going with the query you are building. Can
you provide a template of the results you want?

Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine


You were right about this. I was merely posting the first part of my
query, as that's where my trouble began. Here's the result table that
I'm looking for, where priceChange is the % increase in price for the
two date interval.
+-----------+----------+---------+------------+-----------+-------------+
| commodity | PrevDate | PrevAvg | LatestDate | LatestAvg | priceChange
+----------------------+---------+------------+-----------+-------------+

The slowest part of the script is when generating this table (5.00 sec).
   How could I optimise it further? I don't mind creating more temp
tables as long as the total execution time is much reduced.

I'm using mysql 4.1.1a-alpha on win2k.

Thanks,
nyem


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: slow insert into select statement

Reply via email to