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));
LVL 1
Mik MakConsultantAsked:
Who is Participating?
 
Kelvin SparksCommented:
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
0
 
Mik MakConsultantAuthor Commented:
Hi Kelvin - yes a tmp table could be a solution - will look into it later today
0
 
PortletPaulfreelancerCommented:
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
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Mik MakConsultantAuthor Commented:
Hi PortletPaul - I'm not sure I quite understand - can you show me what the Query would look like with your Is Null ?
0
 
PortletPaulfreelancerCommented:
WHERE tbl_SALES_EXIST.CustomerID Is Null
;

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

WHERE tbl_SALES_EXIST.ArticleID Is Null
;
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
 
Mik MakConsultantAuthor Commented:
By using a temp local table to do the join, the time went from Infinite to sub 3 seconds :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.