sqlms-accessvbadatabase-performanceright-join

MS ACCESS SQL Join Subquery


I have two tables: newparts, storedparts

I insert the parts of the newparts, which are not jet in the storedparts into the storedparts:

SQL_String = "INSERT INTO storedparts " & _
             "SELECT newparts.* " & _
             "FROM storedparts " & _
             "RIGHT JOIN newparts ON (storedparts.identifier = newparts.identifier) AND (storedparts.timeStamp = newparts.timeStamp) " & _
             "WHERE ((storedparts.AutoID) Is Null);"

This is working fine so far. Now the Problem: Table storedparts is getting so big that the programm is taking too Long for the join process. My solution: Just compare the newparts not to all parts of the storedparts, but just to parts that aren't older than 4 days... I tried a subquery like this, but i can't get it to run.

SQL_String = "INSERT INTO storedparts " & _
             "SELECT newparts.* " & _
             "FROM storedparts (WHERE storedparts.timestamp > Now() - 4) " & _
             "RIGHT JOIN newparts ON (storedparts.identifier = newparts.identifier) AND (storedparts.timeStamp = newparts.timeStamp) " & _
             "WHERE ((storedparts.AutoID) Is Null);"

Any help is appreciated.


Solution

  • This wouldn't be a problem if your tables have indexes.

    CREATE INDEX ndx_sp_identifier ON storedparts (identifier);
    CREATE INDEX ndx_np_identifier ON newparts (identifier);
    

    Then I suggest you change your query to something like this as @jarlh pointed out.

    INSERT INTO storedparts 
    SELECT newparts.* 
    FROM newparts
    LEFT JOIN storedparts 
    ON newparts.identifier = storedparts.identifier
    AND newparts.timeStamp = storedparts.timeStamp
    WHERE storedparts.AutoID Is Null;