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  Transact-SQL (2000)  Derive or Temp or other

Author  Topic  snaayk Starting Member 17 Posts Posted - 2004-08-19 : 16:22:55 I have a table with about 500,000 records and I add about ~2000 records a day. I need to create a crosstab query with about 14 "columns"; Unfortunately, I can't use the multitude of crosstab sprocs available since each "column" uses a variety of criteria. I'm currently using a derived table strategy. I seem to have the right indices built since they all use the index save for 2 of the 14 derived tables. The first time I run the query it takes about 18 seconds, subsequent queries take 10 seconds. I'm thinking, are there other options? Temp tables with more indices? The data is updated only once a day, would I be better off building a new table daily with more indices? And just drop prior to the import and recreate after the import? IS there another option? TIA for any help.