x

Query Performance

Hi The below query as i am using , it fast or slow for performace , The "Resident_First_Name" column have allow null column

select Resident_First_Name from RC_RESIDENT where ISNULL(Resident_First_Name,'') like '%%'

in where clause isnull() is reduce any performance.
more ▼

asked Dec 28, 2011 at 11:08 PM in Default

Karthik M gravatar image

Karthik M
20 2 3 5

Why do you need the ISNULL()?
Dec 28, 2011 at 11:36 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Using ISNULL (or any function) around the field value in the WHERE clause will force a table or index scan and will have to scan all records. If you are expecting this table to never contain more than a few records, (or if you expect a table scan for other reasons) then don't worry. Otherwise you need to find a different way of expressing the query.

The %% you have in your example, is this indicating that you intend to use a pattern along the lines of %p% as your search critiera? If so then this will also always cause a complete table or index scan and should be avoided.

Why do you think you need the ISNULL? Do you really want NULL or Blank names to be returned?
more ▼

answered Dec 29, 2011 at 12:36 AM

pipthegeek gravatar image

pipthegeek
329 1 2 5

You're not sure to get a table scan. You're more likely to get a table scan, but in many cases an index scan will be issued instead.

Same goes with LIKE '%pattern%' - more likely to get a table scan, but often an index scan.

But ISNULL in combination with LIKE '%pattern%' seems to be a killer and SQL Server rarely uses the index at all.
Dec 29, 2011 at 01:18 AM Magnus Ahlkvist
@Magnus - I should have just said "scan" really. I have changed my answer to be clearer though.
Dec 29, 2011 at 07:31 AM pipthegeek
(comments are locked)
10|1200 characters needed characters left

Like both @pipthegeek and @Pavel Pawlowski pointed out - your query will return all rows because LIKE '%%' isn't excluding anything but NULL, and ISNULL takes care of those.

But apart from that: It can't be said if your query is efficient or not.

ISNULL(col,'value') LIKE '%pattern%' 

can be reconstructed as:

col LIKE '%pattern%' OR (col IS NULL AND 'value' LIKE '%pattern%')

That's more likely to use an index on column col

LIKE '%pattern%'

is inefficient, but if sometimes that's the search criteria you want.

So your answer is: It depends on your data and your requirements. My only general advice is that you probably want to reconstruct ISNULL.
more ▼

answered Dec 29, 2011 at 01:28 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left

The WHERE condition which is written as you have it in the question will return all rows so you do not need it at all.

If you are using different patterns in the LIKE clause then I would try to remove the ISNULL from the WHERE to make the condition sargable.

WHERE Resident_First_Name LIKE 'pattern' OR ResidentFirstName IS NULL if you want also return fields with NULL values
WHERE Resident_First_Name LIKE 'pattern' in case you do not want the NULLs to be returned.

Aslso as @pipthegeek mentioned, try to avoid % wildcard on the beginning of the LIKE pattern if possible as it will utilize an INDEX or TABLE SCAN. When the % is used only at the end of the pattern then you will probably receive a better query plan as probably INDEX seek will be done if there is an index on the Resident_First_Name field.

Anyway if you do not have an index on that field, a table scan (or eventual other covering index scan) will be always done by such query.
more ▼

answered Dec 29, 2011 at 12:53 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

I would like to give your question answer that Using ISNULL in where clause will decrease the SQL query performance because If you apply a function to a column used in the WHERE clause of your SQL statement, it is unlikely that the SQL statement will be able to make use of any indexes applied to that column so do not apply any function to a column used in the WHERE clause.

If you can post your expected requirement of SQL query result with example of table data then I could help you to make SQL with more efficiency way.
more ▼

answered Dec 29, 2011 at 01:12 AM

Amardeep gravatar image

Amardeep
1.3k 87 88 89

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986

asked: Dec 28, 2011 at 11:08 PM

Seen: 580 times

Last Updated: Dec 28, 2011 at 11:11 PM