How to Optimizing the query, Replacing IN in sql query?

Question

I have the query which is takes more time run. I Find is because of IN operator in the query.I need to optimize it, pls advise

The query is

DECLARE @LevelList TABLE(BadgeNo NVARCHAR(200))  
INSERT INTO @LevelList(BadgeNo) EXEC spDataLevelSecurity SIP008548 
declare @RetCountValueOUT nvarchar(3)
SELECT  @RetCountValueOUT = COUNT([Badge No] ) FROM (       
        SELECT
            Distinct
            BC.BadgeNo AS [Badge No],
            dbo.ProperCase(FirstName + + ' ' + ISNULL(MiddleName,'') + CASE WHEN ISNULL(MiddleName,'') <> '' THEN ' ' ELSE '' END + ISNULL(LastName,'')) AS [Employee Name],
            ISNULL(Job.BusinessUnit,'') AS [Company],
            ' By ''' + dbo.ProperCase(F1.UserFullName) + ''' On ''' + REPLACE(CONVERT(NVARCHAR(11),CreatedOn,106),' ','-') + '''' AS [Creation Details],
            ' By ''' + dbo.ProperCase(F2.UserFullName) + ''' On ''' + REPLACE(CONVERT(NVARCHAR(11),VerifiedOn,106),' ','-') + '''' AS [Verification Details]
        FROM    
            Componentdetails BC 
        INNER JOIN Job 
            ON Job.BadgeNo = BC.BadgeNo
        INNER JOIN Employee
            ON Employee.BadgeNo = BC.BadgeNo
        LEFT JOIN FalconUsers F1
            ON F1.EmployeeID = BC.CreatedBy 
        LEFT JOIN FalconUsers F2
            ON F2.EmployeeID = BC.VerifiedBy 
        WHERE
            Isverified  = 1
            AND IsApproved = 0
            AND RejectTag = 0
        )A 
where a.[Badge No] in   ( Select BadgeNo from @LevelList )
select @RetCountValueOUT

Thanks in advance

http://stackoverflow.com/questions/1200295/sql-join-vs-in-performance — ficuscr, Feb 12 '15 at 05:48
I think the problem is in select clause, where you apply some operations to columns like `dbo.ProperCase`, `ISNULL`, `Convert`, `Replace` ... — Giorgi Nakeuri, Feb 12 '15 at 05:58
SQLServer always assume a table variables contain 1 row when creating the plan so it might use an inefficient plan if it contains a lot of rows. Try replacing the table variable with a temporary table and see if it makes any difference — adrianm, Feb 12 '15 at 06:30

Saravana Kumar · Answer 1 · 2015-02-12T05:54:47.533

Use INNER JOIN.

DECLARE @LevelList TABLE(BadgeNo NVARCHAR(200))  
INSERT INTO @LevelList(BadgeNo) EXEC spDataLevelSecurity SIP008548 
declare @RetCountValueOUT nvarchar(3)
SELECT  @RetCountValueOUT = COUNT([Badge No] ) FROM (       
        SELECT
            Distinct
            BC.BadgeNo AS [Badge No],
            dbo.ProperCase(FirstName + + ' ' + ISNULL(MiddleName,'') + CASE WHEN ISNULL(MiddleName,'') <> '' THEN ' ' ELSE '' END + ISNULL(LastName,'')) AS [Employee Name],
            ISNULL(Job.BusinessUnit,'') AS [Company],
            ' By ''' + dbo.ProperCase(F1.UserFullName) + ''' On ''' + REPLACE(CONVERT(NVARCHAR(11),CreatedOn,106),' ','-') + '''' AS [Creation Details],
            ' By ''' + dbo.ProperCase(F2.UserFullName) + ''' On ''' + REPLACE(CONVERT(NVARCHAR(11),VerifiedOn,106),' ','-') + '''' AS [Verification Details]
        FROM    
            Componentdetails BC 
        INNER JOIN
            Job 
        ON
            Job.BadgeNo = BC.BadgeNo
        INNER JOIN
            Employee
        ON
            Employee.BadgeNo = BC.BadgeNo
        LEFT JOIN
            FalconUsers F1
        ON
            F1.EmployeeID = BC.CreatedBy 
        LEFT JOIN
            FalconUsers F2
        ON
            F2.EmployeeID = BC.VerifiedBy 
        WHERE
            Isverified  = 1
        AND
            IsApproved = 0
        AND
            RejectTag = 0
        )A 
        INNER JOIN @LevelList AS B 
        ON a.[Badge No] = B.BadgeNo
select @RetCountValueOUT

change `LEFT` to `INNER` and remove `AND B.BadgeNo IS NULL`. OP wants to filter rows by values in @LevelList — Giorgi Nakeuri, Feb 12 '15 at 05:53
sorry inner join too reacting same as like IN...both are taking 14 sec to load data — jai, Feb 12 '15 at 06:14

How to Optimizing the query, Replacing IN in sql query?

1 Answers1