Hi Doyce
Are you able to modify the table and add indexes to those two fields? VENID_55 should definitely be an index because it is presumably the foreign key in a relationship with your Vendors table.
The one that will certainly speed up this query though is an index on TNXDTE_55. You are using this in an ORDER BY clause in your subquery, so it will be very slow if it's not indexed.
The composite index on VENID_55 and TNXDTE_55 is presumably a unique constraint to prevent two orders being placed with the same vendor on the same day (an odd restriction!) It will not help here, because the primary ordering is by VENID_55, then by TNXDTE_55.
I'm just off to the beach for my daily swim now (yes, daily – even in winter J). The sun has just risen and it's about 17°C outside. Sea temperature around 21°C.
Sorry – couldn't resist rubbing it in J
Cheers,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of winberry.doyce@roadsysinc.com
Sent: Wednesday, 5 March 2014 07:19
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] RE: Last Receipt with Vendor
Graham,
If I look at this linked SQL table in design view, it does not show an index on VENID_55. However it does have a key for VENID_55 and TNXDTE_55. Can I use that to speed up this query. If so how? Right now this query is too slow to be useful. This table has over 77,000 records dating back to 2000. I'm going to look into purging some of these records. Perhaps I can eliminate the non-inventory receipts and limit the date range.
BTW, while you were out swimming, we got an ice storm over the weekend with sleet and freezing rain.
Doyce
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (8) |
Tidak ada komentar:
Posting Komentar