CODEWITHSUNDEEP
sqlpostgresqlms-accessms-access-2007
santhosha
santhosha

Reputation: 440

SLOW running query in MS access

What causing this query to execute long time. Is sub query block creating a mess?

SELECT WD.IdentityUserID, WD.Email, AD.mail
FROM WD INNER JOIN AD ON WD.IdentityUserID = AD.GGDComputerUserId
WHERE (((AD.mail) Not In (SELECT EMAIL FROM WD)));

Upvotes: 0

Views: 890

Answers (2)

Stévillis
Stévillis

Reputation: 491

I believe that the problem is that for every record of the INNER JOIN you are looping over all the records of the WD table.

SELECT WD.IdentityUserID, WD.Email, AD.mail
FROM WD 
INNER JOIN AD ON WD.IdentityUserID = AD.GGDComputerUserId
WHERE NOT EXISTS(SELECT EMAIL FROM WD WHERE EMAIL = AD.mail);

Upvotes: 1

mangusta
mangusta

Reputation: 3544

Not sure if this is going to help but you may try to reduce the size of "right side" of join by filtering out the emails beforehand: 

SELECT  
 WD.IdentityUserID,  
 WD.Email,  
 T.mail  
FROM WD  
INNER JOIN  
 (  
    SELECT
     mail,  
     GGDComputerUserId     
    FROM AD  
    WHERE  
     mail Not In (SELECT EMAIL FROM WD)  
 ) T  
ON WD.IdentityUserID = T.GGDComputerUserId;

(In case if query is already being optimized for that, then this would make no difference though)

Upvotes: 0

Related Questions