none
Alternative for OR in WHERE clause

    Question

  • Hi.

    I have a simple SELECT query:

    select 
    	P.ID
    	,P.PolicyNo 
    	,P.status 
    	,P.HolderName 
    	,P.InsurerName 
    	,P.BrokerName 
    	,P.CAAClientName 
    	,P.dtStart 
    	,P.dtEnd
    	from [Policy].[vPolicyList2] P 
    	where 
    	(P.HolderName like '%' + @Find + '%') 
    	OR 
    	(P.PolicyNo like '%' + @Find + '%')

    The issue I have is with the WHERE clause.  If I run one WHERE on it's own (either of them), the result set returns nigh on instantly.  However, when both are executed with the OR, It takes around 5 seconds to execute with simple string criteria.

    Are there any alternatives to the WHERE clause on how I am extracting the data?  Keep in mind that I need to keep the wildcard search.

    I have also checked indexes on the associated tables - all are running fine.

    Thanks in advance.

    Friday, July 26, 2013 1:33 PM

Answers

  • See if you get any performance benefits in using UNION ALL ?

    select P.ID ,P.PolicyNo ,P.status ,P.HolderName ,P.InsurerName ,P.BrokerName ,P.CAAClientName ,P.dtStart ,P.dtEnd from [Policy].[vPolicyList2] P where (P.HolderName like '%' + @Find + '%')

    UNION ALL

    select P.ID ,P.PolicyNo ,P.status ,P.HolderName ,P.InsurerName ,P.BrokerName ,P.CAAClientName ,P.dtStart ,P.dtEnd from [Policy].[vPolicyList2] P where (P.PolicyNo like '%' + @Find + '%')



    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Prajesh Friday, July 26, 2013 1:38 PM better
    • Proposed as answer by Prajesh Friday, July 26, 2013 5:45 PM
    • Marked as answer by warnerrj79 Thursday, August 01, 2013 8:06 AM
    Friday, July 26, 2013 1:37 PM

