Wednesday, March 06, 2013 12:07 PM
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.
Wednesday, March 06, 2013 12:10 PMAnswerer
Wednesday, March 06, 2013 12:15 PMModerator
> 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/
Check the execution plan for pinpointing the problem areas.
Kalman Toth Database & OLAP Architect
Paperback / Kindle: Windows Azure SQL Database Programming & Design
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Wednesday, March 06, 2013 12:16 PM
Thursday, March 07, 2013 9:21 AM
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?
Monday, March 11, 2013 3:58 PM
Any answer to the above question, please?
Monday, March 11, 2013 4:06 PM>>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
Monday, March 11, 2013 4:22 PM
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 (#)?
Monday, March 11, 2013 4:34 PM
You can use a Table variable in dynamic SQL, but only if
1) you declare the table variable in the dynamic batch
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.
- Marked As Answer by divvyboy Monday, March 11, 2013 5:03 PM
Monday, March 11, 2013 4:41 PM
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! :)