January 27, 2011 at 3:38 pm
Guys I've a UDF which returns a table and the select query is sumthing like below
==============
select
....
from
emp u with( nolock )
left outer join
(
(
select
empID
,[ID] = max(empDate)
from
empHistory uh with( nolock )
where
empDate <= isnull(@asOfDate,empDate)
and
uh.empID = isnull(@empID,uh.empID)
group by
empID
) uhr
join
empHistory uh
on
uh.empDate = uhr.ID and uh.empID = uhr.empID
and
uh.empID = isnull(@empID,uh.empID)
)
on
u.empID = uh.empID
where
uh.empID = isnull(@empID,u.empID)
==============
Now even if my tables have appropriate indexes, execution plan always shows index scan and when I change it to use the case stmt then it using the index seek and, after I checked the IO and cpu from the profiler its quite low as compared to the above one but.... when I've created a new udf with updated where clause and then I tried runing it from different query window and running the udf instead of the select.. t way slow and consuming almost 5 time of io and cpu then the original one...
The updated where clause is something like below
===============
empDate <=
CASE
WHEN @asOfDate IS NULL THEN empDate
ELSE @asOfDate END
and
uh.empID =
CASE
WHEN @empID IS NULL THEN uh.empID
ELSE @empID END
group by
empID
) uhr
join
empHistory uh
on
uh.empDate = uhr.ID and uh.empID = uhr.empID
and
uh.empID =
CASE
WHEN @empID IS NULL THEN uh.empID
ELSE @empID END
)
on
u.empID = uh.empID
where
uh.empID =
CASE
WHEN @empID IS NULL THEN uh.empID
ELSE @empID END
===============
I'm unable to understand this....
Datatype of empdate is datetime and empid is numeric
Any thoughts on this.
Rohit
January 27, 2011 at 3:43 pm
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
The problem is that if SQL creates a plan with a seek and on a second call the parameter is null the query would return incorrect results. The plan must be safe for reuse, so you get index scans instead.
p.s. Nolock?
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2011 at 3:46 pm
I was wondering about NOLOCKS as well...
CEWII
January 27, 2011 at 3:55 pm
GilaMonster (1/27/2011)
p.s. Nolock?
Are you talking about the one table I missed nolock hint or sumthing else??
January 27, 2011 at 3:57 pm
I was wondering why you were using NOLOCKS at all, I tend to urge caution with it and that many people don't really understand the ramifications of using it. Gila might have been refering to something I hadn't thought of..
CEWII
January 27, 2011 at 4:01 pm
Ramji29 (1/27/2011)
GilaMonster (1/27/2011)
p.s. Nolock?
Are you talking about the one table I missed nolock hint or sumthing else??
No, I'm wondering why you're using them at all. I assume since you're using it that occasionally incorrect data is acceptable. I also assume you didn't read the blog post I linked to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2011 at 12:00 pm
this code is used in a udf and that udf is used across all over.. thats y we've used nolock to minimize locking.... apart from this in the link you posted I found that either we should go for dynamic sql( which is not a option for me).... We're on 2005 and the recompile is also not an option for me... I'm thinking for the hint, but I personnaly don't want it.... I'm still loking aroung and will post my findings if I found something useful...
Thanks a Lot Gail... ur a Star like always...
Rohit
January 28, 2011 at 1:25 pm
You really only have 3 options
Dynamic SQL (not in a function though)
The recompile hint on 2008
Write your code in such a way that you're not using the same piece of code for both a null parameter and a passed parameter. Since you have only one parameter this is easy to do.
The 'pass a null for all, otherwise value' 'pattern' is very popular among developers. It looks cool, it minimises code, it allows reuse. Problem is it does not work well on SQL and will not perform well.
As for the nolock, locks are not a problem, they are essential for concurrent access. If you have blocking, optimise your code, tune your indexes. Don't tell SQL 'quick and dirty please, inaccuracy is OK' unless you know that is the case.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply