|
Home > Archive > microsoft.public.sqlserver.server > October 2002 > Anyone got any tips for this Query?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Anyone got any tips for this Query?
|
|
| Nabbatron 2002-10-05, 9:07 pm |
| 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_RibbonStati
stics 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_RibbonStati
stics WHERE dDaTim =
sfl.DateTime) as ProductID
FROM APP_FaultDensities_ScannerFaul
tLogs 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_RibbonStati
stics WHERE dDaTim = sfl.DateTime) as
ProductID
FROM
APP_FaultDensities_ScannerFaul
tLogs 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_RibbonStati
stics 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
| |
| Roy Harvey 2002-10-05, 9:07 pm |
| Andy,
I may very well not have this straight, but I took your last query and
worked on it a bit.
The first thing I might suggest is to create a view so that you can
write that complicated calculation just once and be done with it.
Note that we do NOT have the date test in here.
Create View RS_View
AS
SELECT *,
((ISNULL(dSpeed, 0) * 0.06) *20
* (ABS(ISNULL(dTrack_Right, 0)
- ISNULL(dTrack_Left, 0) ) / 10000)) as AreaOver20Minutes
FROM APP_FaultDensities_RibbonStati
stics
Then I think we can rewrite your last SELECT as a join, with the date
tests in the WHERE clause, and eliminate the subquery in the SELECT
list at the same time:
SELECT sfl.DateTime,
SUM(sfl.[Number Of Faults] / rs1.AreaOver20Minutes)
as TotalNumberOfFaults,
rs1.ProductID
FROM APP_FaultDensities_ScannerFaul
tLogs sfl
JOIN RS_View AS rs1
ON sfl.DateTime = rs1.dDaTim
WHERE sfl.DateTime BETWEEN '2002-09-10 07:20:00'
AND '2002-09-11 07:20:00'
AND rs.dDaTim 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
Roy
| |
| Nabbatron 2002-10-05, 9:07 pm |
| Roy,
agreed that that looks a wee bit more efficient... will give it a try in the
morn.
Many thanks,
AM
"Roy Harvey" <RoyHarv@compuserve.com> wrote in message
news:2g7vnusqo513qvnlbn5nv6qku
r7m1lfuv6@4ax.com...
> Andy,
>
> I may very well not have this straight, but I took your last query and
> worked on it a bit.
>
> The first thing I might suggest is to create a view so that you can
> write that complicated calculation just once and be done with it.
> Note that we do NOT have the date test in here.
>
> Create View RS_View
> AS
> SELECT *,
> ((ISNULL(dSpeed, 0) * 0.06) *20
> * (ABS(ISNULL(dTrack_Right, 0)
> - ISNULL(dTrack_Left, 0) ) / 10000)) as AreaOver20Minutes
> FROM APP_FaultDensities_RibbonStati
stics
>
> Then I think we can rewrite your last SELECT as a join, with the date
> tests in the WHERE clause, and eliminate the subquery in the SELECT
> list at the same time:
>
> SELECT sfl.DateTime,
> SUM(sfl.[Number Of Faults] / rs1.AreaOver20Minutes)
> as TotalNumberOfFaults,
> rs1.ProductID
> FROM APP_FaultDensities_ScannerFaul
tLogs sfl
> JOIN RS_View AS rs1
> ON sfl.DateTime = rs1.dDaTim
> WHERE sfl.DateTime BETWEEN '2002-09-10 07:20:00'
> AND '2002-09-11 07:20:00'
> AND rs.dDaTim 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
>
> Roy
|
|
|
|
|