SELECT with subquery incredibly slow..

[thatguyNYC]

Hey there--

I have a form with two listboxes, each populated by SQL statements that pull from separate tables, like this:

lstAvail.RowSource = SELECT Person_Id, LastName, FirstName FROM HREmps WHERE Person_Id NOT IN (SELECT Person_Id FROM Employees) ORDER BY LastName, FirstName;
lstSel.RowSource = SELECT Person_Id, cLName, cFName FROM Employees ORDER BY cLName, cFName, Person_Id;

This shows all employees in the Employees table (in lstSel) and only those employees in the HREmps table that are not already in Employees (in lstAvail).

The problem is that opening the form takes around 2mins, as does requerying the recordset when a record is copied from HREmps to Employees. I'm assuming this is because Access, in its infinite wisdom, runs the subquery for every record in the HREmps table (about 1900) to compare Person_Id.

I'm wondering if anyone has some thoughts about how to speed this up? Is something wrong with my SQL? Could there be an easier way to do this?

Thanks

[FishVal]

Are the tables [HREmps] and [Employees] in one-to-one relation by [Person_ID]? Or in one-to-many?

[JConsulting]

Hey there--

I have a form with two listboxes, each populated by SQL statements that pull from separate tables, like this:

lstAvail.RowSource = SELECT Person_Id, LastName, FirstName FROM HREmps WHERE Person_Id NOT IN (SELECT Person_Id FROM Employees) ORDER BY LastName, FirstName;
lstSel.RowSource = SELECT Person_Id, cLName, cFName FROM Employees ORDER BY cLName, cFName, Person_Id;

This shows all employees in the Employees table (in lstSel) and only those employees in the HREmps table that are not already in Employees (in lstAvail).

The problem is that opening the form takes around 2mins, as does requerying the recordset when a record is copied from HREmps to Employees. I'm assuming this is because Access, in its infinite wisdom, runs the subquery for every record in the HREmps table (about 1900) to compare Person_Id.

I'm wondering if anyone has some thoughts about how to speed this up? Is something wrong with my SQL? Could there be an easier way to do this?

Thanks

you can try this option. Its an unmatched query

 
SELECT Person_Id, LastName, FirstName
FROM HREmps LEFT JOIN Employees ON [HREmps ].[Person_Id] = [Employees].[Person_Id]
WHERE ([HREmps ].[Person_Id] Is Null);
 
 

[thatguyNYC]

you can try this option. Its an unmatched query

 
SELECT Person_Id, LastName, FirstName
FROM HREmps LEFT JOIN Employees ON [HREmps ].[Person_Id] = [Employees].[Person_Id]
WHERE ([HREmps ].[Person_Id] Is Null);
 
 

There was a typo in the WHERE clause; it should be

WHERE ([Employees].[Person_Id] Is Null);

But that works perfectly! Took the load/requery time from 2mins to 2seconds.

Thanks!

[JConsulting]

There was a typo in the WHERE clause; it should be

WHERE ([Employees].[Person_Id] Is Null);

But that works perfectly! Took the load/requery time from 2mins to 2seconds.

Thanks!

Excellent...I modeled mine then tried pasting in your field names...guess I missed that one :)
Happy to help.
J