May 5, 2003 at 9:10 am
Maybe I don't know enough about the inner workings of SQL Server, but if anyone could shed some light on this, it would be much appreciated. Consider the following queries.
Q1:
declare @txt_string varchar(255)
select @txt_string = dbo.ManipulateText('Some Text')
select * from table1 where descr_field = @txt_string
Q2:
select * from table1 where descr_field = dbo.ManipulateText('Some Text')
Obviously, in the first query, the UDF is only evaluated once and the query is very quick. But in the second query, it appears that SQL Server evaluates the function for every row in table1. So depending on how many rows are in the table and how complex the function is, the difference in time ranges from seconds to hours. Is this normal behavior for SQL Server? Is there anything I can do to tell SQL Server to evaluate the function in the second query only once?
Any help would be much appreciated.
Thanks in advance.
Peter
May 5, 2003 at 11:14 am
Looks interesting.
Did you try
select * from table1 where descr_field IN ( dbo.ManipulateText('Some Text') )
May 5, 2003 at 12:07 pm
rajeshpatavardhan:
Thanks for the input. I just tried the query with the IN clause but the results are the same.
May 6, 2003 at 7:41 am
Have you check the execution plan. My guess is that with Q2 it is not using an index. The same behavior can be found when the R value of an = is any kind of a function
Example: a.Key = Right(SomeValue,10) will cause a table scan in most cases.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply