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.
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;