February 4, 2008 at 5:34 am
I'm using table variable in place of cursor to increase the performance.
There are two table variables names : @EmpMaster and @ECFMaster in place of two cursors.
I'm doing some process from getting data from first table variable @EmpMaster and using that data inside second table variable @ECFMaster.. means like cursor inside another cursor.
I'm using identity field in table variables to loop through data.
Now, inner table variable will load everytime from outer table variable, So every time I have to start inner table variable value from one for identity field. But I can't truncate,drop table variable or reset identity field of table variable. So what is the way to do that ? Please give proper help using example in "SQL SERVER 2000."
set @RowCnt=1
declare @EmpMaster table (
Rownum int identity(1,1),
EmpID int,
EmpName varchar(50),
Status varchar(20),
Termination_date smalldatetime,
agentid varchar(20))
declare @ECFMaster table (
RowID int identity(1,1),
ECFID int,
Effective_date smalldatetime)
insert @EmpMaster
SELECT distinct Emp_Id,real_fname+', '+real_lname+'('+cast(Emp_ID as varchar(20))+')' as EmpName,Status,Termination_Date,NT_Domain
FROM ERRISIndia.Effectivereports.EmployeeMaster
WHERE (Designation='Voice Agent' OR Designation='Mentor'OR Designation like'%agent%')
and Status='Active'
select @MaxRows=@@rowcount
WHILE @RowCnt <=@MaxRows
BEGIN
select @Emp_Id=EmpID,
@Emp_Name=EmpName,
@status=status,
@Terdate=Termination_date,
@AgentID=agentid
from @EmpMaster
where Rownum=@RowCnt
delete from @ECFMaster
set @RowCount=1
insert @ECFMaster
SELECT ECF_ID,corrected_Effective_Date FROM Effectivereports.Effectivereports.ECFIndiaMaster WHERE Emp_ID=@Emp_ID
set @TotalRows=@@rowcount
WHILE @RowCount <=@TotalRows
BEGIN
select @ECF_Id=ECFID,
@EFFdate=Effective_date
from @ECFMaster
where RowID=@RowCount
------- Doing some process inside
set @RowCount=@RowCount+1
END
delete from @EmpMaster where Rownum=@RowCnt
set @RowCnt=@RowCnt+1
END
February 4, 2008 at 7:40 pm
You need to post in the appropriate forums for your question. I've moved this to T-SQL, 2005, but if it's not the right place, please let me know.
February 5, 2008 at 9:21 am
If you are using SQL 2005, can you use row_number() instead of the identity column?
Something like:
INSERT@ECFMaster (RowID , ECFID , Effective)
SELECTROW_NUMBER() OVER (ORDER BY ECF_ID,corrected_Effective_Date ),
ECF_ID,
corrected_Effective_Date
FROMEffectivereports.Effectivereports.ECFIndiaMaster
WHEREEmp_ID=@Emp_ID
February 5, 2008 at 9:40 am
If you're looking to improve your performance - you need to ditch the inner cursor. Selecting one row at a time, and process updates one row at a time MIGHT help with performance, but is actually more likelt to be LESS efficient than staying with the straight cursor. The retrieve and delete and re-insert is likely to make the table variable WORSE than the cursor.
You're essentially replacing a cursor with another cursor. Just because you aren't actually using the word cursor doesn't mean your process isn't doing the same thing.
In order to truly improve performance, you really want to work on removing BOTH loops. So that the process affects bunches of records at a time, not one at a time. Come on, fess up - what's behind the ------- Doing some process inside ?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 5, 2008 at 12:38 pm
The only way I know to do what you're asking is to move the second table variable to a separate proc, and call that proc with input parameters from the first table.
But, as pointed out, that's just a way to use a cursor inside a cursor without calling it that. To misquote The Bard, "A cesspool by any other name would smell ...."
The better bet is to try to find a way to do the whole thing as a set, instead of row by row.
- 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
February 5, 2008 at 4:52 pm
ditto Gsquared.
If you must use a cursor, use the SQL Server one, do NOT write your own in TSQL. I've never see a "hand written" cursor come anywhere close to the performance of the native cursor support. As a matter of fact, I've was a "hero" for changing a hand written cursor to a native one...shaved a process from a couple hours to 10 minutes... . The developer was told to NEVER use a cursor - he followed the rules given to him and wrote his own, then came to us for performance assistance.
Should have been told to avoid cursors if possible.
jg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply