Share via

tune query in sql

Vineet S 1,310 Reputation points
Oct 26, 2024, 6:49 AM

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'))
Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,069 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,663 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 112.8K Reputation points MVP
    Oct 26, 2024, 9:16 AM

    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.


  2. LiHongMSFT-4306 28,806 Reputation points
    Oct 28, 2024, 1:53 AM

    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".

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.