Query question

  • Thread starter Thread starter Gregg
  • Start date Start date
G

Gregg

My question is why does the first query below take about
5 seconds to run, where the second query takes about 5
minutes to run? The only difference is the first has the
criteria enter in the query and the second is using the
table shown below.

SELECT dbo_sh_sales.store, dbo_sh_sales.upc,
dbo_sh_sales.pricetype, dbo_sh_sales.quantity,
dbo_sh_time.ActualDate
FROM dbo_sh_time INNER JOIN dbo_sh_sales ON
dbo_sh_time.DateKey = dbo_sh_sales.date
WHERE (((dbo_sh_sales.store)=4) AND ((dbo_sh_sales.upc)
=1714) AND ((dbo_sh_sales.pricetype)=8 Or
(dbo_sh_sales.pricetype)=9 Or (dbo_sh_sales.pricetype)=10
Or (dbo_sh_sales.pricetype)=11) AND
((dbo_sh_time.ActualDate)<Date()));




SELECT dbo_sh_sales.store, dbo_sh_sales.upc,
dbo_sh_sales.pricetype, dbo_sh_sales.quantity,
dbo_sh_time.ActualDate
FROM DistinctStoreUPC, dbo_sh_time INNER JOIN
dbo_sh_sales ON dbo_sh_time.DateKey = dbo_sh_sales.date
WHERE (((dbo_sh_sales.store)=[DistinctStoreUPC]![STORE])
AND ((dbo_sh_sales.upc)=[DistinctStoreUPC]![UPC]) AND
((dbo_sh_sales.pricetype)=8 Or (dbo_sh_sales.pricetype)=9
Or (dbo_sh_sales.pricetype)=10 Or (dbo_sh_sales.pricetype)
=11) AND ((dbo_sh_time.ActualDate)<Date()));




DistinctStoreUPC
STORE UPC
4 1714
 
They look like SQLServer syntax to me, perhaps from
SQLServer Back-End.

If this is the case, you may like to check the Query
(execution) Plan in SQLServer Query Analyser and see which
section in the 2nd Query takes most time and see if you
can re-structure the SQL.

It is likely that in the first Query, indices are used
while in the 2nd Query, indices are not being used and the
hash Tables (?) are needed.

HTH
Van T. Dinh
MVP (Access)
 
Lets See-
Query one has two tables and they are joined.

Query two has three tables and one of the tables is NOT joined. In this case, I
believe that for every combination of every record in DistinctStoreUPC with
every row returned by the two joined tables. After that set is built, then the
WHERE clause will be applied to the record set.

That is all a guess on my part, but I would think that has a big probability of
being true.
 
I thought that might be the problem too but,it does the
same thing whether joined or not, because for testing
purposes, the unjoined table only has one record in it.
-----Original Message-----
Lets See-
Query one has two tables and they are joined.

Query two has three tables and one of the tables is NOT joined. In this case, I
believe that for every combination of every record in DistinctStoreUPC with
every row returned by the two joined tables. After that set is built, then the
WHERE clause will be applied to the record set.

That is all a guess on my part, but I would think that has a big probability of
being true.
My question is why does the first query below take about
5 seconds to run, where the second query takes about 5
minutes to run? The only difference is the first has the
criteria enter in the query and the second is using the
table shown below.

SELECT dbo_sh_sales.store, dbo_sh_sales.upc,
dbo_sh_sales.pricetype, dbo_sh_sales.quantity,
dbo_sh_time.ActualDate
FROM dbo_sh_time INNER JOIN dbo_sh_sales ON
dbo_sh_time.DateKey = dbo_sh_sales.date
WHERE (((dbo_sh_sales.store)=4) AND ((dbo_sh_sales.upc)
=1714) AND ((dbo_sh_sales.pricetype)=8 Or
(dbo_sh_sales.pricetype)=9 Or (dbo_sh_sales.pricetype) =10
Or (dbo_sh_sales.pricetype)=11) AND
((dbo_sh_time.ActualDate)<Date()));

