Hi ,
My belwo script takes long time to run. What can i do about its performance tunning. This scripts read values from table t_emp . It search for that empid
ie comp_guid across the database . ie each table and each of database so find out whether that empid is used for no in the database if its used it insert into temp table . My t_emp is having row 400-900 rows and its long time what can i do to fine tune it .
eclare @objid int, @colid int
declare @stmt nvarchar(2000)
declare @comp_guid int
declare @objid2 int
declare c1 cursor
for select comp_guid from t_emp
--set @guid=30500
if object_id('tempdb..#t') is not null
drop table #t
create table #t(objid int, name sysname, clname sysname, cnt int)
open c1
FETCH NEXT FROM c1 INTO @comp_guid
WHILE (@@FETCH_STATUS = 0)
begin
set @objid=0
while @objid is not null
begin
select @objid=min(id) from sysobjects b
where id > @objid
and xtype <> 'S'
and type='U' and user_name(uid) = 'dbo'
set @colid=0
while @colid is not null
begin
select @colid=min(colid) from syscolumns
where colid > @colid and id=@objid and name like '%_guid'
and xtype in (select xtype from systypes where name in ('bigint','decimal','float','int','money','numeric ','real','smallint','smallmoney','tinyint'))
if (@colid is not null)
begin
select @stmt = 'insert into #t select top 1 ' + convert(nvarchar(500),@Comp_guid) + ' ,''' + object_name(@objid) + ''',''' + col_name(@objid,@colid) + ''' nm , 1 cnt from ' + object_name(@objid) + ' where ' + col_name(@objid,@colid) + '=' + convert(nvarchar(500),@comp_guid )
exec sp_executesql @stmt
end
print @comp_guid
end
end
FETCH NEXT FROM c1 INTO @comp_guid
end
CLOSE c1
DEALLOCATE c1
thanks