Problem with dbo.sysindexkeys in SQL 2000

Hello,

I am experiencing some performance issues with the dbo.sysindexkeys.

I wonder if anyone can help me create an index on it?

Thanx!

SQL script:

set nocount on
set ansi_warnings off
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Create test database'
print
'--------------------------'

use master
go

if not db_id('test') is null
drop database test
go

create database test
go

use test
go

--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Create 4000 tables in test database'
print
'--------------------------'

declare @.i_iter int

set @.i_iter = 0
while @.i_iter < 4000
begin

if @.i_iter % 100 = 0
print 'Table -> ' + cast(@.i_iter as char(10))

exec ('
create table TABLE_' + @.i_iter + '
( COLUMN_0 int not null,
COLUMN_1 int not null,
COLUMN_2 datetime not null,
COLUMN_3 datetime not null,
COLUMN_4 varchar(100) not null,
COLUMN_5 smallint not null,
COLUMN_6 bit not null,
constraint TABLE_' + @.i_iter + '_PK primary key (COLUMN_0,
COLUMN_1, COLUMN_2)
)
create index TABLE_' + @.i_iter + '_I1 on TABLE_' + @.i_iter + '
(COLUMN_4, COLUMN_5)
')

set @.i_iter = @.i_iter + 1
end
go
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Dump dbo.sysindexkeys in temp. table and create a clustered
index on it'
print
'--------------------------'

if not object_id('dbo.t_sink') is null
drop table dbo.t_sink
go
select * into dbo.t_sink from dbo.sysindexkeys
alter table dbo.t_sink add constraint t_sink_pk primary key ([id] ,
[indid], [colid])
go

print
'--------------------------'
print ' Create a pivot table with all columns in the indexes and
primary keys using this'
print ' temp table'
print
'--------------------------'

if not object_id('tempdb..#dump') is null
drop table #dump
go

declare @.time datetime
set @.time = getdate()

select sobj.name as table_name,
sind.name as index_name,
max(case sink.keyno when 1 then scol.name end) as c01,
max(case sink.keyno when 2 then scol.name end) as c02,
max(case sink.keyno when 3 then scol.name end) as c03,
max(case sink.keyno when 4 then scol.name end) as c04,
max(case sink.keyno when 5 then scol.name end) as c05,
max(case sink.keyno when 6 then scol.name end) as c06,
max(case sink.keyno when 7 then scol.name end) as c07,
max(case sink.keyno when 8 then scol.name end) as c08,
max(case sink.keyno when 9 then scol.name end) as c09
into #dump
from dbo.sysobjects sobj,
dbo.sysindexes sind,
dbo.t_sink sink,
dbo.syscolumns scol
where sobj.xtype = 'u'
and sind.id = sobj.id
and indexproperty(sind.id, sind.name, 'IsAutoStatistics') = 0
and indexproperty(sind.id, sind.name, 'IsStatistics') = 0
and sink.id = sobj.id
and sink.indid = sind.indid
and scol.id = sobj.id
and scol.colid = sink.colid
group by sobj.name,
sind.name
order by sobj.name,
sind.name

print 'Rows: ' + cast(@.@.rowcount as char(10))

set @.time = getdate() - @.time
print 'Time: ' + convert(char(25), @.time, 114)
go

if not object_id('dbo.t_sink') is null
drop table dbo.t_sink
go
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Create a pivot table with all columns in the indexes and
primary keys using the'
print ' system tables'
print
'--------------------------'

if not object_id('tempdb..#direct') is null
drop table #direct
go

declare @.time datetime
set @.time = getdate()

select sobj.name as table_name,
sind.name as index_name,
max(case sink.keyno when 1 then scol.name end) as c01,
max(case sink.keyno when 2 then scol.name end) as c02,
max(case sink.keyno when 3 then scol.name end) as c03,
max(case sink.keyno when 4 then scol.name end) as c04,
max(case sink.keyno when 5 then scol.name end) as c05,
max(case sink.keyno when 6 then scol.name end) as c06,
max(case sink.keyno when 7 then scol.name end) as c07,
max(case sink.keyno when 8 then scol.name end) as c08,
max(case sink.keyno when 9 then scol.name end) as c09
into #direct
from dbo.sysobjects sobj,
dbo.sysindexes sind,
dbo.sysindexkeys sink,
dbo.syscolumns scol
where sobj.xtype = 'u'
and sind.id = sobj.id
and indexproperty(sind.id, sind.name, 'IsAutoStatistics') = 0
and indexproperty(sind.id, sind.name, 'IsStatistics') = 0
and sink.id = sobj.id
and sink.indid = sind.indid
and scol.id = sobj.id
and scol.colid = sink.colid
group by sobj.name,
sind.name
order by sobj.name,
sind.name

print 'Rows: ' + cast(@.@.rowcount as char(10))

set @.time = getdate() - @.time
print 'Time: ' + convert(char(25), @.time, 114)
goherman (herman@.hotmail.com) writes:
> I am experiencing some performance issues with the dbo.sysindexkeys.
> I wonder if anyone can help me create an index on it?

I guess you could if you change the configuration parameer "allow updates
to system tables", but I have no idea wether SQL Server would actually
look at the index.

Anyway, this query executes almosts as fast as your query with
the temp table:

select sobj.name as table_name,
sind.name as index_name,
index_col(sobj.name, sind.indid, 1) as c01,
index_col(sobj.name, sind.indid, 2) as c02,
index_col(sobj.name, sind.indid, 3) as c03,
index_col(sobj.name, sind.indid, 4) as c04,
index_col(sobj.name, sind.indid, 5) as c05,
index_col(sobj.name, sind.indid, 6) as c06,
index_col(sobj.name, sind.indid, 7) as c07,
index_col(sobj.name, sind.indid, 8) as c08,
index_col(sobj.name, sind.indid, 9) as c09
--into #direct
from dbo.sysobjects sobj
join dbo.sysindexes sind ON sind.id = sobj.id
where sobj.xtype = 'U'
and indexproperty(sind.id, sind.name, 'IsAutoStatistics') = 0
and indexproperty(sind.id, sind.name, 'IsStatistics') = 0
order by sobj.name, sind.name

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp