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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mik MakConsultantAuthor Commented:
Hi Kelvin - yes a tmp table could be a solution - will look into it later today
0
PortletPaulEE Topic AdvisorCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
PortletPaulEE Topic AdvisorCommented:
WHERE tbl_SALES_EXIST.CustomerID Is Null
;

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

WHERE tbl_SALES_EXIST.ArticleID Is Null
;
0
Qlemo"Batchelor", 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.