|
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: OR OR 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. |
|
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 may help with cardinality estimation if the query contains additional joins, which may well yield a better plan. Many thanks Glenn - that's very clear 2
@Glenn: You say "not currently sargable" - what are the plans to change this behaviour? Is it planned for Innsbruck? 1
We are looking at making IS NOT NULL sargable. No promises as to when that functionality will be added. 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
|
|
IS NOT NULL was made sargable in 12.0.1. 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
|
