How to decrease response time of a simple select q

问题:

MarketPlane table contains more than 60 million rows.

When I need the total number of plane from a particular date, I execute this query which takes more than 7 min. How can I reduce this time ?

SELECT COUNT(primaryKeyColumn) 
FROM   MarketPlan 
WHERE  LaunchDate > @date

I have implemented all things mentioned in your links even now I have implemented With(nolock) which reduce response time is to 5 min.

回答1:

You will have to create an index on the table, or maybe partition the table by date.

You might also want to have a look at

SQL Server 2000/2005 Indexed View Performance Tuning and Optimization Tips

SQL Server Indexed Views

回答2:

Does the table in question have an index on the LaunchDate column? Also, did you really mean to post LaunchDate>@date?

回答3:

Assuming SQL-Server based on @date, although the same can be applied to most databases.

If your primary query is to select out a range of data (based on sample), adding, or altering the CLUSTERED INEDX will go a long way to improving query times.

See: http://msdn.microsoft.com/en-us/library/ms190639.aspx

By default, SQL-Server creates the Primary Key as the Clustered Index which is great from a transactional point of view, but if your focus is to retrieve the data, then altering that default makes a huge difference.

CREATE CLUSTERED INDEX name ON MarketPlan (LaunchDate DESC)

Note: Assuming LaunchDate is a static date value and is primarily inserted in increasing/sequential order to minimize index fragmentation.

回答4:

There are some fine suggestions here, if all else fails, consider a little denormalization, create another table with the cumulative counts and update it with a trigger. If you have more queries of this nature think about OLAP

回答5:

Your particular query does not require clustered key on the date column. It would actually run better with nonclustered index with the leading date column because you don't need to do key lookup in this query, so the nonclustered index would be covering and more compact than clustered (it implicitly includes clustered key columns). If you have it indexed properly and it still does not perform it is most likely fragmentation. In this case defragment the index and try again.

回答6:

Create a new index like this:

CREATE INDEX xLaunchDate on MarketPlan (LaunchDate, primaryKeyColumn)

Check this nice article about how an index can improve the performance.

http://blog.sqlauthority.com/2009/10/08/sql-server-query-optimization-remove-bookmark-lookup-remove-rid-lookup-remove-key-lookup-part-2/

回答7:

"WHERE LaunchDate > @date"

Is the value of parameter @date defined in the same batch (or transaction or context)?
If not, then this would lead to Clustered Index Scan (of all rows) instead of Clustered Index Seek (of just rows satisfying WHERE condition) if its value is coming from outside of current batch (as, for example, input parameter of stored procedure or udf function).
The query cannot be fully optimized by SQL Server optimizer (at compile time) leading to full table scan since the value of parameter is known only at run-time

Update: Comment to answers proposing OLAP.
OLAP is just concept, SSAS cubes is just one of the possible ways of OLAP implementation.
It is convenience, not obligation in getting/using OLAP concept.
You have not use SSAS to use OLAP concept.
See, for ex., Simulated OLAP

Update2: Comment to question in comments to answer:

• MDX performance vs. T-SQL

MDX is an option/convenience/feature/functionality provided by SSAS (cubes/OLAP) not obligation

回答8:

The simplest thing you can do is:

SELECT COUNT(LaunchDate) 
FROM   MarketPlan 
WHERE  LaunchDate > @date

This will guarantee you index-only retrieval for any LaunchDate index.

Also (this depends on your execution plan), I have seen instances (but not specific to SQL Server) in which > did a table scan and BETWEEN used an index. If you know the top date you might try WHERE LaunchDate BETWEEN @date AND <<Literal Date>>.

回答9:

How wide is the table? If the table is wide (ie: many columns of (n)char, (n)varchar or xml) there might be a significant amount of IO causing the query to run slowly as a result of using the clustered index.

To determine if IO is causing the long query time perform the following:

1. Create a non-clustered index only on the LaunchDate column.

2. Run the query below which counts LaunchDate and forces the use of the new index.

   SELECT COUNT(LaunchDate)

   FROM MarketPlan WITH (INDEX = TheNewIndexName)

   WHERE LaunchDate > @date

I do not like to use index hints and I only suggest this hint only to prove if the IO is causing long query times.

回答10:

There are two ways to do this

1. First create a clustered index on the date column, since query is date range specific, all the data will be in the actual order and this will avoid having to scan through all records in the table

2. You can try using Horizontal partioning, this will affect your existing table design but this is the most optimal way to do so, see this http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/