We help IT Professionals succeed at work.

Basic question on speed of lookup/indexing

terpsichore
terpsichore used Ask the Experts™
on
Dear experts -
I have an expense table, with 2 fields Vendor_ID and deadline (date).

When saving record, I need to look against a vendor availability table (fields: Vendor_ID, startdate, enddate).

I want to see if this vendor's deadline falls within the start and end date in ANY related record.

Two questions:
1) What type of indexes do I need on the related table?
2) What is the fastest way to check - do i use a dcount function, or is something else faster?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<1) What type of indexes do I need on the related table?>>

 I would have one on each of the three fields individually.

<<2) What is the fastest way to check - do i use a dcount function, or is something else faster?>>

 Count() or DCount() will be the fastest.

 Attached is a small sample DB with how to do overlap checking in a query (it was for camp sites for a campground, but it's the same concept).

Jim
res.zip
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
You might also want to read the article on Fast Table Lookup Functions

This is significantly more advanced than using DLOOKUP, but I've started to implement a number of custom lookup functions to replace my use of the DLOOKUP().  If you are only doing the occassional DLOOKUP() it might not be worth the effort, but if you are doing a lot of lookups, they [fast lookups] can be significantly faster, and you can really see the difference in speed.

Author

Commented:
many thanks
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Seek is hands down the fastest way to find something, but it is not applicable in this case because your not looking for single value, but rather a set of records that may overlap your start/end dates.

Because it's a set of records that you need to check for, a SQL statment is required and then returning a field or a count to indicate if you found any or not.   Count() or DCount() should be faster though, as Count() is internally optimized.

Jim.