0

I have a table call_log has about 10 million records in it.

I have query like:

select dnis, COUNT(1) as total, SUM(DATEDIFF(minute, time_in, time_out))
    from call_log 
    where time_in bewteen @dt1 and @dt2
    group by dnis

normally the date range will be about 1 month, and the total records during the selected period is about 15,000. Currently the query runs very slow (26 seconds after clear the cache).

I have index on the column time_in + dnis.

Is there any way to optimize it?

Thanks,

hunch_hunch
  • 2,283
  • 1
  • 21
  • 26
alan shen
  • 29
  • 2
  • 1
    Are the statistics on the table updated?. So, you have an index with time_in and dnis as keys, can you include the time_out column in it? – Lamak Mar 23 '15 at 20:18
  • 1
    Show your [execution plan](http://stackoverflow.com/a/7359705/2055998) – PM 77-1 Mar 23 '15 at 20:20
  • Try to use an index on dnis, time_in and time_out in this order. – Zohar Peled Mar 23 '15 at 20:20
  • 1
    @ZoharPeled That is not the optimum order. Should be `time_in` first to allow a seek on the range then could just add the other two columns as included columns as `dnis` won't be in any useful order for this query.(would need an `=` on time_in for the composite index to be useful) – Martin Smith Mar 23 '15 at 20:27
  • 1
    @MartinSmith is correct. For some more info on this, see http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/index-merge-performance (and http://use-the-index-luke.com/sql/myth-directory/most-selective-first is also relevant). – gregmac Mar 23 '15 at 20:30
  • I add the time_out to the index, so the index is on (time_in, time_out, dnis), it is much faster, why I need to include time_out in the index? thanks a lot! – alan shen Mar 23 '15 at 20:30
  • as @MartinSmith said, you don't need every column as keys of the index, only `time_in`, the rest can be included columns – Lamak Mar 23 '15 at 20:31
  • 1
    Once you included that column in the index, then it's a covering index of your query. Meaning that the query doesn't need to go to the table, it can use only the index – Lamak Mar 23 '15 at 20:32
  • @MartinSmith Thanks for the correction. I thought that the dnis should be the first since it's in the group by clause. – Zohar Peled Mar 23 '15 at 20:34
  • @ZoharPeled - Putting it first means that it could go into a stream aggregate without a sort but the downside would be that it reads the whole 10 million records not just the 15,000 of interest. – Martin Smith Mar 23 '15 at 20:35
  • @MartinSmith thanks. It's been about 1.5 years since I've actually worked with sql server so I'm a bit rusty. – Zohar Peled Mar 23 '15 at 20:37

0 Answers0