September 26, 2003 at 12:12 pm
The cases I've looked at worked best when all optional parameters were non-null, and timed out when some parameters were null, so I meant it the way I wrote it. There can't be an execution plan for "may be null", it either uses an index or doesn't.
But you're correct that if the query requires a table scan for other reasons, or if appropriate indexes are not available, then the query will run the same no matter how you do it.
September 26, 2003 at 1:41 pm
quote:
Read this outstanding article.Dynamic Search Conditions in T-SQL
An SQL text by Erland Sommarskog, SQL Server MVP.
http://www.algonet.se/~sommar/dyn-search.html
AMB
October 1, 2003 at 1:10 am
quote:
However, if you use the function:[...]
And then the select:
select * from lef(123456,123456,'LUN')
It uses the same index as the first select, doing a seek, and is very fast. Which relates to my original question, that something different is happening in UDF optimization, in this case something smarter.
[...]
So it is definitely re-optimizing the UDF each time it runs, kind of as thought it was dynamic sql
Ferguson,
It is interesting to note that if you call the UDF using variables instead of constants, the whole optimisation is gone: it does a scan, not a seek. Just try:
declare @RefNbr int
declare @CustCd int
declare @UnitCd char(3)
set @RefNbr = 2123456
set @CustCd = 123
set @UnitCd='LUN'
select * from lef(@RefNbr, @CustCd, @UnitCd)
Another interesting thing is that the optimisation (in fact, the re-compiling) is done only for "Inline table-valued functions", not for "Multistatement table-valued functions". If we take a peek at the syscacheobjects table, we will see that inline functions are considered views and multistatement functions are considered procs. This may be the cause of different compiling strategies.
October 1, 2003 at 6:54 am
That is interesting, and thanks for the pointer to see how it considered these.
In our case, we are generally calling the SP or UDF from a command string in ADO.NET (or similar), and replace the parameters with actual values in either case. But it is significant that if one were imbeding this inside other SP's, that the expectation of optimization would be wrong.
PS. THe pointer to the article above was very helpful also.
October 1, 2003 at 8:35 am
Great thread! Lots of good comments. We work with larger tables (Millions of rows) and find that using OR is a performance killer more often than not. In this particular case, I would use a UNION
SELECT ...
where @P IS NULL
UNION
SELECT ...
WHERE T.P = @P
Makes two precise queries without table scans.
My $0.02
Guarddata-
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply