May 11, 2010 at 8:05 am
All,
one of my co worker sent a query this morning and told that it is running for 12 seconds. He asked me is there any possibility to reduce the execution time.
I have checked the query and noticed that he used function in the WHERE clause condition.
i.e
where asset_type = dbo.fngetasset_type('asset','HFund')
I replaced it as below
DECLARE @var_HFund INT
SELECT @var_HFund = dbo.fngetasset_type('asset','HFund')
where asset_type = @var_HFund
and i ran the query...
It took 630 milliseconds only ( less that 1 second).
So i have leanred a new thing that we should avoid using function call in the WHERE clause.
But i am not clear with the reason. As far as I know , query will call the function for each and every row. Thats why we face performance related issue. Am i correct?
karthik
May 11, 2010 at 12:12 pm
You would need to look at the execution plan to be sure what was happening in the query with the fuction, but yes, using functions like that in that situation can certainly be problematic for query performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply