Need to optimize my code!

Here is how my code goes.

- query calling table A from DB1
- query calling table B from DB2

Note:
1. both tables are structurally identical but from different datasources - DB1 (CSV) and DB2 (postgres)
2. table A contains new data; table B contains existing data.

Requirements:
1. If row in table A doesn't exist in B, then insert into B.
2. If row in table B doesn't exist in A, then delete from B.
3. For matching rows, check if any changes in data.
If no change, leave it alone. If changed, then update table B with values from table A.

My long-winded method:
- Loop old table B. Delete that row if not found in new table A. Loop to next record and continue process.
- Loop new table A. Select if row found in B. If not found insert. If found, update row with new values from B. Loop to next record and continue process.

Hope it makes sense. The page has alot of loops and queries.
Would appreciate a shorter algorithm - shorter code or maybe a different comparison method.. any ideas?

Thanks,
Prem

Looping over the data is probably the only way. You could try using query of queries as well but this might be more complex than just looping.

The best advise would really be to scrap this entire system and build one that meets your needs effectively, becuase the current setup is woefully ineffecient.

Kiteless, I understand what you mean. However, since the client has a different internal system (FileMaker), that's how they export their data to us so we can add it into their web DB (postgres). I know - why not just use postgres. I guess they've got many internal systems connected to/and built with FileMaker, so that's another issue. Guess I'll stick to this for now.

Yes, the only alternative that I can see is looping over the data and manually pulling out what you want.

On a positive note, it's good this is done only when we get new data. Typically accessing the page maybe once a week and just 1 HTTP request. Even with a few 1,000s of records, it shouldn't be too slow.

I should ask if you've tried using query of queries to solve the problem?