July 2, 2018 at 2:14 pm
Dear All,
I have a query which has ISNull in my where condition, I'm aware that it henders the performance because 'scan' is performed in order to retrieve the data (regardless of index being implemented); I want to find out if there is another way to have an efficient way.
Thank you in advance!
July 2, 2018 at 2:58 pm
We could give a more specific answer if you'd show the actual code.
But, for example, if it's something like this:
WHERE ISNULL(date_column, '19000101') >= '20170101'
then you don't need the ISNULL at all:
WHERE date_column >= '20170101'
will produce exactly the same results.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 3, 2018 at 1:55 am
thank you for your reply!
The script is something like:
CREATE PROCEDURE employeeinfo
AS
emaployeeid INT = NULL
select employeename, id FROM Employee
where isnull(@emaployeeid,0) = isnull(employee.cgId,0)
July 3, 2018 at 2:04 am
Using a function on a column generally makes the query non-SARGable. This means any indexes you have on that table/column cannot be used. If you need to check whether a column has a specific value, or has the value NULL, then you should use an OR:
WHERE (YourColumn = @InputParameter
OR YourColumn IS NULL)
AND SomeOtherColumn...;
For your query you should be doing:WHERE (employee.cgId = @emaployeeid
OR (employee.cgId IS NULL AND @emaployeeid IS NULL))
This, however, has a bit more of the look of a "catch-all" query, which suffer from performance issues too.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 3, 2018 at 7:49 am
Do you actually have NULL employee ids? That's rather bizarre.
If you do, use Thom's code. If not, all you need to write is:
where employee.cgId = @employeeid
If not, and you meant the code to select all employees when the @employeeid value is null or zero, then this:
where (@employeeid IS NULL OR @employeeid = 0 OR employee.cgId = @employeeid)
Besides being more accurate, it's much more straightforward to read and understand (at least to me).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply