By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

SELECT with subquery incredibly slow..

P: 20
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
Sep 5 '07 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
Are the tables [HREmps] and [Employees] in one-to-one relation by [Person_ID]? Or in one-to-many?
Sep 5 '07 #2

JConsulting
Expert 100+
P: 603
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Person_Id, LastName, FirstName
  3. FROM HREmps LEFT JOIN Employees ON [HREmps ].[Person_Id] = [Employees].[Person_Id]
  4. WHERE ([HREmps ].[Person_Id] Is Null);
  5.  
  6.  
Sep 6 '07 #3

P: 20
you can try this option. Its an unmatched query

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Person_Id, LastName, FirstName
  3. FROM HREmps LEFT JOIN Employees ON [HREmps ].[Person_Id] = [Employees].[Person_Id]
  4. WHERE ([HREmps ].[Person_Id] Is Null);
  5.  
  6.  
There was a typo in the WHERE clause; it should be
Expand|Select|Wrap|Line Numbers
  1. WHERE ([Employees].[Person_Id] Is Null);
But that works perfectly! Took the load/requery time from 2mins to 2seconds.

Thanks!
Sep 6 '07 #4

JConsulting
Expert 100+
P: 603
There was a typo in the WHERE clause; it should be
Expand|Select|Wrap|Line Numbers
  1. 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
Sep 7 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.