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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may

man on floor with data

DevX Quick Guide to Data Ingestion

One of the biggest trends of the 21st century is the massive surge in internet usage. With major innovations such as smart technology, social media, and online shopping sites, the internet has become an essential part of everyday life for a large portion of the population. Due to this internet

payment via phone

7 Ways Technology Has Changed Traditional Payments

In today’s digital world, technology has changed how we make payments. From contactless cards to mobile wallets, it’s now easier to pay for goods and services without carrying cash or using a checkbook. This article will look at seven of the most significant ways technology has transformed traditional payment methods.