June 18, 2009 at 10:42 pm
Hi Expertz
Performance wise Which is better
char(1) or varchar(1)
tinyint or smallint or int
float(n) or float ie with specifying (n) or without
And please provide me a good cursor example (ie optimized cursor eexample).
Tanx 😀
June 18, 2009 at 11:05 pm
Hi
I try to answer effectively your questions, in the first case, char(1) or varchar(1) sounds like the same but the difference is on the space used, the char type always use the space even if no data is stored, the varchar only use the space if the data is stored. For the tiny, small or int, the answer depend on what must be the maximum number to be stored, remember that tinyint use only 1 byte and must store until 255 as value, the smallint use 2 bytes and can store values into range 2^15 (-32,768) to 2^15-1 (32,767), finally the int use 4 bytes and can store values into range -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). The last question is answered by Microsoft that specify "Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53." So when you use float without n you are using float(53).
To show a sample that use a cursor, this depend on the need, sometimes the best solution is not use a cursor, sorry if i can't show you a cursor.
Regards
June 18, 2009 at 11:53 pm
.
Tanx 😀
June 19, 2009 at 1:48 am
Can you please help me with how to about optimizing stored procedures containing cursors.
What is the difference between clustered index seek and scan.
how to reduce cost %.
Tanx 😀
June 19, 2009 at 3:34 am
Eswin (6/19/2009)
Can you please help me with how to about optimizing stored procedures containing cursors.
Without seeing the procedure all I can suggest is to try and replace the cursor with set-based code.
What is the difference between clustered index seek and scan.
http://sqlinthewild.co.za/index.php/2007/11/15/execution-plan-operations-scans-and-seeks/
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 21, 2009 at 10:47 pm
GilaMonster (6/19/2009)
Eswin (6/19/2009)
Can you please help me with how to about optimizing stored procedures containing cursors.Without seeing the procedure all I can suggest is to try and replace the cursor with set-based code.
--exec shiftwise_attendance_details -1,2,'4/25/2009','5/24/2009','E',2009
CREATE PROCEDURE [dbo].[shiftwise_attendance_details]
@employee_id int,
@organization_id int,
@start_date datetime,
@end_date datetime,
@report_type varchar(1),
@leaveYear int
AS
BEGIN
declare
@count int,
@day int,
@s_date datetime,
@e_date datetime,
@hireDate datetime,
@termDate datetime,
@emp_id int,
@User_Id varchar(10),
@user_name varchar(30),
@ATSDATE datetime,
@exp_punchin_time varchar(8),
@act_punchin_time varchar(8),
@exp_punchout_time varchar(8),
@act_punchout_time varchar(8),
@ACT_TIME_SPENT varchar(8),
@break_time varchar(8),
@item_code varchar(15),
@holiday_region varchar(5),
@restricted_holiday varchar(2),
@half_day varchar(5),
@itemcodecomp varchar(15),
@temp_dt datetime,
@emp_id1 int,
@User_Id1 varchar(10),
@user_name1 varchar(30),
@ATSDATE1 datetime,
@exp_punchin_time1 varchar(8),
@act_punchin_time1 varchar(8),
@exp_punchout_time1 varchar(8),
@act_punchout_time1 varchar(8),
@ACT_TIME_SPENT1 varchar(8),
@break_time1 varchar(8),
@item_code1 varchar(15),
@half_day1 varchar(5),
@criteria_flag int,
@flag int,
@slot1 float,
@slot2 float,
@slot3 float,
@slot4 float,
@empCount int
set @slot1=0
set @slot2=0
set @slot3=0
set @slot4=0
set @criteria_flag =0
set @empCount=0
create table #temp1
(
emp_id int, User_Id varchar(10), user_name varchar(30), ATSDATE datetime,
exp_punchin_time varchar(8) null, act_punchin_time varchar(8),
exp_punchout_time varchar(8) null, act_punchout_time varchar(8),
ACT_TIME_SPENT varchar(8), break_time varchar(8),item_code varchar(15),half_day varchar(5)
)
select @e_date=convert(datetime,convert(varchar(10), getdate(),105),105)
if( @end_date > @e_date )
begin
select @end_date = @e_date
end
If @employee_id = @start_date and leave.leave_start_dt = @start_date and leave.leave_end_dt <= @end_date
or leave.leave_start_dt = @end_date)
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 @end_date
begin
select @e_date = @end_date
end
while (@s_date 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
/* unplanned leave calculation */
/* checking for holiday */
set nocount on
declare c4 cursor for
select distinct emp.employee_id, amt.user_id, amt.user_name, emp.HireDate, emp.TermDate
from employee_tbl emp,ats_map_tbl amt
where employee_status='A' and emp.employee_id=amt.employee_id
and amt.organization_id=@organization_id
open c4
fetch c4 into @emp_id, @User_id, @user_name, @hireDate, @termDate
while @@fetch_status = 0
begin
if @hireDate = @end_date)
begin
select @e_date = @end_date
end
else
begin
select @e_date = @termDate
end
select @holiday_region=(case when holiday_region is null then 'KER' else holiday_region end) from Employeemaster_leave_tbl where employee_id=@emp_id
and leave_year = @leaveYear
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)
while (@s_date 0
begin
select @count=count(*) from TL_holiday where holiday_date=@s_date and holiday_region=@holiday_region
If @count > 0
begin
select @restricted_holiday = restricted_holiday from TL_holiday where holiday_date = @s_date
and holiday_region = @holiday_region
if @restricted_holiday = 'N'
begin
update #temp1 set item_code='HOL' where ATSDATE = @s_date and emp_id=@emp_id
end
else
begin
select @count = count(*) from tl_emp_restholidays where
rhday_date = @s_date and employee_id = @emp_id
if @count >= 1
begin
update #temp1 Set item_code = 'HOL' where ATSDATE = @s_date and emp_id=@emp_id
end
end
end
end
else
begin
select @count=count(*) from TL_holiday where holiday_date=@s_date and holiday_region=@holiday_region
if @count > 0
begin
select @restricted_holiday = restricted_holiday from TL_holiday where holiday_date = @s_date
and holiday_region= @holiday_region
if @restricted_holiday = 'N'
begin
insert into #temp1 values
(
@emp_id, @User_Id, @user_name, @s_date,
@exp_punchin_time, '00:00:00',
@exp_punchout_time, '00:00:00',
'00:00:00', '00:00:00','HOL','N'
)
end
else
begin
select @count = count(*) from tl_emp_restholidays where
rhday_date = @s_date and employee_id = @emp_id
if @count >= 1
begin
insert into #temp1 values
(
@emp_id, @User_Id, @user_name, @s_date,
@exp_punchin_time, '00:00:00',
@exp_punchout_time, '00:00:00',
'00:00:00', '00:00:00','HOL','N'
)
end
end
end
if @count < 1
begin
if( convert(varchar(10), @s_date,105) = convert(varchar(10), getdate(),105) )
begin
if( @exp_punchin_time 0
begin
if @criteria_flag = 1 -- 2to 8
begin
select @slot1=0.5
--select @slot1 as one
end
else if @criteria_flag = 2 ---8 to 12
begin
select @slot2=0.5
-- select @slot2 as two
end
else if @criteria_flag = 3 ---12 to 2.30
begin
select @slot3=0.5
-- select @slot3 as three
end
else if @criteria_flag = 4 ---2.30 to ..
begin
select @slot4=0.5
--select @slot4 as four
end
else
begin
select @slot1=0
select @slot2=0
select @slot3=0
select @slot4=0
end
end ---if>0 ends new change
---insertion/updation starts--(if empl id not in new table then insert else update)
select @empCount = count(*) from #temp1_shiftwisereport where emp_id2=@emp_id1
if @empCount > 0--emp id already present so update
begin
--select @empCount,@User_Id1 as alreadypresent_ODWITHOUTHD_UPDATE
update #temp1_shiftwisereport set slot1=slot1+@slot1,slot2=slot2+@slot2,slot3=slot3+@slot3,slot4=slot4+@slot4 where emp_id2=@emp_id1
end
else--emp id not present so insert
begin
--select @empCount,@User_Id1 as notpresent_ODWITHOUTHD_INSERT
insert into #temp1_shiftwisereport ( emp_id2, User_Id2, user_name2, ATSDATE2,
exp_punchin_time2, act_punchin_time2,
exp_punchout_time2, act_punchout_time2,
ACT_TIME_SPENT2, break_time2,item_code2,half_day2,slot1,slot2,slot3,slot4)
values
(@emp_id1, @User_Id1, @user_name1, @ATSDATE1,
@exp_punchin_time1, @act_punchin_time1,
@exp_punchout_time1, @act_punchout_time1,
@ACT_TIME_SPENT1, @break_time1,@item_code1,@half_day1,
@slot1,@slot2,@slot3,@slot4)
end
---insertion/updation ends--(if empl id not in new table then insert else update)
---
set @slot1=0
set @slot2=0
set @slot3=0
set @slot4=0
set @criteria_flag =0
--
--end ---if>0 ends
end
-------------further split as od with hd----- ends---
-------------further split as od with out hd----- starts---
else
begin
select @criteria_flag = case when @exp_punchout_time1 between '14:00:00' and '19:59:59' then 1
else case when @exp_punchout_time1 between '20:00:00' and '23:59:59' then 2
else case when @exp_punchout_time1 between '00:00:00' and '02:29:59' then 3
else case when @exp_punchout_time1 between '02:30:00' and '13:59:59' then 4 else 0 end
end
end
end
if @criteria_flag > 0
begin
if @criteria_flag = 1 -- 2to 8
begin
select @slot1=1
--select @slot1 as one
end
else if @criteria_flag = 2 ---8 to 12
begin
select @slot2=1
-- select @slot2 as two
end
else if @criteria_flag = 3 ---12 to 2.30
begin
select @slot3=1
-- select @slot3 as three
end
else if @criteria_flag = 4 ---2.30 to ..
begin
select @slot4=1
--select @slot4 as four
end
else
begin
select @slot1=0
select @slot2=0
select @slot3=0
select @slot4=0
end
end ---if>0 ends new change
---insertion/updation starts--(if empl id not in new table then insert else update)
select @empCount = count(*) from #temp1_shiftwisereport where emp_id2=@emp_id1
if @empCount > 0--emp id already present so update
begin
--select @empCount,@User_Id1 as alreadypresent_ODWITHOUTHD_UPDATE
update #temp1_shiftwisereport set slot1=slot1+@slot1,slot2=slot2+@slot2,slot3=slot3+@slot3,slot4=slot4+@slot4 where emp_id2=@emp_id1
end
else--emp id not present so insert
begin
--select @empCount,@User_Id1 as notpresent_ODWITHOUTHD_INSERT
insert into #temp1_shiftwisereport ( emp_id2, User_Id2, user_name2, ATSDATE2,
exp_punchin_time2, act_punchin_time2,
exp_punchout_time2, act_punchout_time2,
ACT_TIME_SPENT2, break_time2,item_code2,half_day2,slot1,slot2,slot3,slot4)
values
(@emp_id1, @User_Id1, @user_name1, @ATSDATE1,
@exp_punchin_time1, @act_punchin_time1,
@exp_punchout_time1, @act_punchout_time1,
@ACT_TIME_SPENT1, @break_time1,@item_code1,@half_day1,
@slot1,@slot2,@slot3,@slot4)
end
---insertion/updation ends--(if empl id not in new table then insert else update)
---
set @slot1=0
set @slot2=0
set @slot3=0
set @slot4=0
set @criteria_flag =0
--
--end ---if>0 ends new change
end
-------------further split as od with out hd----- ends---
--------------------------new END------------
---normal shift comes in which slot?
end--id O/D ends
-- else---if not O/D
else
begin
--select @item_code1 as elseIG
if @half_day1 = 'Y'--if not O/D but HD
begin
---calculation part starts--
select @criteria_flag = case when @act_punchout_time1 between '14:00:00' and '19:59:59' then 1
else case when @act_punchout_time1 between '20:00:00' and '23:59:59' then 2
else case when @act_punchout_time1 between '00:00:00' and '02:29:59' then 3
else case when @act_punchout_time1 between '02:30:00' and '13:59:59' then 4 else 0 end
end
end
end
--calculation part ends----
if @criteria_flag > 0
begin
if @criteria_flag = 1 -- 2to 8
begin
select @slot1=0.5
--select @slot1 as one
end
else if @criteria_flag = 2 ---8 to 12
begin
select @slot2=0.5
--select @slot2 as two
end
else if @criteria_flag = 3 ---12 to 2.30
begin
select @slot3=0.5
--select @slot3 as three
end
else if @criteria_flag = 4 ---2.30 to ..
begin
select @slot4=0.5
--select @slot4 as four
end
else
begin
select @slot1=0
select @slot2=0
select @slot3=0
select @slot4=0
end
end ---if>0 ends new change
---insertion/updation starts--(if empl id not in new table then insert else update)
select @empCount = count(*) from #temp1_shiftwisereport where emp_id2=@emp_id1
if @empCount > 0--emp id already present so update
begin
update #temp1_shiftwisereport set slot1=slot1+@slot1,slot2=slot2+@slot2,slot3=slot3+@slot3,slot4=slot4+@slot4 where emp_id2=@emp_id1
end
else--emp id not present so insert
begin
insert into #temp1_shiftwisereport ( emp_id2, User_Id2, user_name2, ATSDATE2,
exp_punchin_time2, act_punchin_time2,
exp_punchout_time2, act_punchout_time2,
ACT_TIME_SPENT2, break_time2,item_code2,half_day2,slot1,slot2,slot3,slot4)
values
(@emp_id1, @User_Id1, @user_name1, @ATSDATE1,
@exp_punchin_time1, @act_punchin_time1,
@exp_punchout_time1, @act_punchout_time1,
@ACT_TIME_SPENT1, @break_time1,@item_code1,@half_day1,
@slot1,@slot2,@slot3,@slot4)
end
---insertion/updation ends--(if empl id not in new table then insert else update)
---
set @slot1=0
set @slot2=0
set @slot3=0
set @slot4=0
set @criteria_flag =0
--
--end ---if>0 ends new change
end--if not O/D but HD ends--
else--if not O/D and not HD --
begin
--calculation part starts----
-------**********************************----------
if @act_punchin_time1!=@act_punchout_time1 --to avoid non punch entries
begin
if @item_code1 is null
begin
select @criteria_flag = case when @act_punchout_time1 between '14:00:00' and '19:59:59' then 1
else case when @act_punchout_time1 between '20:00:00' and '23:59:59' then 2
else case when @act_punchout_time1 between '00:00:00' and '02:29:59' then 3
else case when @act_punchout_time1 between '02:30:00' and '13:59:59' then 4 else 0 end
end
end
end
end--end of if (to avoid non punch entries)
if @criteria_flag > 0
begin
if @criteria_flag = 1 -- 2to 8
begin
select @slot1=1
end
else if @criteria_flag = 2 ---8 to 12
begin
select @slot2=1
end
else if @criteria_flag = 3 ---12 to 2.30
begin
select @slot3=1
end
else if @criteria_flag = 4 ---2.30 to ..
begin
select @slot4=1
end
else
begin
select @slot1=0
select @slot2=0
select @slot3=0
select @slot4=0
end
end ---if>0 ends new change
---insertion/updation starts--(if empl id not in new table then insert else update)
select @empCount = count(*) from #temp1_shiftwisereport where emp_id2=@emp_id1
if @empCount > 0--emp id already present so update
begin
update #temp1_shiftwisereport set slot1=slot1+@slot1,slot2=slot2+@slot2,slot3=slot3+@slot3,slot4=slot4+@slot4 where emp_id2=@emp_id1
end
else--emp id not present so insert
begin
insert into #temp1_shiftwisereport ( emp_id2, User_Id2, user_name2, ATSDATE2,
exp_punchin_time2, act_punchin_time2,
exp_punchout_time2, act_punchout_time2,
ACT_TIME_SPENT2, break_time2,item_code2,half_day2,slot1,slot2,slot3,slot4)
values
(@emp_id1, @User_Id1, @user_name1, @ATSDATE1,
@exp_punchin_time1, @act_punchin_time1,
@exp_punchout_time1, @act_punchout_time1,
@ACT_TIME_SPENT1, @break_time1,@item_code1,@half_day1,
@slot1,@slot2,@slot3,@slot4)
end
---insertion/updation ends--(if empl id not in new table then insert else update)
---
set @slot1=0
set @slot2=0
set @slot3=0
set @slot4=0
set @criteria_flag =0
--
end
end
---*********************************************8--------------
---For non punch entries ,leaves and od--start---
if @item_code1 = 'ABS' or @item_code1 = 'LOP'
begin
select @slot1=0
select @slot2=0
select @slot3=0
select @slot4=0
select @empCount = count(*) from #temp1_shiftwisereport where emp_id2=@emp_id1
if @empCount = 0
begin
insert into #temp1_shiftwisereport ( emp_id2, User_Id2, user_name2, ATSDATE2,
exp_punchin_time2, act_punchin_time2,
exp_punchout_time2, act_punchout_time2,
ACT_TIME_SPENT2, break_time2,item_code2,half_day2,slot1,slot2,slot3,slot4)
values
(@emp_id1, @User_Id1, @user_name1, @ATSDATE1,
@exp_punchin_time1, @act_punchin_time1,
@exp_punchout_time1, @act_punchout_time1,
@ACT_TIME_SPENT1, @break_time1,@item_code1,@half_day1,
@slot1,@slot2,@slot3,@slot4)
end
else
begin
update #temp1_shiftwisereport set slot1=slot1+@slot1,slot2=slot2+@slot2,slot3=slot3+@slot3,slot4=slot4+@slot4 where emp_id2=@emp_id1
end
end
---For non punch entries ,leaves and od--ends---
---*********************************************8--------------
end--if not O/D and not HD --
fetch shiftWise into @emp_id1, @User_Id1, @user_name1, @ATSDATE1,
@exp_punchin_time1, @act_punchin_time1,
@exp_punchout_time1, @act_punchout_time1,
@ACT_TIME_SPENT1, @break_time1,@item_code1,@half_day1
end--while ends
close shiftWise
deallocate shiftWise
select distinct emp_id2, User_Id2, user_name2, ATSDATE2,
exp_punchin_time2, act_punchin_time2,
exp_punchout_time2, act_punchout_time2,
ACT_TIME_SPENT2, break_time2,item_code2,half_day2,slot1,slot2,slot3,slot4,
master.shiftin_time,master.shiftout_time
from #temp1_shiftwisereport,ATS_shift_empl punch,ATS_shift_master master
where punch.employee_id=emp_id2
and punch.effective_date in
(select max(effective_date) from ATS_shift_empl where employee_id=emp_id2 and effective_date < = @end_date)
and master.shift_id = punch.shift_id
order by user_name2
drop table #temp1
drop table #temp1_shiftwise
drop table #temp1_shiftwisereport
END
GO
This is the code. It take almost two minute to get the result.
It affects around 4000 rows and displays only 203 rows. Could this be the problem.
I think its the cursors that are creating the problem.
Is using cursor on temp table bad .
I have declare shiftWise cursor for
select * from #temp1_shiftwise .
Should i use firehose cursor or read-only cursor .
Tanx 😀
June 22, 2009 at 4:34 am
That's 800 lines of code there.
Using a cursor is bad regardless of whether it's on a temp table or a real table. As for fire-hose or read-only, neither. Your best bet is to try and make that whole piece set-based and drop the cursors completely. It's going to be a rewrite and it's going to take a while.
I would suggest starting with one cursor and seeing if you can turn what it's doing into set-based 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 = @start_date and leave.leave_end_dt <= @end_date
or leave.leave_start_dt = @end_date)
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 @end_date
begin
select @e_date = @end_date
end
while (@s_date 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
Can you explain what this is supposed to do?
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 22, 2009 at 10:09 am
Eswin (6/18/2009)
Hi ExpertzPerformance wise Which is better
...
And please provide me a good cursor example (ie optimized cursor eexample).
Isn't this, optimized cursor an oxymoron?? 😉
June 22, 2009 at 10:15 am
Lynn Pettis (6/22/2009)
Eswin (6/18/2009)
Hi ExpertzPerformance wise Which is better
...
And please provide me a good cursor example (ie optimized cursor eexample).
Isn't this, optimized cursor an oxymoron?? 😉
Heh... more like a "carbon-monoxide moroff". 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2009 at 10:17 am
Lynn Pettis (6/22/2009)
Isn't this, optimized cursor an oxymoron?? 😉
Optimised cursor = runs way slower than set-based code
Unoptimised cursor = runs way, way, way slower than 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 22, 2009 at 12:24 pm
Eswin, I'm going to suggest hiring a contractor to sort that one out for you. It's going to take a few hours, at the very least, and getting it exactly right will require access to a test copy of your database.
That'll be faster and easier than trying to get free help online in this case, just because it's not a simple project.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 23, 2009 at 10:36 am
I agree with Gila.
Your first cursor code is only 44 lines of code. So this should be manageable for your first cut at rewriting the cursor-based code with a few UPDATE and INSERT statements handling ALL employees of relevance simulatneously.
I suggest you try writing set-based code INSTEAD of jumping to a cursor. A cursor is only a WAY of doing things, not an actual OPERATIONAL NEED. Without invoking "THE THREAD" again, let's make sure a cursor or loop is REALLY the only way to go. Not much of a point in going faster in the wrong direction...
Before we can answer your question, you have to give it a try first. If it does not work, then post your new code and describe the problem. Do not forget to include the code to fill the tables with some representative data so we can run and debug your code.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply