More efficient SQL Query Access

Question

I have a SQL Query that works when the number of values is small, but in this instance I have around 5500 values in my "customerListLINKED" table and the query is taking 20 minutes or so to run. Is there a more efficient way to write with query?

SELECT 
    CustomerListLINKED.Base8, 
    (COUNT (Filtered_ZFEWN.Notification)) AS [Historical Count of Jobs]
FROM 
    CustomerListLINKED 
LEFT JOIN 
    Filtered_ZFEWN ON CustomerListLINKED.Base8 = Filtered_ZFEWN.[Base 8]
WHERE 
    Filtered_ZFEWN.[Base 8] IN (SELECT CustomerListLINKED.Base8 
                                FROM CustomerListLINKED) 
GROUP BY 
    Filtered_ZFEWN.[Base 8], CustomerListLINKED.Base8;

I am using MS Access.

Gordon Linoff · Accepted Answer · 2018-07-11 15:44:11Z

1

You should be able to just use an INNER JOIN and dispense with the IN clause:

SELECT CustomerListLINKED.Base8, COUNT(Filtered_ZFEWN.Notification) AS 
[Historical Count of Jobs]
FROM CustomerListLINKED INNER JOIN
     Filtered_ZFEWN
     ON CustomerListLINKED.Base8 = Filtered_ZFEWN.[Base 8]
GROUP BY CustomerListLINKED.Base8;

You want an index on Filtered_ZFEWN([Base 8]).

answered Jul 11, 2018 at 15:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ClaireLandis Over a year ago

Well I can't use the inner join, I'll have to keep the left join, but I think my problem is I was not aware that you can use the Group By Clause without a Where Clause. That must have been a misunderstanding in my knowledge. I will do some tests to make sure this yields the same results on a smaller data set and accept the answer if it test accurately. Thanks for the help

Collectives™ on Stack Overflow

More efficient SQL Query Access

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related