Table A has 500k records with PK. Table B has 7million records with Table A PK as Foreign Key (FK)… I want to do a search on Table B where Pk X, Pk Y, Pk Z == Fk on Tables B and return record entry from both Table A&B. What I am thinking of, get records of Pk in Table A into memory then do a where statement on table B with the Primary keys X, Y & Z. Or, do a Join statement on Table A & table B. Constraint- api calls sql more than 300 times/ sec Any opinion on best approach? Any referrals to sql operations cheat sheet/good summary book. TC: 195k #sql
Join
Fundamentally a join by using a where exists can be quite performant. I would check the query plan on both. Also, check your indexes make sure you have sufficient coverage for the traffic
He needs entries from both tables, so it should be a join, not exists.
Use joins and check query plan uses proper index. If it’s getting called frequently then DB server will anyways cache the execution plan and it should run fast. Make sure that table statistics are updated.
Creating an execution plan for something so simple is not much of a burden. Caching or not, will not be the main issue here.
This is not stackoverflow
Providing a known correct (even if not optimal) query would have been an easy way to clarify what you are after. I couldn't tell for sure if you had a compound key with three components, or were trying to create separate matches on three non-compound keys.
Write it both ways and look at the query plans
This is the way