How to improve the performance of the SQL query which uses table variable which has more than 50k en-CodePudding

Background

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 @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 ) ```

CodePudding user response：

Please change the variable table to temporary temp table because variable table have a fixed value for cardinality stimate

enter code here

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 )

CodePudding user response：

try below url & follow steps

also check statistics in db level

Link 1 :https://www.c-sharpcorner.com/blogs/sql-database-performace-tunning

Link 2 : https://www.c-sharpcorner.com/blogs/dynamic-sql-table-partition-for-improve-query-performance