which one is better
-
Hi friends
I have following query
select taskid,staffid,
[timespent]=(SELECT CASE WHEN sum(datediff(s,wt_starttime,wt_stoptime)) IS NULL THEN '0'
ELSE sum(datediff(s,wt_starttime,isnull(wt_stoptime,getdate()))) END -- total time spent
FROM pt_vCheckworktime where fk_taskid=t.taskid and fk_staffid=t.staffid)
from task t
and have same piece of code i.e in a UDF
((SELECT CASE WHEN sum(datediff(s,wt_starttime,wt_stoptime)) IS NULL THEN '0'
ELSE sum(datediff(s,wt_starttime,isnull(wt_stoptime,getdate()))) END -- total time spent
FROM pt_vCheckworktime where fk_taskid=t.taskid and fk_staffid=t.staffid))
I am wondering which one is better or ideal.
my first query or the following ??
select taskid,staffid,dbo.pt_fntimespent(t.taskid,t.staffid) from task
at the moment both queries taking same amount of time.
-
select t.taskid, t.staffid,
sum(isnull(datediff(s,wt_starttime,isnull(wt_stoptime,getdate()))), 0) as [timespent]
from task t
left join pt_vCheckworktime
on fk_taskid=t.taskid and fk_staffid=t.staffid
group by t.taskid,t.staffid
keep in mind that your udf will be executed at row-level where the join will be used in the optimisation of the execution plan.
(IMO this query is more readeble/understandeble than when using a udf)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks alzdba thats great
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply