terpsichore
asked on
Basic question on speed of lookup/indexing
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
many thanks
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.
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.
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.