|
|
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
Navigation:
[Reply to this message]
|