SQL Server Need to optimised MSSql query with clause not in

ogsagwnx  于 2023-05-05  发布在  其他
关注(0)|答案(3)|浏览(147)

Below is the query I need to optimized:

INSERT INTO dbo.tblLabOrderNameMaster (
        Labpanelcdname, labtestcdabbr ,
        Status, AppDateCreated, AppDateModified, UserId
        )
        SELECT TestName, Test, 1, GETDATE(), GETDATE(), 1
        FROM dbo.tblLabsDrawImport WITH(NOLOCK) WHERE TestName NOT IN (SELECT Labpanelcdname FROM dbo.tblLabOrderNameMaster WITH(NOLOCK) WHERE Status = 1)

tblLabOrderNameMaster contain 20000 records whereas tblLabsDrawImport contains 100000 records, Both table have their primary keys.

1szpjjfi

1szpjjfi1#

You have two problems in the current query plan:

I suggest you convert to a NOT EXISTS . Note that the result when nulls are involved is different, make sure you understand it.

And don't use NOLOCK , it's not a go-faster switch, and has serious implications for correctness.

INSERT dbo.tblLabOrderNameMaster (
  Labpanelcdname, labtestcdabbr,
  Status, AppDateCreated, AppDateModified, UserId
)
SELECT
  ldi.TestName,
  ldi.Test,
  1,
  GETDATE(),
  GETDATE(),
  1
FROM dbo.tblLabsDrawImport ldi
WHERE NOT EXISTS (SELECT 1
    FROM dbo.tblLabOrderNameMaster lonm
    WHERE ldi.TestName = lonm.Labpanelcdname
      AND lonm.Status = 1
);

You also want one of the following indexes

dbo.tblLabOrderNameMaster (Status, Labpanelcdname)
-- alternatively
dbo.tblLabOrderNameMaster (Labpanelcdname) INCLUDE (Status) WHERE (Status = 1)

as well as this index (although this one is not as essential)

dbo.tblLabsDrawImport (TestName) INCLUDE (Test)
a0x5cqrl

a0x5cqrl2#

You can do that by using NOT EXISTS :

INSERT INTO dbo.tblLabOrderNameMaster (
    Labpanelcdname,
    labtestcdabbr,
    Status,
    AppDateCreated,
    AppDateModified,
    UserId
)
SELECT
    ldi.TestName,
    ldi.Test,
    1,
    GETDATE(),
    GETDATE(),
    1
FROM dbo.tblLabsDrawImport AS ldi
WHERE NOT EXISTS (
    SELECT *
    FROM dbo.tblLabOrderNameMaster AS ln
    WHERE ln.ln.Labpanelcdname = ldi.TestName
        AND ln.Status = 1
)
5n0oy7gb

5n0oy7gb3#

If there any foreign key between two table you better use join but you can try CTE Queries and see the result:

DECLARE @CurrentDate DATETIME = GETDATE()
;WITH CTE_NotRequireRows
AS
(
    SELECT Labpanelcdname FROM 
    FROM dbo.tblLabOrderNameMaster 
    WHERE Status = 1    
)
INSERT INTO dbo.tblLabOrderNameMaster
(Labpanelcdname, labtestcdabbr ,Status, AppDateCreated, AppDateModified,UserId)
SELECT TestName, Test, 1, @CurrentDate , @CurrentDate , 1
FROM dbo.tblLabsDrawImport 
WHERE TestName NOT IN (SELECT Labpanelcdname FROM CTE_NotRequireRows)

相关问题