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.
3条答案
按热度按时间1szpjjfi1#
You have two problems in the current query plan:
NOT IN
which has weird effects when nullable columns are involved , you get three lookups on the inner table.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.You also want one of the following indexes
as well as this index (although this one is not as essential)
a0x5cqrl2#
You can do that by using
NOT EXISTS
:5n0oy7gb3#
If there any foreign key between two table you better use join but you can try CTE Queries and see the result: