CODEWITHSUNDEEP
sqlsql-server
Phh
Phh

Reputation: 135

Improve performance of multiple insert and update queries

I have two tables: Sale_Source (10.000 rows) and Sale_Target (1 billion rows). I have four queries to INSERT and UPDATE Sale_Target with the data from Sale_Source.

Queries

INSERT query #1:

INSERT INTO dbo.Sale_Target (id, Salevalue, Salestring, Date)
    SELECT id, Salevalue, Salestring, Date 
    FROM dbo.Sale_Source s
    WHERE NOT EXISTS (SELECT 1 FROM dbo.Sale_Target t ON s.id = t.id)

UPDATE query #1 (when the date are the same):

UPDATE t
SET t.Salevalue = s.Salevalue,
    t.Salestring = s.Salestring
FROM dbo.Sale_Source s 
JOIN dbo.Sale_Target t ON t.id = s.id AND s.Date = t.Date
WHERE t.Salevalue <> s.Salevalue OR t.Salestring <> s.Salestring

UPDATE query #2 (when date on SaleSource > Date on SaleTarget):

UPDATE t 
SET t.Salevalue = s.Salevalue,
    t.Salestring = s.Salestring 
FROM dbo.Sale_Source s  
JOIN dbo.Sale_Target t ON t.id = s.id AND s.Date > t.Date

UPDATE query #3 (when id on Source is null in join clause):

UPDATE t 
SET t.Salevalue = null,
    t.Salestring = null 
FROM dbo.Sale_Source s 
LEFT JOIN dbo.Sale_Target t ON t.id = s.id 
WHERE s.id IS NULL

The four queries require 1 hr. 30 mins. to finish, which is very slow for the source table with only 10.000 rows. I think the problem here is that each time when four queries run, they need to JOIN two source and target tables again, which cost a lot of time.

Therefore I have an idea:

  1. I will create a query which saves the matched rows between two tables (source and target) into table temp_matched, and save the non matched rows (non matched to target) into temp_nonmatched.

    For this, I have problem now with MERGE query because in MERGE we can't save data into another table.

  2. Use temp_nonmatched in INSERT query, in UPDATE query. I will replace table Sale_Source with temp_matched.

Do you have any idea to do it or can we optimize these four queries in another way?

Thank you.

Table Definition: 

CREATE TABLE [dbo].[Sale_Target](
   [id] [numeric](8, 0) NOT NULL,
   [Salevalue] [numeric](8, 0)  NULL,
   [Salestring] [varchar](20)  NULL,
   [Date] [Datetime2](7) NULL
) ON [PRIMARY]
CREATE NONCLUSTERED COLUMNSTORE INDEX "NCCI_Sale_Target"  
    ON [dbo].[Sale_Target] (id,Date) 

CREATE TABLE [dbo].[Sale_Source](
   [id] [numeric](8, 0) NOT NULL,
   [Salevalue] [numeric](8, 0)  NULL,
   [Salestring] [varchar](20)  NULL,
   [Date] [Datetime2](7) NULL
) ON [PRIMARY]
CREATE NONCLUSTERED COLUMNSTORE INDEX "NCCI_Sale_Source"  
    ON [dbo].[Sale_Target] (id,Date)

Upvotes: 0

Views: 217

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

Target Tables has no Index.

First thing I would do is index the Target Table on id and date.

Upvotes: 2

Related Questions