Query question
From: Van T. Dinh (VanThien.Dinh_at_discussions.microsoft.com)
Date: 06/10/04
- Next message: Van T. Dinh: "Week Number Query"
- Previous message: Van T. Dinh: "UNION Query truncating Memo fields"
- In reply to: Gregg: "Query question"
- Next in thread: Gregg: "Query question"
- Reply: Gregg: "Query question"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 9 Jun 2004 20:55:44 -0700
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
>
>.
>
- Next message: Van T. Dinh: "Week Number Query"
- Previous message: Van T. Dinh: "UNION Query truncating Memo fields"
- In reply to: Gregg: "Query question"
- Next in thread: Gregg: "Query question"
- Reply: Gregg: "Query question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
