1

I am writing a parameterised query where there is a requirement for the LIKE clause to include NULL values when field LIKE '%'.

I have multiple solutions; I ran these queries side-by-side and they return the similar execution plans with the same summary and the query times are similar. I worry about how these queries scale-up in performance for tables with millions of records.

-- Where @PartId is typically '%' or could be 'Part123%'.
SELECT *
FROM dbo.OrderItems AS oi
WHERE oi.PartId IS NULL OR oi.PartId LIKE @PartId

SELECT *
FROM dbo.OrderItems AS oi
WHERE ISNULL(oi.PartId, '') LIKE @PartId

SELECT *
FROM dbo.OrderItems AS oi
WHERE COALESCE(oi.PartId, '') LIKE @PartId

This question suggests that there is very little difference between ISNULL and COALESCE. Grant Fritchey demonstrates in this article that applying functions on WHERE clauses causes a bigger performance hindrance than using LIKE, however I've read that using logical OR can cause performance issues in queries. What is the performance impact with using IS NULL OR vs. the alternative of ISNULL?

Edit: This is the execution plan summary for the queries: enter image description here

dkmann
  • 611
  • 2
  • 8
  • 18
  • I think the first query doesn't work as desired, as it retursn rows with null value witrhout looking at @CustomerName – Turo Feb 28 '18 at 18:55
  • Surely the plans are the same, as you say with every query 'give me all you have' – Turo Feb 28 '18 at 18:59
  • @Turo I described using `'%'` to include `NULL` values. I've removed `@CustomerName` to clarify my question. – dkmann Feb 28 '18 at 19:02
  • 1
    Those aren't the execution plans, they are just a summary of the plans. Right-click and hit Show XML to see the real plans. By comparing the XML you will discover whether or not they are actually doing the same thing or if they just happen to have the same summary. – Jonathan Allen Feb 28 '18 at 19:07
  • 2
    The logic of your search impacts performance. It is a dumb scan with no chance for anything else to happen. Because of particular data model and search logic. Playing with "ISNULL" under given conditions gives not more effect than changing T-short. – Ivan Starostin Feb 28 '18 at 19:08
  • Are there any indexes on the table? Try adding one on the Name column. – MJH Feb 28 '18 at 19:09
  • @MJH I've added a more concrete example on a much bigger table with indexes. – dkmann Feb 28 '18 at 19:14
  • @JonathanAllen thanks for the correction. The real plans are not completely identical upon inspection. – dkmann Feb 28 '18 at 19:38
  • 1
    Catch-All queries like this are better handled with dynamic sql. https://www.sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ – RBarryYoung Feb 28 '18 at 19:57
  • 1
    You might also get some extra insights by adding a SET STATISTICS TIME, IO ON; and then passing in a few different parameter options to all three queries. – Eric Brandt Feb 28 '18 at 21:04

3 Answers3

2

You didn't paste the plan that was chosen, but I can only assume that it involved a table scan?

Instead of using "WHERE CONDITION1 OR CONDITION2" it is sometimes worth rewriting your SQL to use a union query like so:

SELECT *
FROM dbo.Customers AS c
WHERE c.Name IS NULL 
UNION ALL
SELECT *
FROM dbo.Customers AS c
WHERE c.Name = @CustomerName

This can prevent the table scan.

MJH
  • 1,710
  • 1
  • 9
  • 19
0

Not quite sure about your query logic. Your where clause 

oi.PartId IS NULL OR oi.PartId LIKE '%'

and other two where clauses just literally means "select me records where PartID is null or PartID is NOT null".

Since PartID can only be NULL or not null, why do you need the where clause in the first place?

jyao
  • 1,550
  • 3
  • 19
  • 26
  • 2
    Because `'%'` would be parameterised. Other values could be supplied to the query e.g. `'AB123%'` and would not include `NULL`s. – dkmann Feb 28 '18 at 20:11
0

You tested? Testing to only like '%' is flawed results. If you replace like with = it is still flawed.

These queries return different results!

declare @match varchar(10)  = '%match%';
declare @val varchar(10) = null;

select 'yes' 
where @val is null or @val like @match;

select 'yes'
where isnull(@val, '') like @match;
paparazzo
  • 44,497
  • 23
  • 105
  • 176