May 21, 2008 at 9:24 am
Anyone ever have any noticable performance issue using ISNULL? As in ISNULL(@A, '')?
I know there are things to consider, like the amount of data being queried, but all things being equal. . .
May 21, 2008 at 9:34 am
I have never really noticed an issue when using IsNull in the select list, but using any function against a column in a join or where clause can cause performance issues as they often limit the use of indexes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 21, 2008 at 9:37 am
How do they limit the use of Indexes? I've never heard of this before.
May 21, 2008 at 9:44 am
The use of functions against column values in the WHERE clause tends to force the optimizer to have to use index SCANS instead of SEEKS, since the function has to be evaluated for each row, serially. This can seriously slow down queries, so be careful with their use.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 21, 2008 at 9:51 am
In this query:
[font="Courier New"]SELECT
ManagerID,
NationalIDNumber
FROM
HumanResources.Employee
WHERE
nationalidnumber = '295847284'
[/font]
You get an index seek.
In this query which looks very similar:
[font="Courier New"]SELECT
ManagerID,
NationalIDNumber
FROM
HumanResources.Employee
WHERE
nationalidnumber = 295847284
[/font]
You get an index scan because the NationalIDNumber is NVarchar so the it is doing an implicit conversion to int. This is because it has so scan all the values in order to convert them and then see if they meet the criteria.
The same thing happens in these 2 queries as well using IsNull:
[font="Courier New"]SELECT
EmployeeID
FROM
HumanResources.Employee
WHERE
ManagerID IS NULL
[/font]
[font="Courier New"]SELECT
EmployeeID
FROM
HumanResources.Employee
WHERE
ISNULL(ManagerID, '') = ''
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 21, 2008 at 9:55 am
Holy crap I never thought of it that way. Many thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply