Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Query tuning - a couple of pointers needed.

Respondida Query tuning - a couple of pointers needed.

  • Wednesday, March 06, 2013 12:07 PM
     
      Has Code
    Sign In to Vote
    0

    Hi all

    I am in the process of tuning one of our legacy search queries.  It's contents are as follows:

    select 
N.GUID as 'NotificationGUID'
,N.ID as 'NotificationID'
,N.CAARef as 'Ref'
,V.RegNo as 'RegNo'
,case when N.KeeperIsDriver = 1 then
	isnull(N.vKeeperName, 'unknown')
else
	isnull(N.vDriverName , 'unknown')
end as 'Driver'
,(Select Name from Company where ID = P.HolderID) as 'Client'
,case when N.[Status] = 1 
 then 'open' 
 else 
	case when N.[Status] = 2 then 
		'closed'
	else 
		'reopened'
	end
 end as 'Status'
,P.PolicyNo as 'PolicyNo'
,N.dCreated as 'dtNotified'
,N.dIncident as 'dtIncident'
from Notification N
inner join Policy P on P.ID = N.PolicyID
inner join Vehicle V on V.ID = N.VehicleID 
Where 1=1
and (
		@UserCompanyID = 391
	Or
		P.HolderID in (select CompanyID from @PC)
	Or
		P.InsurerID in (select CompanyID from @PC)
	Or
		P.CAAClientID in (select CompanyID from @PC)
	Or 
		P.IntermediaryID in (select CompanyID from @PC)
	Or 
		P.ID in (select PolicyID from @PP)
	)
and (N.CAARef like @CAARef + '%' or @CAARef is Null)
and (N.[Status] = @Status or @Status is NULL)
and ((N.ID in (select ID from @VNIDS) and @RegNo is Not NULL) OR @RegNo IS NULL)
and ((N.ID in (select ID from @DNIDS) and @DriverName is not NULL) or @DriverName is NULL)
and (P.PolicyNo like '%' + @PolicyNo + '%' or @PolicyNo is NULL)
and (N.dCreated >= @dtNotificationStart or @dtNotificationStart is NULL)
and (N.dCreated <= @dtNotificationEnd or @dtNotificationEnd is NULL)
and (N.dIncident >= @dtIncidentStart or @dtIncidentStart is NULL)
and (N.dIncident <= @dtIncidentEnd or @dtIncidentEnd is NULL)
and (N.ID in 
		(
			select distinct R.NotificationID 
			from RepairNotification RN
			inner join Repair R on R.RepairNotificationID = RN.ID
			where RN.ClaimNo like @InsurerRef + '%'
		) or @InsurerRef is NULL)
and (P.HolderID = @HolderID or @HolderID is NULL)

    As you can see, it uses a lot of variable inputs in the where clause and it takes any combination of these variables whether they're occupied or not at the time of search.  The where clause also contains two sub-queries where it looks at table variables (@VNIDS and @DNIDS).  These temp tables contain previously inserted values from three different data sources.

    After a lot of playing with this query, I have done at least 2 things to make it faster:

    1. Remove the WHERE sub-selects and refer to them in a LEFT OUTER JOIN

    2. Remove the (OR IS NULL) statements in the WHERE clause - this had a major performance increase.

    My initial question is, is there any way I can allow for nulls in the search criteria other than the method above?  During searches using this statement, any combination of variables will be occupied or nulled and both values have to be allowed for.

    Another question is in the last sub select (N.ID IN (SELECT DISTINCT R.NOTIFICATIONID etc....), would this be better in FROM section of the statement?  Is there another way I can accomplish this?

    I have also tried chucking all of this into a dynamic sql string using IF statements where a variable is occupied - however ive come across issues where my variables (including table variables) are declared outside of the sql string - ie must declare scalar variable ??? errors.

    Any help with this would be greatly appreciated.

    Many thanks.

     

All Replies

  • Wednesday, March 06, 2013 12:10 PM
    Answerer
     
     
    Sign In to Vote
    0
    http://www.sommarskog.se/dyn-search-2008.html

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

     
  • Wednesday, March 06, 2013 12:15 PM
    Moderator
     
     
    Sign In to Vote
    0

    > however ive come across issues where my variables (including table variables) are declared outside of the sql string - ie must declare scalar variable ??? errors

    With dynamic SQL you need to declare variables with the query (not in the parent program).  #temptable can be created in the parent and used in the child process.

    Place a PRINT @SQL first instead of the execute to make sure that the generated code is valid.

    Dynamic SQL: http://www.sqlusa.com/bestpractices/dynamicsql/

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

    Check the execution plan for pinpointing the problem areas.

    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Windows Azure SQL Database Programming & Design


     
  • Thursday, March 07, 2013 9:21 AM
     
     
    Sign In to Vote
    0

    Hi Gents - thanks to you both for your responses.

    Can I use table variables using Dynamic SQL (declaring the columns in the paramlist) or would a temp table using "#" be more efficient for this process?

    Thanks,

     
  • Monday, March 11, 2013 3:58 PM
     
     
    Sign In to Vote
    0

    Hi.

    Any answer to the above question, please?

    Thanks.

     
  • Monday, March 11, 2013 4:06 PM
     
     Answered
    Sign In to Vote
    0
    >>2. Remove the (OR IS NULL) statements in the WHERE clause - this had a major performance increase.

    This strongly suggests that you should use either dynamic SQL or OPTION RECOMPILE.  OPTION RECOMPILE is vastly easier to implement.

    David

    David http://blogs.msdn.com/b/dbrowne/

     
  • Monday, March 11, 2013 4:22 PM
     
     
    Sign In to Vote
    0

    Hi David - thank for your reply.

    I have implemented OPTION RECOMPILE and although this has improved the query speed, I still do not think it is efficient.

    With regard to the dynamic SQL question, can I use table variables (@) or must I use temp tables (#)?

    Thanks.

     
  • Monday, March 11, 2013 4:34 PM
     
     Answered
    Sign In to Vote
    0

    You can use a Table variable in dynamic SQL, but only if

    1) you declare the table variable in the dynamic batch

    or

    2) You define a User Defined Table Type and pass the table variable using a parameter

    .  A temporary table defined in your stored procedure will automatically be visible from inside the dynamic SQL executed by the stored procedure, and has the added benefit supporting indexes and statistics for better query plans. 

    So there's really no benefit of using a Table Variable if you're doing dynamic SQL.

    David

    David http://blogs.msdn.com/b/dbrowne/

    • Marked As Answer by divvyboy Monday, March 11, 2013 5:03 PM
    •  
     
  • Monday, March 11, 2013 4:41 PM
     
     
    Sign In to Vote
    0

    I need the temp table to be available outside the batch so I think that'll be the way to go.

    Thanks for your help! :)