SQLTeam.com | Weblogs | Forums

Procedure takes more time to execute and allocate max memory


#1

Theory -

I have written a procedure where i Find Members Id via Loop and check certain condition and If condition true
Then use insert statement.
I have use basic code
Like..

create procedure procName
as
Begin
select ids from members order by ids
Begin
While(condition)
Begin
insert statement
commit
end
end
else
begin
rollback
end

end

in Member Table there is One Lakh Sixty Seven Thousand Data.

when i execute this procedure, mean while
i execute members query , it does not execute till my procedure complete process.

Procedure take time 5 to 6 hrs.

I seem that due to begin- end and Commit or rollback , reason it locks another process.
and it takes more memory till execution the process.


Sir,
please help me to reduce time and minimum memory allocation
and select query execute when procedure runs

please help


#2

Do you have to do this in a loop? By looking at what you have it looks like you could do this set based.


#3

Yes in while Looping
checking different condition
and then write in a table