Anyone got any tips for this Query?

[Nabbatron]

Hi,

I have two tables with the following properties:

"K" denotes primary key

TABLE 1: "rs"

K: Datetime column. We'll call this "dDaTim"
Various other columns containing some statistical data, such as "dSpeed",
"dTrack_Right" and "dTrack_Left"

TABLE 2: "sfl"

K: datetime column. We'll call this "DateTime"
K: Module #
K: Class #
K: Type #
Number Of Faults - this is a count of the number of "faults" at time a, with
module m, class c, type t - combining to make a unique record

SCENARIO

*Usually* there is ONE rs.dDaTim record matching MANY sfl.DateTime records,
with new entries every 20 minutes (e.g. 00:00, 00:20 and so on)

In cases where these times match, I want to do some maths on the sfl.Number
Of Faults field, using some of the values in the "rs" table. I then group
these together by datetime, which gives me a nice summary of the results per
each unique 20 minute timestamp.

PROBLEM

At the moment, I have two queries - both looking at the individual tables -
but when I come to combine them, and say "where rs.DaTim = sfl.DateTime AND
rs.dDaTim BETWEEN timeX AND timeY"... well, the query slows up shockingly
(!), and I'm wondering what is the best way to maximise it?

Ok... here are the two queries:

QUERY 1: gets "rs" data over specified time range, and does some maths

SELECT rs.dDaTim, ((ISNULL(dSpeed, 0) * 0.06) *20 * (ABS(
ISNULL(dTrack_Right, 0) - ISNULL(dTrack_Left, 0) ) / 10000)) as
AreaOver20Minutes
FROM APP_FaultDensities_RibbonStatistics rs
WHERE (rs.dDaTim BETWEEN '2002-09-10 07:20:00' AND '2002-09-11 07:20:00')

QUERY 2: gets "sfl" data over same time range

SELECT sfl.DateTime, SUM(sfl.[Number Of Faults]) as TotalNumberOfFaults,
(SELECT ProductID FROM APP_FaultDensities_RibbonStatistics WHERE dDaTim =
sfl.DateTime) as ProductID
FROM APP_FaultDensities_ScannerFaultLogs sfl
WHERE (sfl.DateTime BETWEEN '2002-09-10 07:20:00' AND '2002-09-11
07:20:00')
AND (sfl.Class >= 0 AND sfl.CLASS <= 31)
Group by DateTime
ORDER BY DateTime ASC

Now, I also want to be able to divide each sfl.TotalNumberOfFaults entry by
rs.AreaOver20Minutes for matching time records... so at the moment my
"combined" query is:

SELECT sfl.DateTime, SUM(sfl.[Number Of Faults] / rs1.AreaOver20Minutes) as
TotalNumberOfFaults, (SELECT ProductID FROM
APP_FaultDensities_RibbonStatistics WHERE dDaTim = sfl.DateTime) as
ProductID
FROM
APP_FaultDensities_ScannerFaultLogs sfl,
(SELECT rs.dDaTim, ((ISNULL(dSpeed, 0) * 0.06) *20 * (ABS(
ISNULL(dTrack_Right, 0) - ISNULL(dTrack_Left, 0) ) / 10000)) as
AreaOver20Minutes FROM APP_FaultDensities_RibbonStatistics rs WHERE
(rs.dDaTim BETWEEN '2002-09-10 07:20:00' AND '2002-09-11 07:20:00')) as rs1
WHERE (sfl.DateTime BETWEEN '2002-09-10 07:20:00' AND '2002-09-11
07:20:00')
AND sfl.DateTime = rs1.dDaTim
AND (sfl.Class >= 0 AND sfl.CLASS <= 31)
Group by DateTime
ORDER BY DateTime ASC

...basically I have used a derived query to get the "rs" data.

However, the average execution time for this is around 5 seconds, whereas
both stand-alone queries run at < 1 second.

I'm not good with JOIN techniques... so I was wondering if anyone had any
ideas?!!!

Thanks for your time!

Andy

[derek.beacon]

Not sure what rdbms your using but I think about using temporary tables to
build the rs 1 table first then use that in the join rather than trying to
do it all in one sql statement.

"Nabbatron" <nabREM...@PARTPLEASEnabnet.freeserve.co.uk> wrote in
message news:3d7f83ea$0$8509$cc9e...@news.dial.pipex.com...