Posted by Erland Sommarskog on 03/01/06 01:02

sjoshi (sjoshi@ingr.com) writes:
> 4. Loops thru. the cursor examining each oid and then calls a
> StoredProc to update another table
>
> declare @objectOid uniqueidentifier
> declare @tempOid uniqueidentifier
> open DanglingRelation
> fetch DanglingRelation into @ObjectOid
>
> while ( @@fetch_status = 0)
> begin
> set @tempOid='00000000-0000-0000-0000-000000000000'
>
> if left(@ObjectOid,8)='00000002'
> select @tempOid=oid from COREToDoList where Oid=@ObjectOid
> else if left(@ObjectOid,8)='00000003'
> select @tempOid=oid from COREToDoRecord where Oid=@ObjectOid
> else if left(@ObjectOid,8)='00000004'
> else
> select @tempOid=oid from COREBaseClass where Oid=@ObjectOid
>
> if @tempOid = '00000000-0000-0000-0000-000000000000'
> BEGIN
> exec CORESetObjectIntegrity @ObjectOid, 2
> END
> fetch DanglingRelation into @ObjectOid
> end
> close DanglingRelation
> deallocate DanglingRelation
> drop table [#tSP3DCoreOid]
>
> Now the #tSP3DCoreOid temp table can sometimes get as many as 7.5
> million rows and this is making the batch fail on SQLServer as it runs
> out of memory.

I guess it's the cursor above you want to optimize. Since I don't know
what's in thar stored procedure, it's difficult to give very detailed
suggestions. Obviously you would have to rewrite the procedure to work
with many rows as a time to be able to eliminate the cursor.

Since you appear to be working with some hierarchy, it may be difficult
to completely avoid iteration, but if you can do an complete
level at time you can win a lot.

I'k not sure that I understand how the uniqueidentifier values work
here, but I get a feeling that they add an extra level of complexity.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]