2

I have a table on a MS Azure SQL DB with 60,000 rows that is starting to take longer to execute with a SELECT statement. The first column is the "ID" column which is the primary key. As of right now, there is no other indexes. The thing about this table is the rows are based on recent news articles, therefore the last rows in the table are always going to be accessed more than the older rows.

If possible, how can I tell SQL Server to start querying at the end of the table working backwards when I do a SELECT operation?

Also, what can I do with indexes to make reading from the table faster with the last rows as the priority?

Nathan Tuggy
  • 2,237
  • 27
  • 30
  • 38
user2653364
  • 115
  • 2
  • 8
  • Can you post your DDL? – Felix Pamittan Jun 23 '15 at 01:14
  • 2
    If you have a datetime column... index that column and make sure that you have that field in the where clause. If you don't... add one and make an index for that. – TheMadDBA Jun 23 '15 at 01:17
  • wewesthemenace - I would but SSMS throws a "Specified cast is not valid. (Microsoft.SqlServer.Smo)" error whenever I click on the "CREATE TO" menu option. TheMadDBA - I'll do that. I currently don't have one. Thanks! – user2653364 Jun 23 '15 at 01:24

1 Answers1

5

Typically, the SQL Server query optimizer will choose the data access strategy based on the available indexes, data distribution statistics & query. For example, SQL Server can scan an index forward, backward, physical order & so on. The choice is determined based on many variables.

In your example, if there is a date/time column in the table then you can index and use that in your predicate(s). This will automatically enable use of that index if that is the most selective one.

Alternatively, you can partition the table based on a column and access most recent data based on the partitioning key. This is common use of partitioning with a rolling window. With this approach, the predicate in your queries will specify the partitioning column which will help the optimizer pick the correct set of partitions to scan. This will dramatically reduce the amount of data that needs to be searched since partition elimination happens before execution depending on the query plan.

Umachandar - Microsoft
  • 484
  • 3
  • 5