Need to optimised MSSql query with clause not in

Question

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.

_The NOLOCK hint has been deprecated in favor of READ COMMITTED SNAPSHOT (RCSI). Starting with SQL Server 2022, these hints will no longer be honored, and the transaction will operate under default isolation level (RCSI, if enabled, or READ COMMITTED if not). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature._ — jarlh, May 03 '23 at 08:21
For query performance help we need at a minimum: the tables *and index* definitions, and please share the query plan via https://brentozar.com/pastetheplan — Charlieface, May 03 '23 at 09:56
@Charlieface https://www.brentozar.com/pastetheplan/?id=ryh-9Rk4n — Sup Ravi Kumar, May 03 '23 at 12:57

score 2 · Accepted Answer · answered May 03 '23 at 13:19

You have two problems in the current query plan:

Because you are using NOT IN which has weird effects when nullable columns are involved, you get three lookups on the inner table.
To compound that, the table is not indexed.

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)

Delta · Answer 2 · 2023-05-03T08:01:11.103

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
)

Mohammad.g · Answer 3 · 2023-05-03T07:53:38.400

-1

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)

edited May 03 '23 at 07:53

answered May 03 '23 at 05:27

Mohammad.g

54
6

2

It's generaly a bad idea to use `WITH (NOLOCK)` – Delta May 03 '23 at 07:06
@Delta Can you please explain why this is bad idea to use WITH(NOLOCK), Till today I was told that it ensure dead lock safety, Also in our organization, It is mandatory to use it with select query. – Sup Ravi Kumar May 03 '23 at 07:22
3

[Bad habits: putting `nolock` everywhere](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) – Dale K May 03 '23 at 07:30
Yes, by using `NOLOCK` you avoid dead locks but you can't garanty the integrity of your dataset. That's why in my compagny I don't allow any `WITH (OPTIONS...)` and I let MSSQL apply the server's rules and I let it design the execution plaan. – Delta May 03 '23 at 07:58
@Delta yes, you are right i edit my query and delete WITH (NoLock) – Mohammad.g May 03 '23 at 08:01
@SupRaviKumar, to go further in the rules that are applied in your company I would recommend you this great tool that could help you in improving the health of your MSSQL : https://www.brentozar.com/responder/ – Delta May 03 '23 at 08:05
_The NOLOCK hint has been deprecated in favor of READ COMMITTED SNAPSHOT (RCSI). Starting with SQL Server 2022, these hints will no longer be honored, and the transaction will operate under default isolation level (RCSI, if enabled, or READ COMMITTED if not). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature._ – jarlh May 03 '23 at 08:22
1

@jarlh It's an April fools joke, I wish it wasn't... – Charlieface May 03 '23 at 13:21

Need to optimised MSSql query with clause not in

3 Answers3