How to avoid cursors

[sjoshi]

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

[Erland Sommarskog]

sjoshi (sj****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Doug]

why do you have 7.5 million tables in a single database named
core???????

and, you are printing something from each one??????

perhaps you might be better served to have ONE or two core tables?

If nothing else, delete or rename some of them. If nothing else,
create yet another new table name of the ones that have been processed,
and quit processing them over and over again.

if you must process them all each time, then use "top 10,000" to only
grab 10,000 at a time, and keep track of hte ones you ahve done in a
temp table for that particular run, so you don't do them twice.

[sjoshi]

There are no 7.5 million rows in a database. These rows get inserted
into the temporary table #SP3DCoreOID after it gets all the distinct
ones from all the CORE named tables. The stored procedure,
CORESetObjectIntegrity just inserts/updates a row in another table.
The oids are of type GUIDS.

thanks
Sunit

[Doug]

Uncle.

Why would you want to do this??????????