Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  sqlserver performance

Author  Topic  dith Starting Member 24 Posts Posted - 2001-01-23 : 17:54:13 Hi! I have this sql code that i run on sql7 query analyzer... -- td_mfgname has 200 records -- techspec has 1.3 million records -- my sql server runs on pentium III 450Mhz dual processors, 512MB RAM, Raid 5 configuration totalling 30GB HD. it takes around 8 minutes to create each table. computing everything, it would take me around 27 hours just to run this code. is this normal? -----sql code----- select distinct(left(mfg_name,2)) as mfg_name into td_mfgname from products order by left(mfg_name,2) declare @table char(4), @letter char(2) declare td_cursor cursor for select * from td_mfgname OPEN td_cursor FETCH NEXT FROM td_cursor INTO @letter WHILE @@FETCH_STATUS = 0 BEGIN SET @table = 'td' + @letter EXEC ('if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].['+@table+']'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) drop table dbo.['+@table+']') EXEC ('SELECT *, 3 as dist_id INTO ['+@table+'] FROM techspec where (left(mfg_name,2)) = '''+@letter+'''') EXEC ('CREATE INDEX i_suppart ON ['+@table+'](part_no) ON [PRIMARY]') EXEC ('CREATE INDEX i_part ON ['+@table+'](mfg_part) ON [PRIMARY]') EXEC ('CREATE INDEX i_name ON ['+@table+'](mfg_name) ON [PRIMARY]') EXEC ('CREATE INDEX i_partname ON ['+@table+'](mfg_part, mfg_name) ON [PRIMARY]') FETCH NEXT FROM td_cursor INTO @letter END CLOSE td_cursor DEALLOCATE td_cursor GO DROP TABLE td_mfgname -----end of sql code-----