CODEWITHSUNDEEP
sqlms-accessvba
cheslijones
cheslijones

Reputation: 9194

MS Access Update SQL Query Extremely Slow and Multiplying the Amount of Records Updated

I am stumped on how to make this query run more efficiently/correctly. Here is the query first and then I can describe the tables that are involved:

UPDATE agg_pivot_test AS p
LEFT JOIN jd_cleaning AS c
    ON c.Formerly = IIF(c.Formerly LIKE '*or*', '*' & p.LyFinalCode & '*', CStr(p.LyFinalCode))
SET p.CyFinalCode = c.FinalCode
WHERE p.CyFinalCode IS NULL AND c.Formerly IS NOT NULL;

agg_pivot_test has 200 rows of data and only 99 fit the criteria of WHERE p.CyFinalCode IS NULL. The JOIN needs some explaining. It is an IIF because some genius decided to link last year's data to this year's data using Formerly. It is a string because sometimes multiple items have been consolidated down to one so they use "or" (e.g., 632 or 631 or 630). So if I want to match this year's data I have to use Formerly to match last year's LyFinalCode. So this year the code might be 629, but I have to use the Formerly to map the items that were 632, 631, or 630 to the new code. Make sense? That is why the ON has an IIF. Also, Formerly is a string and LyFinalCode is an integer... fun.

Anyway, when you run the query it says it is updating 1807 records when again, there are only 200 records and only 99 that fit the criteria.

Any suggestions about what this is happening or how to fix it?

Upvotes: 0

Views: 480

Answers (1)

John Bingham
John Bingham

Reputation: 2006

An interesting problem. I don't think I've ever come across something quite like this before.

I'm guessing what's happening is that rows where CyFinalCode is null, are being matched multiple times by the join statement, and thus the join expression is calculating a cartesian product of row-matches, and this is the basis of the rows updated message. It seems odd, as I would have expected access to complain about multiple row matches, when row matches should only be 1:1 in an update statement.

I would suggest rewriting the query (with this join) as a select statement, and seeing what the query gives you in the way of output; Something like:

SELECT p.*, c.*
FROM agg_pivot_test p LEFT JOIN jd_cleaning c
    ON c.Formerly = IIF(c.Formerly LIKE '*or*', '*' & p.LyFinalCode & '*', CStr(p.LyFinalCode))
WHERE p.CyFinalCode IS NULL AND c.Formerly IS NOT NULL

I'm also inclined to suggest changing "... & p.LyFinalCode & ..." to "... & CStr(p.LyFinalCode) & ..." - though I can't really see why it should make a difference.

The only other thing I can think to suggest is change the join a bit: (this isnt guaranteed to be better necessarily - though it might be)

UPDATE agg_pivot_test AS p LEFT JOIN jd_cleaning AS c
    ON (c.Formerly = CStr(p.LyFinalCode) OR InStr(c.Formerly, CStr(p.LyFinalCode)) > 0)

(Given the syntax of your statement, I assume this sql is running within access via ODBC; in which case this should be fine. If I'm wrong the sql is running server side, you'll need to change InStr to SubString.)

Upvotes: 1

Related Questions