Speeding Up My Query

[bplantes]

I have designed a form to bridge one customer record with a master customer record in a table. For example:

CustomerName, CustomerNumber:
ABC Company, 101;
ABC Corp. , 102;
ABC Inc., 103;
ABC Co., 104;
ABC Comp., 105;

MasterCustomerName, MasterCustomerNum:
ABC Master, 1001

Bridge:
101, 1001;
102, 1001;
103, 1001;
104, 1001;
105, 1001

To give me the customers that have not yet been assigned to a Master Record, I wrote query that looks at a table and finds all customer numbers that are not in the Bridge table:

"SELECT DISTINCT CUSTOMER_NBR, CUSTOMER_NAME
FROM tblX
WHERE NOT Exists (SELECT BridgeCustomerNumber, From tblCustomerBridge WHERE BridgeCustomerNumber = CUSTOMER_NBR)
ORDER BY CUSTOMER_NAME;"

tblX has approx 500,000 records and it is taking about 10 to 12 seconds to run the query. I have to refresh the query every time I use the form to update the Bridge Table.

Any advice on how to speed up the runtime of the query?

Thanks in advance,

bplantes

[Stewart Ross]

...tblX has approx 500,000 records and it is taking about 10 to 12 seconds to run the query. I have to refresh the query every time I use the form to update the Bridge Table.

Hi. Subqueries are very useful for certain tasks, but on large datasets the use of a subquery can lead to a performance hit as you are finding. As an alternative without the subquery, try the following, changing the field names to the proper ones before you do so:

SELECT tblX.[Customer No], tblX.[Customer Name]
FROM tblX LEFT JOIN Bridge ON tblX.[Customer No] = Bridge.Customer_Nbr
WHERE (((Bridge.Customer_Nbr) Is Null))
ORDER BY tblX.[Customer Name];
 
 

I don't know how it will perform on 500,000 records but the left-joined tables where all we want to find are nulls at the bridge end should be faster than the subquery I reckon.

-Stewart

[bplantes]

Thanks for that... I have worked with MS SQL before and have used "Left Outer Join" which would only take the left, non-joined, records. Access doesn't seem to have that functionality. I just implemented this into the code and it worked great. Thanks for the help.

[NeoPa]

MS SQL's LEFT [OUTER] JOIN (where the OUTER keyword is optional) is equivalent to Access's LEFT JOIN.