How to improve the performance of the SQL query which uses table variable which has more than 50k entries?

Question

I have to update two temp tables @tbNewEntry and @tbUpdateEntry from another temp table @OnlineDataBase (I got the Data from online into this table) based on some conditions.

Conditions to update:

• Update @tbNewEntry if CityCode is not present in your Local DB (LocalFunction returns the list of City codes. This function is just inner join of two other tables)
• Update @tbUpdateEntry if CityCode is Present in Local DB but the CityName is not matching with @OnlineDataBase.

I followed some points from Google and created the required indexes. But still not able to improve the performance.

Guide me to improve the performance of following the query. The tables have millions of records.

DECLARE @OnlineDataBase TABLE(ID INT IDENTITY(1,1),CityCode NVARCHAR(10),CityName NVARCHAR(MAX),Area INT,  Population INT);
DECLARE @tbNewEntry TABLE(ID INT IDENTITY(1,1),CityCode NVARCHAR(10),CityName NVARCHAR(MAX),Area INT,  Population INT);
DECLARE @tbUpdateEntry TABLE(ID INT IDENTITY(1,1),CityCode NVARCHAR(10),CityName NVARCHAR(MAX),Area INT,  Population INT);


INSERT INTO @tbNewEntry(CityCode,CityName,Area,Population)
SELECT TN.CityCode, TN.CityName, TN.Area, TN.Population
FROM @OnlineDataBase TN WHERE TN.CityCode NOT IN (SELECT CityLocalDB.CityCode IN FROM LocalFunction as CityLocalDB)


INSERT INTO @tbUpdateEntry(CityCode,CityName,Area,Population)
SELECT TN.CityCode, TN.CityName, TN.Area, TN.Population
FROM @OnlineDataBase TN WHERE TN.CityCode IN (SELECT CityLocalDB.CityCode IN FROM LocalFunction as CityLocalDB
WHERE TN.CityName !=CityLocalDB.CityName ) 

Answer 1

You should change the variable table to temporary temp table because variable table has a fixed value for cardinality estimate

CREATE  TABLE #tbNewEntry (ID INT IDENTITY(1,1),CityCode NVARCHAR(10),CityName NVARCHAR(MAX),Area INT,  Population INT);
CREATE TABLE #tbUpdateEntry (ID INT IDENTITY(1,1),CityCode NVARCHAR(10),CityName NVARCHAR(MAX),Area INT,  Population INT);


INSERT INTO #tbNewEntry(CityCode,CityName,Area,Population)
SELECT TN.CityCode, TN.CityName, TN.Area, TN.Population
FROM #OnlineDataBase TN WHERE TN.CityCode NOT IN (SELECT CityLocalDB.CityCode IN FROM LocalFunction as CityLocalDB)


INSERT INTO #tbUpdateEntry(CityCode,CityName,Area,Population)
SELECT TN.CityCode, TN.CityName, TN.Area, TN.Population
FROM #OnlineDataBase TN WHERE TN.CityCode IN (SELECT CityLocalDB.CityCode IN FROM LocalFunction as CityLocalDB
WHERE TN.CityName !=CityLocalDB.CityName ) 

Answer 2

You will probably see a bigger performance improvement if you replaced the your "IN" and "NOT IN" statements with "EXISTS" and "NOT EXISTS".

Eg:

INSERT INTO @tbNewEntry(CityCode,CityName,Area,Population)
SELECT TN.CityCode, TN.CityName, TN.Area, TN.Population
FROM @OnlineDataBase TN 
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM LocalFunction as CityLocalDB 
        WHERE 
            CityLocalDB.CityCode = TN.CityCode
    )