0

I have three tables in Access:

employees
----------------------------------
id (pk),name

times
----------------------
id (pk),employee_id,event_time

time_notes
----------------------
id (pk),time_id,note

I want to get the record for each employee record from the times table with an event_time immediately prior to some time. Doing that is simple enough with this:

    select employees.id, employees.name, 
(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC) as time_id 
from employees

However, I also want to get some indication of whether there's a matching record in the time_notes table:

select employees.id, employees.name, 
(select top 1 time_notes.id from time_notes where time_notes.time_id=(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC)) as time_note_present,
(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC) as last_time_id 
from employees

This does work but it's SOOOOO SLOW. We're talking 10 seconds or more if there's 100 records in the employee table. The problem is peculiar to Access as I can't use the last_time_id result of the other sub-query like I can in MySQL or SQL Server.

I am looking for tips on how to speed this up. Either a different query, indexes. Something.

  • Are these native tables or linked (e.g. ODBC) tables? – C Perkins Jan 31 '18 at 5:57
1

you can use link to create index on MS-Access.

Hope this query work ok in MS-Access ,

select employees.id, employees.name, last_time_id
,(select top 1 time_notes.id from time_notes where time_notes.time_id=last_time_id)
time_note_present
from
(
select employees.id, employees.name, 
(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC) 
as last_time_id 
from employees
)t4

Thanks @Perkins.I have edited my answer.

| improve this answer | |
  • Adding an index on the event_time column may also help. Since the Access (i.e. Jet / Ace) engine does not do full optimization of the execution plan, it will likely re-execute every subquery for every row, each time re-ordering the results, then selecting the top value. It is not smart enough to change this to an equivalent join, nor does it automatically create temporary tables to avoid redundant execution (like other DB engines might). You need to explicitly restructure the query to avoid these pitfalls. – C Perkins Jan 31 '18 at 6:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.