Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Dec 14, 1999

Very Slow Queries

Question:
I'm working on a database built for reporting purposes. Tables are updated once a month. Reporting queries are taking many hours to complete, some up to 26 hours.

The database has about 30 tables. Two of these tables have a little over 3 million rows; the major table has about 15 indexes on it.

I checked all indexes using the DBCC SHOWCONTIG and everything appears to be in good shape.

What should I be looking at to resolve this problem?

Answer:
I know that in SQL Server 6.5 there are several factors about the construction of the index that can have an effect on performance. These are the data type of the index, the data length of the column in the index, the order of columns in the index and the selectivity or cardinality of the index (that is, the number of distinct values).

In SQL 7, these aren't as critical, but I'll bet integers are still faster than characters :) .

Another thing that springs to mind is that you mention that the database is built for reporting. Are you building cubes in an OLAP product or making do in a transactional database set aside for reporting? It sounds as if more of the data warehousing techniques using data staged and aggregated before it is reported on may be of help to you.

You can write to me offline about this further, if you like.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date