August 21, 2006 at 6:34 am
Hi All,
I have a procedure with following structure:
cursor with Query
Select Id from Table;
Open Cursor
Process 15 procedures and pass ID as paramater.
Close Cursor
This procedure is taking arround 1:30 hrs .
I want to eliminate the cursor and want to use something like array or (PLSQL table in Oracle).
Thanks for advance,
Regards
Sumit Soni
August 21, 2006 at 7:44 am
Hi Sumit,
are you calling the same procedure 15 times or are you calling 15 different procedures within the cursor?
How many records are there in the Table?
I'd recommend that you eliminate the cursor entirely if that's possible by looking at the code inside the procedure and trying to run it as one single set-based operation.
I don't know much about Oracle but a cursor is essentially an array which you iterate through.
Either way, whether it's Oracle or SQL Server, it's a database so you're always going to lose performance if you start doing things iteratively so I'd advise you stick to set-based operations if you can.
August 22, 2006 at 6:49 pm
sql svr ist not as fast in cursor mode as oracle, but cursing through a loop n times calling 15 procedures (is that your plan?) should not be significantly slower.
sql svr doesn't have any collections like index-by tables. The nearest thing in SQL Svr is to use a datatype table, you'll have to create your own index tho'. sqlservercentral had a good article some time ago about improving on a cursor using an ordinary while loop in conjuntion with datatype table.
Maybe someone else can point you to it.
Cheers,
Win
August 29, 2006 at 11:49 am
DECLARE
@IDlist TABLE (
ID int NOT null,
seq int IDENTITY NOT NULL PRIMARY KEY CLUSTERED)
DECLARE @ID int, @seq int
INSERT INTO @IDlist (ID) SELECT ID FROM table
SET @seq = @@ROWCOUNT
WHILE @seq > 0 BEGIN
SELECT @ID = ID, @seq = @seq-1 FROM @IDlist WHERE seq = @seq
EXEC proc1 @ID
EXEC proc2 @ID
EXEC proc3 @ID
...
EXEC proc15 @ID
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply