We help IT Professionals succeed at work.

Access 2010 Slow Left Join query, mixed backends

I have an issue with running a slow left Join query, on mixed backends. The Exist table is on a SQL 2014 server, and the New table is on a Access 2010 backend. Both tables contains around 250.000 records. Querying them each goes fine, but doing a left join, just seems to hang Access. Theres indexes on all the fields involved in the join.

SELECT tbl_SALES_NEW.CustomerID
FROM tbl_SALES_NEW LEFT JOIN tbl_SALES_EXIST ON (tbl_SALES_NEW.CustomerID = tbl_SALES_EXIST.CustomerID) AND (tbl_SALES_NEW.ArticleID = tbl_SALES_EXIST.ArticleID)
WHERE (((tbl_SALES_EXIST.CustomerID) Is Null) AND ((tbl_SALES_EXIST.ArticleID) Is Null));
Comment
Watch Question

LEFT joins are slow by nature. Where you are joining over different platforms you are asking Access to move heaven an earth. Assuming a close match it is going to compare every join one by one, so you're effectively doing 250,000 selects one after another. I'd look at building a bit of code that sucks the SQL Server table into a temporary table in Access and doing it there, or even better if you have the rights to do so, do a temp copy to sql server, perform the join there are return the results.


Kelvin
Mik MakConsultant

Author

Commented:
Hi Kelvin - yes a tmp table could be a solution - will look into it later today
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
small point: with the join logic requiring BOTH CustomerID & ArticleID

you only need to test for a single IS NULL

but avoiding the comms between the 2 platforms would make a much bigger impact of course
Mik MakConsultant

Author

Commented:
Hi PortletPaul - I'm not sure I quite understand - can you show me what the Query would look like with your Is Null ?
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
WHERE tbl_SALES_EXIST.CustomerID Is Null
;

------ OR -------

WHERE tbl_SALES_EXIST.ArticleID Is Null
;
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Paul's suggestion is based on the ANSI SQL behaviour that any comparison operator will fail with NULL values, with exception of IS (NOT) NULL.
Mik MakConsultant

Author

Commented:
By using a temp local table to do the join, the time went from Infinite to sub 3 seconds :)