All replies

  • See if you get any performance benefits in using UNION ALL ?

    select P.ID ,P.PolicyNo ,P.status ,P.HolderName ,P.InsurerName ,P.BrokerName ,P.CAAClientName ,P.dtStart ,P.dtEnd from [Policy].[vPolicyList2] P where (P.HolderName like '%' + @Find + '%')

    UNION ALL

    select P.ID ,P.PolicyNo ,P.status ,P.HolderName ,P.InsurerName ,P.BrokerName ,P.CAAClientName ,P.dtStart ,P.dtEnd from [Policy].[vPolicyList2] P where (P.PolicyNo like '%' + @Find + '%')



    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Prajesh Friday, July 26, 2013 1:38 PM better
    • Proposed as answer by Prajesh Friday, July 26, 2013 5:45 PM
    • Marked as answer by warnerrj79 Thursday, August 01, 2013 8:06 AM
    Friday, July 26, 2013 1:37 PM
  • >See ifyou get anyperformance benefits in using UNION ALL ?

    I doubt that.

    Optimization article:

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


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Friday, July 26, 2013 2:05 PM
    Moderator
  • Are you selecting from a view? What does the view look like?

    Tom G.

    Friday, July 26, 2013 2:21 PM
  • >See ifyou get anyperformance benefits in using UNION ALL ?

    I doubt that.

    Optimization article:

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


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Kalman

    Prajesh's suggestion has cut the execution time on my query considerably.

    How does your link affect my query please?  I have already had a look at my system and points 1, 4, 5 and 9 are already fulfilled - with points 2, 3, 6, 7, 8 and 10 not applicable (from my perspective anyway).

    Thanks.


    Friday, July 26, 2013 2:21 PM
  • Are you selecting from a view? What does the view look like?

    Tom G.

    Hi.

    View looks like this and runs very quickly:

    CREATE VIEW [Policy].[vPolicyList]
    
    AS
    
    	SELECT 
    	P.[ID]
    	,P.[GUID]
    	,[PolicyNo]
    	,P.[Name]
    	,[HolderID]
    	,[CAAClientID]
    	,[CAADivisionID]
    	,CH.Name as 'HolderName'
    	,CI.Name as 'InsurerName'
    	,CB.Name as 'BrokerName'
    	,CC.Name as 'CAAClientName'
    	,P.dStart as 'dtStart'
    	,P.dEnd as 'dtEnd'
    	,case when (P.dEnd < getdate()) then 'valid' else 'expired' end as 'status'
    	FROM [dbo].[Policy] P WITH (NOLOCK)
    	inner join dbo.Company CH WITH (NOLOCK) on CH.ID = P.HolderID 
    	inner join dbo.Company CI WITH (NOLOCK) on CI.ID = P.InsurerID 
    	left join dbo.Company CB WITH (NOLOCK) on CB.ID = P.IntermediaryID 
    	left join dbo.Company CC WITH (NOLOCK) on CC.ID = P.CAAClientID 

    Thanks,

    Friday, July 26, 2013 2:26 PM
  • View seems very good from join condition prospective, only thing that can be enhanced here is to create a computed persistent INT version of for the column dEnd and dStart (if needed to compare). Rest seems perfectly fine. SQL server performs best on numeric joins and comparisons.

    ,case when (P.dEndInt < convert(int,getdate())) then 'valid' else 'expired' end as 'status'


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, July 26, 2013 2:36 PM
  • Prajesh's suggestion has cut the execution time on my query considerably.

    Hard to believe; with that filter condition you will always have a full table scan, and with the UNION ALL you may will have 2 parallel table scan. Sure it's not the cache of your previous queries?

    If you just want to void the OR (I don't understand why), you could concatenate both fields and filter on this

    select 
    	P.ID
    	,P.PolicyNo 
    	,P.status 
    	,P.HolderName 
    	,P.InsurerName 
    	,P.BrokerName 
    	,P.CAAClientName 
    	,P.dtStart 
    	,P.dtEnd
    from [Policy].[vPolicyList2] P 
    where (P.HolderName + P.PolicyNo like '%' + @Find + '%') 


    Olaf Helper

    Blog Xing

    Friday, July 26, 2013 2:41 PM
  • Please note that Prajesh's suggestion in some cases will return a different result than your original query.  If you have a row where P.HolderName like '%' + @Find + '%' and P.PolicyNo like '%' + @Find + '%' are both true, then your original query will return that row once, but Prajesh's query will return it twice.

    That may not be important to you.  Maybe you know that both those conditions will never be true, or maybe you don't care if a row comes back twice.  But if it is important, then Prajesh's suggestion may be problematic for you.

    If you have this problem, you may be able to avoid it by changing the UNION ALL in Prajesh's query to UNION.  However, if you do that, it will force a sort on the result so that SQL can find and remove any duplicates.  And that sort may make Prajesh's query as expensive as your original query.

    Tom

    Friday, July 26, 2013 2:45 PM
  • You need to test the quality of the result and performance and find the best option for you, if this is just a few seconds difference, I would recommend keep the OR as it simplifies the query, UNION/UNION ALL makes it bigger. 

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, July 26, 2013 2:50 PM
  • but Prajesh's query will return it twice.


    Easy to solve by using an UNION instead of an UNION ALL; but this will cause an additional SORT operation, which will decrease perfomance

    Olaf Helper

    Blog Xing

    Friday, July 26, 2013 2:57 PM
  • Prajesh's suggestion has cut the execution time on my query considerably.

    Hard to believe; with that filter condition you will always have a full table scan, and with the UNION ALL you may will have 2 parallel table scan. Sure it's not the cache of your previous queries?

    If you just want to void the OR (I don't understand why), you could concatenate both fields and filter on this

    select 
    	P.ID
    	,P.PolicyNo 
    	,P.status 
    	,P.HolderName 
    	,P.InsurerName 
    	,P.BrokerName 
    	,P.CAAClientName 
    	,P.dtStart 
    	,P.dtEnd
    from [Policy].[vPolicyList2] P 
    where (P.HolderName + P.PolicyNo like '%' + @Find + '%') 


    Olaf Helper

    Blog Xing

    Hi Olaf - thanks for your reply.

    I have just tried this and although it's faster than my original query, it's still not as quick as Prajesh's suggestion.

    Thanks again though :)

    Friday, July 26, 2013 2:58 PM
  • You need to be careful with this query as well.  It can return rows that your original query did not return.  For example if your HolderName is "ROBERTO" and your PolicyNo is "M13579" and @Find = "TOM", this query will return that row.

    If you have a character that you know is never in @Find (for example, suppose you know @Find never has an "$", then you can avoid this problem with

    where (P.HolderName + '$' + P.PolicyNo like '%' + @Find + '%') 

    Tom

    Friday, July 26, 2013 3:12 PM
  • Hi divvyboy,

    The fact that you are selecting from a view (and not from a table) is crucial information that changes what every said prior to this.

    You are filtering on two columns, and the view shows that one of these columns is from table Company, and the other is from Policy.

    In the ideal situation, both these columns are indexed, preferably with a covering index. Can you list all the indexes that include Company.Name and Policy.PolicyNo? And can you tell what clustered index is on Policy and on Company?

    In that ideal situation, the optimizer has the maximum choice of query plans and could select the optimal one.

    Prajesh first suggestion (two queries concatenated with UNION ALL) is a good suggestion, because it allows an "index seek" plan on Name for the first predicate and on PolicyNo for the second predicate, in such a way that the optimizer might not figure out for one big query.

    Also, you'd want to make sure that String Summary Statistics are available in the index (see Like '%foo%').


    Gert-Jan

    Friday, July 26, 2013 4:18 PM