Query question

From: Gregg (anonymous_at_discussions.microsoft.com)
Date: 06/09/04 

Date: Wed, 9 Jun 2004 15:38:58 -0700

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

Relevant Pages

  • Re: "Identical" query SQL Throws Error
    ... in the second query I was trying to set up a filter on the MYear ... The criteria on the Factory field ... FROM (tblEndItem INNER JOIN tblSPIData ON ... ...
    (microsoft.public.access.modulesdaovba)
  • Re: Duplicate Label_id History
    ... First query: Return just the scans you are interested in ... INNER JOIN (ScannAction ... FROM AccountAction INNER JOIN (ScannAction INNER JOIN (Employees RIGHT ...
    (microsoft.public.access.queries)
  • Re: IIF Statement to calculate aging buckets
    ... If Access says it's too complex, Access can't understand it -- incorrect may ... Now create a second query, ... all the fields from the first query, then add your original IIFstatement. ...
    (microsoft.public.access.queries)
  • Re: The Microsoft Jet database engine does not recognize...
    ... In the first query, ... Choose Parameters on the Query menu. ... Access opens a dialog. ... (tblGeoLoc INNER JOIN (tblLocPicker INNER ...
    (microsoft.public.access.queries)
  • Re: how do i count consecutive records with a field value >=1
    ... The first query consider for each record how many records in table ... The second query keep only the ranks where bal <0. ... but i don't have a clue how your sql got the results i needed. ...
    (microsoft.public.access.queries)