SELECT dbo_sh_sales.store, dbo_sh_sales.upc,
dbo_sh_sales.pricetype, dbo_sh_sales.quantity,
dbo_sh_time.ActualDate
FROM DistinctStoreUPC, dbo_sh_time INNER JOIN
dbo_sh_sales ON dbo_sh_time.DateKey = dbo_sh_sales.date
WHERE (((dbo_sh_sales.store)=[DistinctStoreUPC]! [STORE])
AND ((dbo_sh_sales.upc)=[DistinctStoreUPC]![UPC]) AND
((dbo_sh_sales.pricetype)=8 Or (dbo_sh_sales.pricetype) =9
Or (dbo_sh_sales.pricetype)=10 Or (dbo_sh_sales.pricetype)
=11) AND ((dbo_sh_time.ActualDate)<Date()));

DistinctStoreUPC
STORE UPC
4 1714
Click to expand...
.
Click to expand...
 
I ran the 2nd query through the Query Analyzer and it
said that the query didn't have any tunable tables?
Any more ideas? I've set up indexes on all the tables,
I've tried joining the 3rd table and I've tried
restructuring the query with different joins. Whatever I
do it still runs the same amount of time.
-----Original Message-----
They look like SQLServer syntax to me, perhaps from
SQLServer Back-End.

If this is the case, you may like to check the Query
(execution) Plan in SQLServer Query Analyser and see which
section in the 2nd Query takes most time and see if you
can re-structure the SQL.

It is likely that in the first Query, indices are used
while in the 2nd Query, indices are not being used and the
hash Tables (?) are needed.

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
My question is why does the first query below take about
5 seconds to run, where the second query takes about 5
minutes to run? The only difference is the first has the
criteria enter in the query and the second is using the
table shown below.

SELECT dbo_sh_sales.store, dbo_sh_sales.upc,
dbo_sh_sales.pricetype, dbo_sh_sales.quantity,
dbo_sh_time.ActualDate
FROM dbo_sh_time INNER JOIN dbo_sh_sales ON
dbo_sh_time.DateKey = dbo_sh_sales.date
WHERE (((dbo_sh_sales.store)=4) AND ((dbo_sh_sales.upc)
=1714) AND ((dbo_sh_sales.pricetype)=8 Or
(dbo_sh_sales.pricetype)=9 Or (dbo_sh_sales.pricetype) =10
Or (dbo_sh_sales.pricetype)=11) AND
((dbo_sh_time.ActualDate)<Date()));




SELECT dbo_sh_sales.store, dbo_sh_sales.upc,
dbo_sh_sales.pricetype, dbo_sh_sales.quantity,
dbo_sh_time.ActualDate
FROM DistinctStoreUPC, dbo_sh_time INNER JOIN
dbo_sh_sales ON dbo_sh_time.DateKey = dbo_sh_sales.date
WHERE (((dbo_sh_sales.store)=[DistinctStoreUPC]! [STORE])
AND ((dbo_sh_sales.upc)=[DistinctStoreUPC]![UPC]) AND
((dbo_sh_sales.pricetype)=8 Or (dbo_sh_sales.pricetype) =9
Or (dbo_sh_sales.pricetype)=10 Or (dbo_sh_sales.pricetype)
=11) AND ((dbo_sh_time.ActualDate)<Date()));




DistinctStoreUPC
STORE UPC
4 1714

.
Click to expand...
.
Click to expand...
 
No. I meant the SQL Server's Query Analyser, not the Access Analyser.

I agreed with John's assessment, especially if you run it as a JET Query
since one of the Tables in not joined (Cartersian product) and it is likely
that lot more data got to go down the wires so that JET can select the
suitable rows.
 
I did use SQL Server's Query Analyzer, that's Analyzer
with a "z", and got the no tunable tables. On the comment
of John's, I replied to it and said, it make s no
difference if the table is joined or not, the runtime is
the same. Thanks for the help so far. Is there anything
else you can suggest?
 
In the SQL Server Analyzer, check the step that uses the highest percentage
of processing time. The problem will be around there. It likely to be the
hash Table (tunable???).

OTOH, it is much better to use Pass-Through Query since in PTQ, the
processing is done by the SQL Server and only data from the selected rows is
send through the network to the PC.
 
Nothing I try seems to help the runtime. I think I'm
going to shoot the server. Thanks for the help.
Gregg
 
Back
Top