tune query in sql

Question

Hi,

How to tune below query in sql,if it has 1 billion records

Select A.*,B.* from  INTO TEMP TableA inner join TableB  
ON TableA.PersonID=TableB.PersonaID
where TableA.PersonCity In ('A','B','C') and 
TableB.personCity in ('A','B','C'))

Answer 1

Are both tables equally large, or is one smaller than the other?

It would certainly help to have an index on (personCity, PersonaID) on both tables.

At least if the condition on personCity selects just a smaller group of rows in the tables. If it selects, say, 10 % of the rows, it may not help much.

Also, the condition looks a little funny to me. So if personID 123 is in London i TableA and in Paris in the other we want that row, but not if that person is in Madrid?

If you can expect the query to return several millions of rows, you may also have to break up the operation in chunks to avoid filling up the log file.

Answer 2

Hi @Vineet S

It is suggested to filter the data before join.

Try this:

SELECT A.*, B.*
INTO TEMP
FROM (SELECT * FROM TableA WHERE PersonCity IN ('A', 'B', 'C')) A
INNER JOIN (SELECT * FROM TableB WHERE PersonCity IN ('A', 'B', 'C')) B
ON A.PersonID = B.PersonaID;

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".