Posted by sjoshi on 02/28/06 20:36

I have a long sql batch that does this:

1. Gets a list of all tables in user database that start with name CORE

declare @tablename varchar(30),
@command varchar(2000),
@cnt integer

declare GetCOREOids cursor for
select sysobjects.name
from sysobjects
where ( OBJECTPROPERTY(sysobjects.id,N'IsUserTable')=1 ) and
( sysobjects.name like 'CORE%' or sysobjects.name =
'CMNSTRStructGeomBasicPort') and
( sysobjects.name not like 'CORESpatialIndex%') and (sysobjects.name
not like 'COREDeletedObjects%') and
( sysobjects.name not in
('CORERelationOrigin','CORERelationDestination') ) and
( sysobjects.id in ( select id from syscolumns where name = 'oid') )
for read only

2. Populates a temporary table with distinct oids from the list.

create table [#tSP3DCoreOid] (oid UNIQUEIDENTIFIER)

open GetCOREOids
fetch GetCOREOids into @tablename

while ( @@fetch_status = 0)
begin
select @command = 'insert into [#tSP3DCoreOid] select distinct oid from
' + @tablename
execute(@command)
--print @tablename + ' rows: ' + convert(char,@@rowcount)

fetch GetCOREOids into @tablename

end /* while */
--Clean up
close GetCOREOids
deallocate GetCOREOids

3. Creates a cursor to get "invalid" oids from 2 other tables

declare DanglingRelation cursor for
select oid from [dbo].[CORERelationOrigin] RO where not exists
( select oid from [#tSP3DCoreOid] where oid = RO.oid )
union
select oid from [dbo].[CORERelationDestination] RD where not exists
( select oid from [#tSP3DCoreOid] where oid = RD.oid )

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 think the problem is the cursor here and would like some
feedback/tips on how to best optimise it.

thanks a lot
Sunit

[Back to original message]