SQL query is not giving bad performance with NOT IN & NOT EXISTS

Question

NOTE : Table Valued Paramter is having 196 rows & MST_DOC Table is having 10,000 rows There are no indexes on the MST_DOC Table

WAY 1 WITH NOT IN (this is taking 30 second to execute one Query)

ALTER PROC [dbo].[BULK_INSERT_DOC]
(
    @pCREATED_BY VARCHAR(50),
    @dt as [dbo].[MST_DOC_02] readonly --Table valued Parameter
)

AS
BEGIN
    --Inserted Into Temp Table From @DT which is Table Valued Parameter
    SELECT * INTO #TEMP_INSERT FROM @DT WHERE 
    DOC_CODE + DOC_NAME + doc_desc 
    NOT IN(SELECT DOC_CODE + DOC_NAME + doc_desc FROM MST_DOC with(nolock))

    --Inserted Into Temp Table From @DT which is Table Valued Parameter
    SELECT * INTO #TEMP_UPDATE FROM @DT 
    WHERE DOC_CODE + DOC_NAME + doc_desc 
    IN (SELECT DOC_CODE + DOC_NAME + doc_desc FROM MST_DOC with(nolock))

    --Remaining Logic
END

WAY 2 WITH NOT EXIST (this is taking 10 seconds)

ALTER PROC [dbo].[BULK_INSERT_DOC]
(
    @pCREATED_BY VARCHAR(50),
    @dt as [dbo].[MST_DOC_02] readonly --Table valued Parameter
)

AS
BEGIN
    Begin --Creation Of Temp Tables
        Create Table #TempMST_Doc (
        [DOC_CODE] Varchar(500) COLLATE database_default,
        [DOC_NAME] Varchar(500) COLLATE database_default,
        [DOC_DESC] Varchar(500) COLLATE database_default,
        [ENTRYDATE] Varchar(100) COLLATE database_default,
        [DEPT_ID] Varchar(100) COLLATE database_default,
        [FACILITY_ID] Varchar(100) COLLATE database_default,
        [ACT_LOCID] Varchar(50) COLLATE database_default,
        [Doc_Data] Varchar(5000) COLLATE database_default)

        CREATE  nonCLUSTERED INDEX IX_1 on #TempMST_Doc ([DOC_CODE])
        CREATE  nonCLUSTERED INDEX IX_2 on #TempMST_Doc ([DOC_NAME])
        CREATE  nonCLUSTERED INDEX IX_3 on #TempMST_Doc ([DOC_DESC])

        Create Table #TEMP_INSERT ([DOC_CODE] Varchar(5000) COLLATE database_default,
        [DOC_NAME] Varchar(5000) COLLATE database_default,
        [DOC_DESC] Varchar(5000) COLLATE database_default,
        [ENTRYDATE] Varchar(100) COLLATE database_default,
        [DEPT_ID] Varchar(100) COLLATE database_default,
        [FACILITY_ID] Varchar(100) COLLATE database_default,
        [ACT_LOCID] Varchar(50) COLLATE database_default)

        Create Table #TEMP_UPDATE ([DOC_CODE] Varchar(5000) COLLATE database_default,
        [DOC_NAME] Varchar(5000) COLLATE database_default,
        [DOC_DESC] Varchar(5000) COLLATE database_default,
        [ENTRYDATE] Varchar(100) COLLATE database_default,
        [DEPT_ID] Varchar(100) COLLATE database_default,
        [FACILITY_ID] Varchar(100) COLLATE database_default,
        [ACT_LOCID] Varchar(50) COLLATE database_default)
End

Insert Into #TempMST_Doc ([DOC_CODE] ,[DOC_NAME] ,[DOC_DESC], [Doc_Data] )
Select [DOC_CODE] ,[DOC_NAME] ,[DOC_DESC],([DOC_CODE]+[DOC_NAME]+[DOC_DESC]) From @dt

Insert Into #TEMP_INSERT ([DOC_CODE] ,[DOC_NAME] ,[DOC_DESC] ,[ENTRYDATE] ,[DEPT_ID] ,[FACILITY_ID] ,[ACT_LOCID])
Select [DOC_CODE] ,[DOC_NAME] ,[DOC_DESC] ,[ENTRYDATE] ,[DEPT_ID] ,[FACILITY_ID] ,[ACT_LOCID] From 
#TempMST_Doc Tempdoc
WHERE 
NOT Exists 
(SELECT null FROM MST_DOC With (NoLock) Where 
([Doc_Data]) = 
(MST_DOC.DOC_CODE + MST_DOC.DOC_NAME + MST_DOC.doc_desc))




