-1

My DB has 7M records with 30+ variables, the below query is to filter data based on daily basis which takes me around approximately 12 mins to return the results

select customername, productname from xyztable where (datatime between '2019-05-17 00:00:00.000'
and '2019-05-18 00:00:00.000')

The same data I am also in need to filter it based on hourly basis as below, 

select customername, productname from xyztable where (datatime between '2019-05-17 00:00:00.000'
and '2019-05-17 01:00:00.000')

I was expecting query result to be quicker on hourly basis since the data will be very less. But it takes the same time as that of daily basis.

So basically both hourly and daily basis query take 12 mins to return the result.

Is there anyway I could make the hourly result return much quicker than that of daily basis?

hdhanarajan
  • 3
  • 3
  • 2
    are there any indexes on xyztable? if both queries take the same time seems there is table scan executed each time – avb May 18 '20 at 05:56
  • We'd need to know full table definition, including indexes, and see an execution plan in order to assist. – Dale K May 18 '20 at 05:56
  • no indexes as such – hdhanarajan May 18 '20 at 08:33
  • 12 minutes to read a table with 7 million records seems like a long, long time -- even without an index. Either your rows are very wide or your system is severely underpowered or what you are calling a "table" is really a "view". – Gordon Linoff May 18 '20 at 12:06

2 Answers2

1

This is because you don't have index on field "datatime" so SQL Server must read the whole "xyztable" to find records. Try to create this index:

CREATE INDEX IX_datatime_xyztable ON xyztable(datatime)
Máté Farkas
  • 146
  • 2
0

Without an execution plan I can only assume that there is a table scan to read the data. You can change this to an index seek by creating a covering index:

create index ix_MyIndexName
on xyztable (datatime)
include (customername, productname)

Your query will then look up the first value for datatime and then scan to the last qualifying row. As customername and productname are included in the index, there will be no need for a lookup in the plan.

MJH
  • 1,710
  • 1
  • 9
  • 19