Geek Answers Handbook

How to reduce the response time of a simple select request?

MarketPlane spreadsheet contains over 60 million rows.

When I need the total number of aircraft from a specific date, I fulfill this request, which takes more than 7 minutes. How can I reduce this time?

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

I implemented everything that was mentioned in your links, even now I implemented With(nolock) , which reduce the response time to 5 minutes.

+3
sql sql-server tsql sql-server-2008
source share
10 answers

You need to create an index in the table, or perhaps split the table by date.

You can also watch

SQL Server 2000/2005 Indexed Configuration Preview and Optimization Optimization

SQL Server Indexed Views

+3
source share

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

0
source share

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

If your main request is to select a data range (based on a selection), adding or modifying CLUSTERED INEDX will greatly improve the query time.

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

By default, SQL-Server creates the primary key as a clustered index, which is different from the transaction point of view, but if the focus is to retrieve data, then changing this default value is of great importance.

 CREATE CLUSTERED INDEX name ON MarketPlan (LaunchDate DESC)

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

0
source share

There are some great suggestions here, if all else fails, consider a little denormalization, create another table with a total count, and update it with a trigger. If you have more queries of this type, consider OLAP

0
source share

Your particular query does not require a clustered key in the date column. In fact, it will work better with a non-clustered index with a leading date column, because you do not need to do a key search in this query, so a non-clustered index will cover more compact than a clustered one (it implicitly includes clustered key columns). If you indexed it correctly and still aren't doing it, this is most likely fragmentation. In this case, defragment the index and try again.

0
source share

Create a new index as follows:

CREATE INDEX xLaunchDate on MarketPlan (LaunchDate, primaryKeyColumn)

Check out this nice article on how an index can improve performance.

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

0
source share

"WHERE LaunchDate> @date"

Is the @date parameter value defined in the same batch (or transaction or context)?
If not, this will lead to a clustered scan of indexes (all rows) instead of a clustered index (only rows satisfying the WHERE clause) if its value comes from outside the current batch (for example, the input parameter of a stored procedure or udf). The query cannot be fully optimized by the SQL Server optimizer (at compile time), which leads to a full table scan, since the parameter value is known only at run time

Update: Comment on answers suggesting OLAP.
OLAP is just a concept, SSAS cubes are just one of the possible ways to implement OLAP.
This is a convenience, not an obligation to obtain / use the OLAP concept.
You do not use SSAS to use the OLAP concept.
See For example, Simulated OLAP

Update2: answer the question in the comments:

  • MDX performance compared to T-SQL

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

0
source share

The simplest thing you can do:

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

This ensures that only the LaunchDate index is retrieved.

Also (it depends on your execution plan), I saw instances (but not specific to SQL Server) in which> scanned the table, and BETWEEN used the index. If you know the top date, you can try WHERE LaunchDate BETWEEN @date AND <<Literal Date>> .

0
source share

How wide is the table? If the table is wide (i.e., Many columns of (n) char, (n) varchar or xml), there can be a significant amount of I / O, causing the query to run slowly as a result of using a clustered index.

To determine if IO causes a long request time, do the following:

  • Create a non-clustered index only in the LaunchDate column.

  • Run a query that 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 indicative hints, and I offer only this hint just to prove that IO causes long requests.

0
source share

There are two ways to do this.

0
source share

More articles:


All Articles