3

I have a query that looks like the following:

SELECT someString  FROM
(
    SELECT someString FROM someTable
    WHERE someField = 1
) X
WHERE dbo.fnMyClrScalarFunction(X.someString) = 0

The problem is that the query optimizer is moving the UDF inside the subquery, where it is applied before the fairly restrictive 'someField = 1' condition. Unfortunately, the UDF is not exactly speedy, and this results in terrible performance. Is there any way to prevent this (aside from using a temp table) or to establish to sql server that the UDF is expensive?

Thanks in advance

Brian
  • 211
  • 1
  • 14

2 Answers2

2

"fairly restrictive 'someField = 1' condition"

Do you have an index on that column. SQL should only apply the scalar function if it thinks it would be faster to do so. If you have an index where the first column is 'someField', then I would recommend that you make sure the statistics for this table is up to date.

George Mastros
  • 24,112
  • 4
  • 51
  • 59
  • There is an index on the someField, but updating statistics doesn't change the query plan. Does sql server, by any chance, collect statistics on UDFs so that their estimated cost (might) become more accurate over time? – Brian Nov 10 '10 at 23:47
0

One way is to use a temporary table, or a table variable:

declare @t table (SomeString varchar(100))
insert @t select someString from someTable where someField = 1
select someString from @t where  dbo.fnMyClrScalarFunction(someString) = 0

Temporary look more expensive than they are. In fact, most joins are executed using temporary helper tables :)

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • This is the approach I had taken, but it seems there is a need to be able to mark such UDFs as expensive so that the optimizer does not produce terrible plans. – Brian Nov 10 '10 at 23:31