none
Performance

    Question

  • Hi,

    I have a query using number of and conditions , i need some help in writing the AND conditions so it improves the performance .

    SELECT  *
    			
    	FROM	P WITH ( NOLOCK )
    			INNER JOIN  R WITH ( NOLOCK ) 
    					ON R.Id = P.Id
    					-- AND R.Result = 0  (i am trying this here instead at the bottom where)
    			LEFT OUTER JOIN  ME WITH ( NOLOCK ) 
    					ON ME.Id = p.Id
    					AND ME.Id = R.Id
    			INNER JOIN  PM WITH ( NOLOCK ) 
    					ON P.Id = PM.Id
    			INNER JOIN  M WITH ( NOLOCK ) 
    					ON P.Id = M.Id
    			INNER JOIN SM WITH ( NOLOCK ) 
    					ON SM.Pk = P.Pk
    					AND R.Id = SM.Id
    			LEFT OUTER JOIN B WITH ( NOLOCK ) 
    					ON P.Id = B.Id
    			LEFT OUTER JOIN  CLE WITH ( NOLOCK ) 
    					ON P.ID = CLE.ID
    			LEFT OUTER JOIN  RI WITH ( NOLOCK ) 
    					ON P.ID = RI.Id
    	WHERE	R.Result = 0
    	AND P.Pk NOT IN	(SELECT	p.Pk
    					WHERE	R.Name = 'MS'
    					AND		p.Type = 'MO' )
    	AND P.Pk NOT IN (SELECT	p.Pk
    					WHERE	R.Name = 'Mad'
    					AND		P.Name = 'POSITIONS'
    					AND		Inven = 'WHAT_LOAN' )
    	AND P.Name NOT IN ( 'Pipe', 'Black' )
    	AND NOT ( P.Name = 'BRS'
    			 AND ISNULL(Treatment, 'Bank') = 'Bank')
    	AND P.Pk NOT IN (SELECT	p.Pk
    					WHERE	P.Name IN ( 'Credit', 'WFC' )
    					AND		PM.type = 'C'
    					AND		ISNULL(P.summary, 0) = 0 )
    	AND P.PK NOT IN (SELECT	p.Pk
    					FROM	 SS
    				WHERE	R.Name = SS.Name
    				AND P.Name = SS.Name
    				AND ISNULL(P.Type, '') = ISNULL(SS.type, '')
    				AND ISNULL(P.Type, '') = ISNULL(SS.type,'')
    				AND p.Name NOT IN ( 'BRS',
    									'io',)
    				AND P.Type NOT IN ( 'CDap', 'DS') 
    						)
    	AND P.Pk NOT IN (SELECT	p.Pk
    					WHERE	R.Name = 'MA' )

    Please advice
    Saturday, July 19, 2014 4:26 PM

Answers

  • not sure.. both the queries looks like they are doing the same. please give us some sample data..

    I tired with the below sample

    declare @P Table(id int,pk varchar(20),Type char(2))
    declare @R Table(id int,name varchar(20),date date)
    Insert into @P(ID,pk,Type)
    Values (1,'s','O'),
    (2,'s','O'),
    (3,'p','O'),
    (4,'p','i')
    
    Insert into @R
    Values (1,'q','07/16/2014'),
    (2,'s','07/16/2014'),
    (3,'p','07/16/2014'),
    (4,'s','07/16/2014')
    
    SELECT count(*) FROM  @P P
    INNER JOIN  @R R 
    ON R.Id = P.Id
    where P.Pk not in (
    		SELECT	p.Pk
    		WHERE	R.Name = 'S'
    		AND p.Type = 'O' )
    	and R.date = '07/16/2014'	
    		
    SELECT count(*) FROM  @P P
    INNER JOIN  @R R  ON R.Id = P.Id
    where  NOT (R.Name = 'S' AND p.Type = 'O')
    and R.date = '07/16/2014'
    



    Hope it Helps!!

    Sunday, July 20, 2014 2:34 AM
  • you probably have null values  either in R.name column or P.type column...thats why the counts are different..

    I added not null condition.. check the code

    declare @P Table(id int,pk varchar(20),Type char(2))
    declare @R Table(id int,name varchar(20),date date)
    Insert into @P(ID,pk,Type)
    Values (1,'s','O'),
    (1,NULL,'O')
    
    Insert into @R
    Values (1,'q','07/16/2014'),
    (1,'s','07/16/2014'),
    (1,NULL,'07/16/2014')
    
    SELECT R.*,P.* FROM  @P P
    INNER JOIN  @R R 
    ON R.Id = P.Id
    where P.Pk not in (
    		SELECT	p.Pk
    		WHERE	R.Name = 'S'
    		AND p.Type = 'O')
    	and R.date = '07/16/2014' and R.Name is not null
    		
    SELECT R.*,P.* FROM  @P P
    INNER JOIN  @R R  ON R.Id = P.Id
    where  NOT (R.Name = 'S' AND p.Type = 'O')
    and R.date = '07/16/2014'
    


    Hope it Helps!!

    Sunday, July 20, 2014 3:11 AM

All replies

  • I have a query using number of and conditions , i need some help in writing the AND conditions so it improves the performance .

    Instead of AND ISNULL(Treatment, 'Bank') = 'Bank'), just specify Treatment IS NULL (same with P.summary).  This will allow an index on Treatment to be used.  Performance depends much on the available indexes.  Please post your existing CREATE TABE statements, along with constraints and indexes.

    Do you really need all columns from all of the tables?  Consider an explicit column list instead of "SELECT *" so that SQL Server has more options for the optimal query plan.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, July 19, 2014 5:05 PM
  • yes, i am using only the columns i needed. i am not using Select *.

    Any thing i can do for the AND conditions to improve the code?

    AND ISNULL(Treatment, 'Bank') = 'Bank') ; instead of this you want me to try AND Treatment = 'Bank'?

    i will check the create table and indexes.

    Saturday, July 19, 2014 5:17 PM
  • No, Dan said just to use "Treatment is null"

    Treatment is null or ISNULL(Treatment, 'Bank') = 'Bank') , you are doing the same comparsion but the first one is more efficient

    he explained the reason as well above...


    Hope it Helps!!

    Saturday, July 19, 2014 5:29 PM
  • yes, i understand the ISNULL now, thanks

    just thinking about the AND conditions , there are many conditions based on different names and conditions.

    Is there any other way of writing those AND conditions?

    Saturday, July 19, 2014 5:41 PM
  • Below is an untested example of how you might rewrite the AND conditions.  I can't say this will perform better.  Useful indexes and sargable expressions are the key to performance.

    	WHERE	R.Result = 0
    		AND NOT (R.Name = 'MS' AND p.Type = 'MO')
    		AND NOT (R.Name = 'Mad' AND	P.Name = 'POSITIONS' AND Inven = 'WHAT_LOAN')
    		AND P.Name NOT IN ( 'Pipe', 'Black' )
    		AND NOT (P.Name = 'BRS' AND Treatment IS NULL)
    		AND NOT (P.Name IN ( 'Credit', 'WFC' ) AND PM.type = 'C' AND P.summary IS NULL)
    		AND P.PK NOT IN (
    			SELECT	p.Pk
    			FROM	 SS
    			WHERE	R.Name = SS.Name
    				AND P.Name = SS.Name
    				AND ISNULL(P.Type, '') = ISNULL(SS.type, '')
    				AND ISNULL(P.Type, '') = ISNULL(SS.type,'')
    				AND p.Name NOT IN ( 'BRS', 'io' )
    				AND P.Type NOT IN ( 'CDap', 'DS') 
    				AND R.Name <> 'MA'


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, July 19, 2014 6:04 PM
  • Thanks Dan,

    will verify.

    Saturday, July 19, 2014 6:34 PM
  • Too many hard-wired values suggest database design issues.

    Instead of hard-wiring literals into queries, look-up tables should be used.

    Meanwhile take a look at the execution plan for quick improvement opportunities.

    WHERE	R.Result = 0
    		AND NOT (R.Name = 'MS' AND p.Type = 'MO')
    		AND NOT (R.Name = 'Mad' AND	P.Name = 'POSITIONS' AND Inven = 'WHAT_LOAN')
    		AND P.Name NOT IN ( 'Pipe', 'Black' )
    		AND NOT (P.Name = 'BRS' AND Treatment IS NULL)
    		AND NOT (P.Name IN ( 'Credit', 'WFC' ) AND PM.type = 'C' AND P.summary IS NULL)
    		AND P.PK NOT IN (
    			SELECT	p.Pk
    			FROM	 SS
    			WHERE	R.Name = SS.Name
    				AND P.Name = SS.Name
    				AND ISNULL(P.Type, '') = ISNULL(SS.type, '')
    				AND ISNULL(P.Type, '') = ISNULL(SS.type,'')
    				AND p.Name NOT IN ( 'BRS', 'io' )
    				AND P.Type NOT IN ( 'CDap', 'DS') 
    				AND R.Name <> 'MA'

    Optimization:  http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012










    Saturday, July 19, 2014 7:23 PM
    Moderator
  • SELECT COUNT(*) FROM  P
    INNER JOIN  R WITH ( NOLOCK ) 
    ON R.Id = P.Id
    where P.Pk NOT IN (
    		SELECT	p.Pk
    		WHERE	R.Name = 'S'
    		AND p.Type = 'O' )
    and R.Date = '7/16/2014'			
    		
    		
    		
    SELECT COUNT(*) FROM  P
    INNER JOIN  R WITH ( NOLOCK ) ON R.Id = P.Id
    where  NOT (R.Name = 'S' AND p.Type = 'O')
    and R.Date = '7/16/2014'	

    These both not returning the same number of rows, am i missing something?

    I also checked execution plan and there no Index suggestions and everything is Index scan no tables scan exists

    • Edited by samantha256 Saturday, July 19, 2014 11:28 PM spell
    Saturday, July 19, 2014 11:10 PM
  • not sure.. both the queries looks like they are doing the same. please give us some sample data..

    I tired with the below sample

    declare @P Table(id int,pk varchar(20),Type char(2))
    declare @R Table(id int,name varchar(20),date date)
    Insert into @P(ID,pk,Type)
    Values (1,'s','O'),
    (2,'s','O'),
    (3,'p','O'),
    (4,'p','i')
    
    Insert into @R
    Values (1,'q','07/16/2014'),
    (2,'s','07/16/2014'),
    (3,'p','07/16/2014'),
    (4,'s','07/16/2014')
    
    SELECT count(*) FROM  @P P
    INNER JOIN  @R R 
    ON R.Id = P.Id
    where P.Pk not in (
    		SELECT	p.Pk
    		WHERE	R.Name = 'S'
    		AND p.Type = 'O' )
    	and R.date = '07/16/2014'	
    		
    SELECT count(*) FROM  @P P
    INNER JOIN  @R R  ON R.Id = P.Id
    where  NOT (R.Name = 'S' AND p.Type = 'O')
    and R.date = '07/16/2014'
    



    Hope it Helps!!

    Sunday, July 20, 2014 2:34 AM
  • you probably have null values  either in R.name column or P.type column...thats why the counts are different..

    I added not null condition.. check the code

    declare @P Table(id int,pk varchar(20),Type char(2))
    declare @R Table(id int,name varchar(20),date date)
    Insert into @P(ID,pk,Type)
    Values (1,'s','O'),
    (1,NULL,'O')
    
    Insert into @R
    Values (1,'q','07/16/2014'),
    (1,'s','07/16/2014'),
    (1,NULL,'07/16/2014')
    
    SELECT R.*,P.* FROM  @P P
    INNER JOIN  @R R 
    ON R.Id = P.Id
    where P.Pk not in (
    		SELECT	p.Pk
    		WHERE	R.Name = 'S'
    		AND p.Type = 'O')
    	and R.date = '07/16/2014' and R.Name is not null
    		
    SELECT R.*,P.* FROM  @P P
    INNER JOIN  @R R  ON R.Id = P.Id
    where  NOT (R.Name = 'S' AND p.Type = 'O')
    and R.date = '07/16/2014'
    


    Hope it Helps!!

    Sunday, July 20, 2014 3:11 AM