Functions and Execution Plan

[Madhivanan]

The cost of query with usage of functions is as same as that of without
functions

In the below code, the query cost of insert is 0.02% and two select
statements costs same 0.04%

Declare @t table(mydate datetime)
Declare @i int
set @i=1
while @i<=5000
Begin
insert into @t values(getdate())
set @i=@i+1EndSelect mydate from @t
Select convert(varchar,mydate,112) from @t

But I thought usage of convert function will take more query cost
What do you think of this?

Madhivanan

[Simon Hayes]

Adding a CONVERT() to the output is very little extra work, but I
suspect that you're referring to the fact that using a function on a
column in the WHERE clause can prevent MSSQL from using an index. That
can have a significant impact on the query plan, eg:

create table dbo.m (mydate datetime primary key)

Declare @i int
set @i=1
while @i<=5000
Begin
insert into m values(getdate() + @i)
set @i=@i+1
End

-- now run these two queries in the same batch

select *
from m
where mydate between '20100815' and '20100917'

select *
from m
where convert(char(8), mydate, 112) between '20100815' and '20100916'
On my test server, the first query takes 11% of the batch, the second
is 89% - although they are functionally equivalent, the first one can
do a seek in the clustered index, but the second must scan it. That's
not to say that functions in the SELECT will never affect the query
plan or cost, but when reviewing code it's probably more important to
look at the WHERE clause first.

Simon

[Madhivanan]

Thanks Simon

So only in Where condition it affects the performance and not in select
isnt it?

Madhivanan

[Simon Hayes]

I'm sure that functions in the SELECT clause can affect the query cost
- nested string functions, nested CASE expressions, a scalar UDF which
looks up other tables etc. And all other things being equal, "SELECT
col1" will be more efficient than "SELECT somefunc(col1)", simply
because MSSQL has less work to do.

But the difference may be extremely small (as in your example), and I
guess that in most cases, any really big differences in performance
would come from functions in the WHERE clause, not the SELECT clause.
Of course there are many other reasons why a query might run slowly -
missing indexes, out-of-date statistics and so on - which have nothing
to do with functions at all, so if you have a performance problem with
a specific query, then it's best to start by looking at the query plan
before you think about how to re-write the code.

Simon

[Erland Sommarskog]

Madhivanan (ma************@gmail.com) writes:

So only in Where condition it affects the performance and not in select
isnt it?

What matters is that if you put an indexed column into an expression,
the index can no longer be used for searches.

If you have

where mydate between '20100815' and '20100917'

and there is an index on mydate, SQL Server can use that index to
find the matching rows. But if you say:

convert(char(8), mydate, 112) between '20100815' and '20100916'

that index can no longer be used, because that index holds datetime
values, and this is a string expression.

Note that if mydate is not indexed, the only cost for the function
call is the function call itself. In this case, it's not more expensive
that having it in the SELECT list. (Except that if it's in a WHERE
clause, it may be applied to more values.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Madhivanan]

Well
Thanks for the suggesstions

Madhivanan