Expressions in criteria and indexes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've been teaching myself access for the past few years and I have a question
which relates to indexes.

I use access to pull data out of another application for ad-hoc reporting.
There is a table which has the fields whshipline.company, whshipline.location
and whshipline.shipment_nbr in a multifield index in that order.

For a simplified example, when I write this query (qryTest):

SELECT LAWSON_WHSHIPLINE.COMPANY, LAWSON_WHSHIPLINE.LOCATION,
LAWSON_WHSHIPLINE.SHIPMENT_NBR, LAWSON_WHSHIPLINE.ITEM,
LAWSON_WHSHIPLINE.DESCRIPTION, LAWSON_WHSHIPLINE.UPDATE_DATE
FROM LAWSON_WHSHIPLINE
WHERE (((LAWSON_WHSHIPLINE.COMPANY)=1) AND
((LAWSON_WHSHIPLINE.LOCATION)="CHOA") AND
((LAWSON_WHSHIPLINE.SHIPMENT_NBR)>=173606));

It returns results in about half a second. However, if I try to write an
expression which derives one of the criteria values (shipment_nbr) the query
takes about 10 minutes to run. I've written a passthrough query (qryMINSHIP)
which derives the first shipment number 90 days ago:

select min(lawson.whshipment.shipment_nbr) as MinShip,
min(lawson.whshipment.ship_date) as MinDate
from lawson.whshipment
where lawson.whshipment.ship_date > (sysdate - 90)

This returns results in about 10 seconds. I figured that if I could derive
the criteria value from this passthrough query, my original query qryTest
would return results in about 10.5 seconds:

SELECT LAWSON_WHSHIPLINE.COMPANY, LAWSON_WHSHIPLINE.LOCATION,
LAWSON_WHSHIPLINE.SHIPMENT_NBR, LAWSON_WHSHIPLINE.ITEM,
LAWSON_WHSHIPLINE.DESCRIPTION, LAWSON_WHSHIPLINE.UPDATE_DATE
FROM LAWSON_WHSHIPLINE
WHERE (((LAWSON_WHSHIPLINE.COMPANY)=1) AND
((LAWSON_WHSHIPLINE.LOCATION)="CHOA") AND
((LAWSON_WHSHIPLINE.SHIPMENT_NBR)>=DFirst("MINSHIP","qryMINSHIP")));

I tested the dfirst expression in the immediate window and it returns the
result I want super quickly.

So in my mind I think that qryTest should work just as fast with the 173606
entered manually as it does when the value is derived from an expression, but
that seems to not be the case. Can anyone explain why this is or how I can
speed up this query?
 
that's fairly simple index behaviour.
An index is (and can) only be used if at least the first index key is a part
of the query
ie putting a field in an index does'nt mean *use this index whenever I query
a value for any of the index keys*

hope this explains your problem

Pieter (Oracle DBA Professional)
 
I don't get it. When I key in the values for the same field, it works. But
if I write an expression that derives the value it doesn't.

To make it really simple it works if I just type in

4

but if I type in

2 + 2

it doesn't.
 
I Might be missing something here but should'nt this do?

SELECT LAWSON_WHSHIPLINE.COMPANY, LAWSON_WHSHIPLINE.LOCATION,
LAWSON_WHSHIPLINE.SHIPMENT_NBR, LAWSON_WHSHIPLINE.ITEM,
LAWSON_WHSHIPLINE.DESCRIPTION, LAWSON_WHSHIPLINE.UPDATE_DATE
FROM LAWSON_WHSHIPLINE
WHERE LAWSON_WHSHIPLINE.COMPANY=1
AND LAWSON_WHSHIPLINE.LOCATION="CHOA"
AND LAWSON_WHSHIPLINE.SHIPMENT_DATE > Date() -90

Pieter
 
Date isn't part of the index, shipment number is. I'm pulling the shipment
number from a header record table to try to derive the shipment number.
 
If The Index is
Company, Location, Shipment_nbr or Location, Company, Shipment_nbr it
doesn't matter much.
Only if Shipment_nbr is the first or seccond "key" in the index.
DFirst is like the rest of the domain aggregate function ultra slow.
Your minimum date query *does* query the date with no index usage, so using
parts of the index is bound to be faster.

HtH

Pieter
 
Back
Top