CODEWITHSUNDEEP
databasesql-server-2008query-optimization
Michael
Michael

Reputation: 14218

Select query optimisation

I have a large table with ID, date, and some other columns. ID is indexed and sequential.

I want to select all rows after a certain date. Given that the IDs are sequential, if the rows are ordered by ID in decreasing order, once the first row that fails the date test there's no need to carry on checking. How can I make use of the index to optimise this?

Upvotes: 0

Views: 110

Answers (2)

Danielle Paquette-Harvey
Danielle Paquette-Harvey

Reputation: 1781

You could do something like this:

With FirstFailDate AS
(
-- You start by selecting the first fail date
SELECT TOP 1 * FROM YOUR_TABLE WHERE /* DATE TEST FAILING */ ORDER BY ID DESC
)
SELECT *
FROM YOUR_TABLE t
-- Then, you join your table with the first fail date, and get all the records
-- that are before this date (by ID)
JOIN FirstFailDate f
  ON f.ID > t.ID

Upvotes: 3

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

I don't think there is a good "legal" way to do this without actually indexing date.

However, you could try something like this:

  • Issue the following query to the DBMS: SELECT * FROM YOUR_TABLE ORDER BY ID DESC.
  • Start fetching the rows in your client application.
  • As you fetch, check the date.
  • Stop fetching (and close the cursor) when the date passes the limit.

The idea is that DBMS sometimes doesn't have to finish the whole query before starting to send the partial results to the client. In this case, the hope is that the DBMS will perform an index scan on ID (due to the ORDER BY ID DESC), and you'll be able get the results as it happens and then stop it before it has even finished.

NOTE: If your DBMS gives you an option to balance between getting the first row fast, versus getting the whole result fast, pick the first option (such as /*+ FIRST_ROWS */ hint under Oracle).

Of course, perform measurements on realistic amounts of data, to make sure this actually works in your particular situation.

Upvotes: 1

Related Questions