June 26, 2009 at 12:53 am
Hi Expertz
Can anyone tell me which will be better performance wise
Sample1:
select @emp_id=emp.employee_id,@User_Id=emp.User_Id,@user_name=amt.user_name,
@s_date=leave.leave_start_dt ,
@e_date=leave.leave_end_dt ,
@item_code=(case when leave.half_day ='Y' then leave_type+'-H/D' else leave_type end)
,@half_day=leave.half_day
from employee_tbl emp,ats_map_tbl amt,tl_leave_tbl leave
where employee_status='A'
and emp.employee_id=amt.employee_id
and emp.employee_id=leave.employee_id
and amt.organization_id=@organization_id
and (leave.status='A' )
and ( ( ( leave.leave_start_dt between @start_date and @end_date)
and (leave.leave_end_dt between @start_date and @end_date) )
or leave.leave_start_dt >= @start_date and leave.leave_start_dt <=@end_date
or leave.leave_end_dt >= @start_date and leave.leave_end_dt <= @end_date
or leave.leave_start_dt <= @start_date and leave.leave_end_dt >= @end_date)
while @@rowcount <> 0
begin
if @s_date < @start_date
begin
select @s_date = @start_date
end
if @e_date > @end_date
begin
select @e_date = @end_date
end
while (@s_date <= @e_date)
begin
select @day = datepart(weekday,@s_date)
If @day != 1
begin
If @day != 7
begin
select @count = count(*) from #temp1 where ATSDATE = @s_date and emp_id=@emp_id
If @count > 0
begin
update #temp1 set item_code=@item_code,half_day=@half_day where ATSDATE = @s_date and emp_id=@emp_id
end
If @count < 1
begin
select @exp_punchin_time=asm.shiftin_time,@exp_punchout_time=asm.shiftout_time from ATS_shift_master asm, ATS_shift_empl ase
where ase.employee_id=@emp_id and ase.shift_id=asm.shift_id
and (ase.effective_date) = (select max(effective_date) from ATS_shift_empl where employee_id=@emp_id and effective_date <= @s_date) insert into #temp1 values
(
@emp_id, @User_Id, @user_name, @s_date,
@exp_punchin_time, '00:00:00',
@exp_punchout_time, '00:00:00',
( case @item_code when 'REG' then 'null' else '00:00:00' end ),
'00:00:00', @item_code,@half_day
)
end
end
end
select @s_date = dateadd(day,1,@s_date)
end
end
[/code]
Or
Sample2:
[code]
set nocount on
declare c2 cursor for
select distinct emp.employee_id,emp.User_Id,amt.user_name,
leave.leave_start_dt as START_DATE,
leave.leave_end_dt as END_DATE,
(case when leave.half_day ='Y' then leave_type+'-H/D' else leave_type end)
as item_code,leave.half_day
from employee_tbl emp,ats_map_tbl amt,tl_leave_tbl leave
where employee_status='A'
and emp.employee_id=amt.employee_id
and emp.employee_id=leave.employee_id
and amt.organization_id=@organization_id
and (leave.status='A' )
and ( ( ( leave.leave_start_dt between @start_date and @end_date)
and (leave.leave_end_dt between @start_date and @end_date) )
or leave.leave_start_dt >= @start_date and leave.leave_start_dt <=@end_date
or leave.leave_end_dt >= @start_date and leave.leave_end_dt <= @end_date
or leave.leave_start_dt <= @start_date and leave.leave_end_dt >= @end_date)
--and emp.employee_id in (select empid from #temporg)--newly added
open c2
fetch c2 into
@emp_id, @User_Id,@user_name, @s_date, @e_date,@item_code,@half_day
while @@fetch_status = 0
begin
if @s_date < @start_date
begin
select @s_date = @start_date
end
if @e_date > @end_date
begin
select @e_date = @end_date
end
while (@s_date <= @e_date)
begin
select @day = datepart(weekday,@s_date)
If @day != 1
begin
If @day != 7
begin
select @count = count(*) from #temp1 where ATSDATE = @s_date and emp_id=@emp_id
If @count > 0
begin
update #temp1 set item_code=@item_code,half_day=@half_day where ATSDATE = @s_date and emp_id=@emp_id
end
If @count < 1
begin
select @exp_punchin_time=asm.shiftin_time,@exp_punchout_time=asm.shiftout_time from ATS_shift_master asm, ATS_shift_empl ase
where ase.employee_id=@emp_id and ase.shift_id=asm.shift_id
and (ase.effective_date) = (select max(effective_date) from ATS_shift_empl where employee_id=@emp_id and effective_date <= @s_date) insert into #temp1 values
(
@emp_id, @User_Id, @user_name, @s_date,
@exp_punchin_time, '00:00:00',
@exp_punchout_time, '00:00:00',
( case @item_code when 'REG' then 'null' else '00:00:00' end ),
'00:00:00', @item_code,@half_day
)
end
end
end
select @s_date = dateadd(day,1,@s_date)
end
fetch c2 into
@emp_id, @User_Id,@user_name, @s_date, @e_date, @item_code ,@half_day
end
close c2
deallocate c2
[/code]
OR can anyone please tell me a better solution
Tanx 😀
June 26, 2009 at 1:06 am
I would say "none"...
I don't see anything that can't be done with a set-based solution, which performs much better. I suggest you to try to convert this into a set based update, maybe using some sort of temp table to store the data you are working with.
Regards
Gianluca
-- Gianluca Sartori
June 26, 2009 at 2:11 am
are both same.
can you give me some example.
Tanx 😀
June 26, 2009 at 2:23 am
Eswin (6/26/2009)
are both same.can you give me some example.
Maybe if you could explain what the innards of that cursor are supposed to do, someone can help. It's rather hard reverse-engineering a cursor.
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
June 26, 2009 at 3:15 am
I want to fetch one row at a time and then satisfy the conditions following while @@row_count 0 and while @@fetch_status = 0 .
Will using SELECT statement that assigns values to a variables and then using "while @@row_count 0" allow me to fetch one row at a time and perform queries on it.
Tanx 😀
June 26, 2009 at 3:32 am
Eswin (6/26/2009)
Will using SELECT statement that assigns values to a variables and then using "while @@row_count 0" allow me to fetch one row at a time and perform queries on it.
Yes, but it's no better than a cursor. It's still row-by-row processing. I's still going to be slow. The best way to move on from here is to work out what needs doing to the resultset as a whole (not one row at a time) and convert this entire piece into set-based code.
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
June 29, 2009 at 3:26 pm
If you are interested in a set-based solution you might try giving us DDL, sample data and expected output in addition explaining what you are actually trying to do.
I'm not sure if this'll help or not:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 29, 2009 at 4:21 pm
Hi Eswin,
just a few thoughts while you're working on providing the data:
- since you're using DATEPART(WEEKDAY ...) function you need to make sure that the setting for @@datefirst is a value you expect. I'd recommend to use SET DATEFIRST ... at the beginning of your proc.
- your "double-if"
If @day != 1
begin
If @day != 7
begin
can be replaced by IF @day >1 AND @day < 7 (in a set based solution this most probably would go the the WHERE clause)
- you should separate the insert and the update statement in two statements. The @count condition can be replaced by EXISTS rsp. NOT EXIST (to determine what rows need to be updated and what rows need to get inserted) EDIT: or by using inner rsp. left outer join.
July 4, 2009 at 12:53 pm
For your date range search, do not use all these filters
and ( ( ( leave.leave_start_dt between @start_date and @end_date)
and (leave.leave_end_dt between @start_date and @end_date) )
or leave.leave_start_dt >= @start_date and leave.leave_start_dt = @start_date and leave.leave_end_dt <= @end_date
or leave.leave_start_dt = @end_date)
Use this simple one instead
and leave.leave_start_dt = @start_date
N 56°04'39.16"
E 12°55'05.25"
July 4, 2009 at 6:21 pm
Eswin (6/26/2009)
I want to fetch one row at a time and then satisfy the conditions following while @@row_count 0 and while @@fetch_status = 0 .
That would be the problem... you have already resigned yourself to working whatever this problem is to one row at a time. Stop thinking about rows... start thinking about what you want to do to a column.
And I agree with the others. State what the problem actually is... not how you think it needs to be solved.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply