This is not a problem that needs answering, more of a query on why this is happening.
I have a field in one table that is populated with 'Y' Or 'N', and i have a query that simply takes the value of that field and pops into another table
The table has approximately 25,000 records in it
The query below takes about 25 seconds to run
UPDATE ObjectivesApproved
INNER JOIN Approved
ON ObjectivesApproved.ID = Approved.ID
SET ObjectivesApproved.Football = [Approved].[Cri Football Related];
Removing the JOIN operation makes the query take even longer.
If however i do the below, the entire operation takes less than 5 seconds, even though it's executing 2 queries
UPDATE ObjectivesApproved
INNER JOIN Approved
ON ObjectivesApproved.ID = Approved.ID
SET ObjectivesApproved.Football = 'Y'
WHERE (([Approved].[Cri Football Related]='Y'));
UPDATE Approved
INNER JOIN ObjectivesApproved
ON Approved.ID = ObjectivesApproved.ID
SET ObjectivesApproved.Football = 'N'
WHERE (([ObjectivesApproved].[Football] Is Null));
I'm happy with my workaround, even if it is a little inelegant, but to further my understanding of SQL why might this be happening?
Your first version is updating 25K rows no matter what but it has to keep the tables in sync since it is using values from one into the other on a row by row basis. Every row that is updated has to read from a field - 25K times.
Your second version (both statements) filter data instead of row by row comparison. Internally a set of records is found and then updated in a batch instead of row by row computation. The value 'Y' doesn't have to be looked up each time - it is constant.
Imagine if I asked you to color 25K boxes black or white based on a list I gave you. Is it faster to pick up the first box, check the list, and color it, pick up the second box, check the list, color it, repeat. Or is it faster to pull out all ones supposed to be white and color them, then all the black ones and color them. Note you only have to "check the list" 2 times in the second case but 25K times the first one.