How to improve performance for datetime filtering in SQL Server?

Solution 1:

Just a suggestion when it comes to indexes on datetime in msql is the index footprint impacts search times (Yes this seems obvious...but please read onward).

The importances to this when indexing on the datetime say for instance '2015-06-05 22:47:20.102' the index has to account for every place within the datetime. This becomes very large spatially and bulky. A successful approach that I've leveraged is create a new datetime column and populate the data by rounding the time to the hour and then building the index upon this new column. Example '2015-06-05 22:47:20.102' translates to '2015-06-05 22:00:00.000'. By taking this approach we leave the detailed data alone and can display it or use it by search on this new column which gives us approximately a 10x (at minimum) return on how fast results are returned. This is due to the fact that the index doesn't have to account for the minutes, seconds and millisecond fields.

Solution 2:

You need to look at your execution plan first to see what SQL Server is doing. More than likely, you just need add an index. Little conversions like this are almost never the reason why your query is slow. Indices are a good first stop for fixing queries.

You don't need to make this the clustered index. Making it the clustered index means that you don't need to do a lookup, but for only 100 rows, lookup is very fast. I would put datetime and subcategory into a nonclustered index, in that order.

If you are ordering, you should also make sure that's in an index. Since it only makes sense to use one index per table, you'll need to make sure all the relevant columns are in the same index, in the right order.

But first, get your actual execution plan!

Solution 3:

For better performance I suggest you create new indexes:

CREATE INDEX x1 ON LiveCity.dbo.Tags(Title) INCLUDE(ID_Tag)
CREATE INDEX x2 ON LiveCity.dbo.Tags(ID_Event, GmtStart_time, GmtStop_time) 
  INCLUDE(
          FavoriteCount, 
          ID_Channel, 
          ID_POI, 
          ID_SubCategory, 
          IDChanelEvent, 
          Start_time, 
          Title
          )
CREATE INDEX x ON LiveCity.dbo.POI(ID_POI, Latitude, Longitude) 
  INCLUDE(
          Address, 
          City, 
          Country, 
          gmtOffset, 
          Name, 
          Region
          )

This will help you avoid RID lookup operation and improve the overall performance of the query.