In situations where a varchar field could get contaminated with empty strings, does it make any difference from a performance point of view whether you go for:

... WHERE fieldname is not null and fieldname !=''  

OR

... WHERE isnull(field,'') !=''   

OR

... something better maybe  

As far as index usage (asuming there is an index on fieldname), maybe the first is better especially as there's no function call involved - or does it all depend on the data? Presumably the ISNULL() isn't sargable, but then neither is IS NOT NULL I think?

This is often a situation where there are a lot of nulls in fieldname (say 50%) and the possibility of a few empty strings.

asked 20 Apr '10, 19:04

Justin%20Willey's gravatar image

Justin Willey
6.8k110144212
accept rate: 20%


You are correct that IS NOT NULL is not currently sargable, nor is the ISNULL() function. ISNULL is worse than IS [NOT] NULL in that its selectivity estimate is a raw guess, whereas for IS [NOT] NULL the server will utilize the column's histogram for an accurate estimate.

Since neither IS NOT NULL nor ISNULL() is sargable, to a significant extent there is no advantage in performance of one over the other. However, because of better estimates, using the conjunction

WHERE fieldname is not null and fieldname !='' 

may help with cardinality estimation if the query contains additional joins, which may well yield a better plan.

permanent link

answered 21 Apr '10, 00:36

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

Many thanks Glenn - that's very clear

(21 Apr '10, 11:00) Justin Willey
2

@Glenn: You say "not currently sargable" - what are the plans to change this behaviour? Is it planned for Innsbruck?

(22 Apr '10, 10:23) Volker Barth
1

We are looking at making IS NOT NULL sargable. No promises as to when that functionality will be added.

(22 Apr '10, 19:09) Glenn Paulley

very interesting.... I have an application that looks for null values in column (which is indexed), and processes those records accordingly, and in so doing, it UPDATEs the NULLs with values. If I understand what 'sargable' means, it means that regardless of the index, that my table is going to be read sequentially looking for records with a NULL value.

Since looking for records with IS NULL is not sargeable, It seems to me that the default value for my column (currently NULL) should really be an empty string, so that the db engine can find those records quickly, and therefore my application responds faster.

Am I right here?

(27 Apr '11, 21:28) Ron Hiner
Replies hidden
2

As to the docs, IS NULL is sargable - and has been with v8 (and possibly even before). So I do not think switching to empty strings would be faster in your situation.

For 10.0.1, cf. the docs.

(28 Apr '11, 03:23) Volker Barth
2

@Glenn: As "IsNull(AField, '')" is so much easier to write and read than "(if AField is null then '' else AField endif)" is there a possibility for the query engine to recognise IsNull(...) and rewrite it to the longer form so that IsNull is able to take advantage of the IS [NOT] NULL sargability?

(28 Apr '11, 19:45) Nick Brooks
1

IS NULL has been a sargable predicate since at least Watcom SQL 3.2.

(29 Apr '11, 09:13) Glenn Paulley

...that's even before you got your hands on the product, right? :)

(29 Apr '11, 09:20) Volker Barth
showing 5 of 8 show all flat view

IS NOT NULL was made sargable in 12.0.1.

permanent link

answered 27 Apr '11, 22:49

John%20Smirnios's gravatar image

John Smirnios
8.9k377112
accept rate: 39%

edited 28 Apr '11, 09:46

Volker%20Barth's gravatar image

Volker Barth
30.9k311457668

That's good news - is there any reason this is not documented as a behaviour change (unless I have overseen this)?

(28 Apr '11, 03:15) Volker Barth
Replies hidden

IS NOT NULL is listed as a sargable predicate in

SQL Anywhere Server - SQL Usage » Query optimization and execution » How the optimizer works » Using predicates in queries

However, that change does not appear to have made it to the list of behaviour changes in the "What's new" section for the 12.0.1 release.

(28 Apr '11, 11:27) Glenn Paulley

Yes, that's the doc page John has linked to:) - Something to add to the DCX "What's new" page?

(28 Apr '11, 11:58) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×242
×20
×5

question asked: 20 Apr '10, 19:04

question was seen: 1,634 times

last updated: 29 Apr '11, 09:21