Insert Into #TEMP_UPDATE ([DOC_CODE] ,[DOC_NAME] ,[DOC_DESC] ,[ENTRYDATE] ,[DEPT_ID] ,[FACILITY_ID] ,[ACT_LOCID])
    Select [DOC_CODE] ,[DOC_NAME] ,[DOC_DESC] ,[ENTRYDATE] ,[DEPT_ID] ,[FACILITY_ID] ,[ACT_LOCID] From 
    #TempMST_Doc Tempdoc
    WHERE 
    Exists 
    (SELECT null FROM MST_DOC With (NoLock) Where 
    ([Doc_Data]) = 
    (MST_DOC.DOC_CODE + MST_DOC.DOC_NAME + MST_DOC.doc_desc))
END

I have used both the method with NOT IN & also with Not Exists but not able to get proper output.

I have thought of creating indexes in temporary table but it is not giving me any enhanced output.

Below is the Indexing used in the Temporary table

CREATE  nonCLUSTERED INDEX IX_1 on #TempMST_Doc ([DOC_CODE])
CREATE  nonCLUSTERED INDEX IX_2 on #TempMST_Doc ([DOC_NAME])
CREATE  nonCLUSTERED INDEX IX_3 on #TempMST_Doc ([DOC_DESC])

I have also referred the articles for indexing on to the Temporary table & Table variable. I get to know that it is not the best practice to write the indexes on to both.

I need one more Suggestion that If I use index on Table Variable & on temporary table then what will be good. or any disadvantage of creating the index on the both

Could you get the actual execution plans of both queries and post them to http://www.pastetheplan.com? If you could then provide the links it's likely it will help a lot. — Rich Benner, Dec 02 '16 at 09:03
Can you clarify various things. @dt is not actually used in sp 2. sp 2 seems to be doing something different to sp 1 so it's difficulty to compare performance. You might have more luck creating one index on all of the fields MST_Doc or even creating a calculated column with those three columns and putting an index on that. — Nick.Mc, Dec 02 '16 at 09:08
@ Nick.McDermaid I have modified the Question actual it was typo error, both the queries are for the same purpose. for making better performance — Hardik Parmar, Dec 02 '16 at 09:29
At least, first query has `IN` and `NOT IN` parts whereas second one - only `NOT EXISTS`. — Ivan Starostin, Dec 02 '16 at 09:57
What's up with `+`? Why don't you compare the fields directly? Doing so would produce correct results in all cases and be [sargable](https://en.wikipedia.org/wiki/Sargable) (i.e. potentially much quicker). Also, beware that NOT IN is not logically equivalent to NOT EXISTS in the presence of NULLs. — Branko Dimitrijevic, Dec 02 '16 at 10:07
Queries are different. They produce different outcomes. They do different job. — Ivan Starostin, Dec 02 '16 at 10:15
The row numbers are extremely small and the performance very bad. The queries do different things so they can't be compared at all. If they *were* doing the same job, they are simple enough that the execution plan would be the same as well. Fix the queries, compare the fields instead of concatenating strings, add indexes to the table — Panagiotis Kanavos, Dec 02 '16 at 11:55
@Panagiotis Kanavos I am checking for combination of all three columns. so how can I compare the fields directly can you give example. — Hardik Parmar, Dec 02 '16 at 11:58
A simple `AND` in the Where clause? Anyway, don't do this. It's impossible to use indexes if you apply functions on the column values. If you really want the full combination, create a computed column and add it to an index. Although in this case concatenation doesn't make any sense. 10K rows is really *no* data, especially when you only perform an equality comparison — Panagiotis Kanavos, Dec 02 '16 at 12:06
PS `nolock` means "read dirty data". I don't thing you want that — Panagiotis Kanavos, Dec 02 '16 at 12:08
@PanagiotisKanavos Yes I know that. sir. thanks but as "Ivanha"s said that "NOT IN is not logically equivalent to NOT EXISTS in the presence of NULLs" what it means — Hardik Parmar, Dec 02 '16 at 12:21
thanks it worked for me by comparing the fields directly. it is now only taking 2 seconds. — Hardik Parmar, Dec 02 '16 at 12:22
@HardikParmar Let's say that (in the sub-query) there are no matching rows **and** there is a row containing NULL. In that case, NOT IN evaluates to NULL, while NOT EXISTS evaluates to true. In other words, they give different results on the same data. This is just another wierdness of the three-valued logic - see [here](http://stackoverflow.com/a/9344100/533120) and [here](http://stackoverflow.com/a/11215611/533120) for why the entire NOT IN on even a single NULL collapses into NULL. — Branko Dimitrijevic, Dec 02 '16 at 22:19

SQL query is not giving bad performance with NOT IN & NOT EXISTS

0 Answers0