SQL performances of selecting recently modified records Solr
2
votes
2
answers
297
views
I have a SQL Server 2916 database, and Solr.
The Solr indexer runs often such queries:
select book_collection from books group by book_collection having max(updated_on) > '2017-04-04 09:50:05'
The column updated_on is updated by a trigger on insert/update; at every run of the Solr incremental indexer (every 10 minutes), many queries like the above get the latest modified records and reindexes them.
This table, for instance, has about one million rows and at every time the query would return 10-20 rows maximum.
These queries end up in the list of the most expensive queries run on the database, so I would like to optimize them.
My questions:
1) Would a timestamp column perform better than a datetime column?
2) If I changed the query like this, would it be more efficient?
select distinct book_collection from books where updated_on > '2017-04-04 09:50:05'
The first query plan is the original one, the second is modified by me. The fact that the second plan asks for an index, and the first one doesn't, really suggests that the modified query can use an index, if present, and is therefore better.


Asked by carlo.borreo
(1477 rep)
Apr 4, 2017, 08:53 AM
Last activity: Apr 4, 2017, 04:10 PM
Last activity: Apr 4, 2017, 04:10 PM