July 12, 2020 at 1:42 am
Dear friends,
We used Stored Procedure on our Queries for us to get valuee we Put the conversion of ISNULL inside the WHERE condition but per checking it affects the Process of our strodproc, it is not using indexes, never index seek always table scan.
For example,
SELECT * FROM tblInfo
WHERE fldinfo=ISNULL(@parameter,fldinfo)
What's your recommendation to fix this sentence, because if I remove the isnull function then index seek appears.
I am interested in some documentation that could support me, to educate our developers team, that functions (any function) located in WHERE clause are not god ideas, if you can give me some guidance I will appreciate.
Thanks a lot for your support
July 12, 2020 at 10:01 am
What are you trying to achieve?
Is @parameter an optional parameter? Is tblInfo.fldinfo a NULLable column? Both?
Certain functions on the right side of the equality do not necessarily always impact performance.
For example, I never usually have a problem with
WHERE dateColumn > DATEADD(DAY, -30, GETUTCDATE() )
because the function is only doing one evaluation of the current date to get a value
whereas
WHERE DATEADD(DAY, 30, dateColumn) > GETUTCDATE()
would be a problem.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
July 12, 2020 at 11:45 am
Imo it's easier to express as 2 queries
drop table if exists test_tblinfo;
go
create table test_tblinfo(fldinfo int);
go
insert test_tblinfo values (null), (1);
declare
@parameter int=null;
if @parameter is null
/* fldinfo=fldinfo will only be true when fldinfo is not null */
select * from test_tblinfo where fldinfo is not null
else
select * from test_tblinfo where fldinfo=@parameter;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 12, 2020 at 9:34 pm
Thanks for your responses,
This code is legacy for a client, I am thinking that the original developer tryed to validate their input parameters at the wrong side, fldinfo is a not null value and @parameter is a variable, I am trying the code without the isnull function and this produce an index seek, that's what I want, only now I am interested in some background information that could help me to give a more informed opinion about this for my client.
Thanks again
July 13, 2020 at 1:20 am
SELECT *
FROM tblInfo
WHERE @parameter IS NULL OR fldinfo = @parameter
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 13, 2020 at 8:06 am
Luis
I see Scott beat me to it 🙂
That's what I was going to suggest if you came back and said the parameter was optional. I'm guessing you meant optional rather than variable?
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
July 13, 2020 at 11:24 am
SELECT *
FROM tblInfo
WHERE @parameter IS NULL OR fldinfo = @parameter
Not quite the same as what the OP posted. Here's another way but readability is not so good imo.
SELECT *
FROM test_tblinfo
WHERE fldinfo IS NOT NULL OR fldinfo = @parameter;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 13, 2020 at 12:40 pm
Thanks all for your responses,
My question now is Why? Why does not works as is ? we are suggested to a very strict change control policy
Thanks
July 13, 2020 at 1:09 pm
Luis
You have the same column on the left and right of the equality
WHERE fldinfo=ISNULL(@parameter,fldinfo)
If @parameter is null it's comparing every row to itself.
Using
WHERE @parameter IS NULL OR fldinfo = @parameter
You are comparing against a single value, so the optimiser can make use of statistics to come up with a reasonable plan (parameter sniffing notwithstanding).
I'm sure someone with more in-depth knowledge can explain the mechanics better, especially how the optimiser interprets it if @parameter is not null, but I'm pretty sure that by putting the column in that function have made the query non sargable.
Happy to stand corrected.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
July 13, 2020 at 1:35 pm
Luis
You have the same column on the left and right of the equality
WHERE fldinfo=ISNULL(@parameter,fldinfo)
...
Happy to stand corrected.
It's actually because of the function usage on a column. Every row must be evaluated as a single condition to produce a result before it can use the result to do the comparison. When you break it apart to two separate conditions, the lookup can happen directly although it would be much better to do this as a dynamic "Catch All" query. Here's what I consider to be the "bible" on such things...
https://duckduckgo.com/?t=ffab&q=gail+shaw+catch+all&ia=web
https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/
--Jeff Moden
Change is inevitable... Change for the better is not.
July 13, 2020 at 2:26 pm
In addition to Scott's good answer, you should also add "OPTION(RECOMPILE)" to the end of your statement in order to see the desired effect (a seek operation when a value is supplied to the parameter). Other wise, the optimizer will create a "worst case scenario" plan and opt to compile a plan with a scan.
SELECT *
FROM tblInfo
WHERE @parameter IS NULL OR fldinfo = @parameter
OPTION(RECOMPILE);
July 13, 2020 at 2:29 pm
Using
WHERE @parameter IS NULL OR fldinfo = @parameter
You are comparing against a single value
It's not the same query as the one the OP posted. NULL does not equal NULL
drop table if exists test_tblinfo;
go
create table test_tblinfo(fldinfo int);
go
insert test_tblinfo values
(null),
(1);
declare
@parameter int=null;
/* original */
SELECT * FROM test_tblinfo
WHERE fldinfo=ISNULL(@parameter,fldinfo);
/* incorrect */
SELECT *
FROM test_tblinfo
WHERE @parameter IS NULL OR fldinfo = @parameter;
/* correct */
SELECT *
FROM test_tblinfo
WHERE fldinfo IS NOT NULL OR fldinfo = @parameter;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 13, 2020 at 3:22 pm
Any function against a table column in a WHERE clause is not a good idea and is potentially bad for performance, because it prevents index seeks for that comparison.
Remember that SQL has to create one plan before the query runs that always satisfies the query.
Thus, when you code something like:
WHERE fldinfo = ISNULL(@parameter,fldinfo)
you force SQL to scan the fldinfo column, rather than seek, because it would be too complex for SQL to try to analyze function calls, which could be arbitrarily complex. Therefore, SQL, to be sure of correct results, must do a scan rather than a seek to match those values.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 14, 2020 at 8:49 am
david.edwards 76768 wrote:Using
WHERE @parameter IS NULL OR fldinfo = @parameter
You are comparing against a single value
It's not the same query as the one the OP posted. NULL does not equal NULL
drop table if exists test_tblinfo;
go
create table test_tblinfo(fldinfo int);
go
insert test_tblinfo values
(null),
(1);
declare
@parameter int=null;
/* original */
SELECT * FROM test_tblinfo
WHERE fldinfo=ISNULL(@parameter,fldinfo);
/* incorrect */
SELECT *
FROM test_tblinfo
WHERE @parameter IS NULL OR fldinfo = @parameter;
/* correct */
SELECT *
FROM test_tblinfo
WHERE fldinfo IS NOT NULL OR fldinfo = @parameter;
Yup, of course NULL does not equal NULL, but the fldinfo cannot be NULL (confirmed by OP).
So presumably @Parameter is optional and can be NULL. In which case the "incorrect" version will return all rows only when @parameter IS NULL, which is presumably the intended behaviour.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
July 14, 2020 at 11:15 am
Yup, of course NULL does not equal NULL, but the fldinfo cannot be NULL (confirmed by OP).
So presumably @Parameter is optional and can be NULL. In which case the "incorrect" version will return all rows only when @parameter IS NULL, which is presumably the intended behaviour.
Well you're right the OP did confirm the fldinfo cannot be NULL. Imo it still makes more sense to have 2 queries (or proc's or whatever) than it does to put "WHERE @parameter IS NULL or..." into 1 query (which I would never do). So I change my original answer to drop the WHERE clause entirely when @parameter is null. It could be 2 procs: 1 with mandatory parameter, and 1 without.
if @parameter is null
select * from test_tblinfo;
else
select * from test_tblinfo where fldinfo=@parameter;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply