I have a query select, with date between used in the where condition, which is taking a long time to execute and render data. If i run without any date conditions against the entire table is much faster.
where rm.ProgID <> 70
and (rm.progid = 71)
and rm.rmloggeddate >= CONVERT(varchar(8), '04/01/16', 112)
and rm.rmloggeddate < CONVERT(varchar(8), dateadd(day, 1, '04/11/16'), 112)
rmloggeddate is a datetime column in table.
Can you please advice any other better method to run the query with in a date range.
Not clear from your example whether you are using mm/dd/yy convention or dd/mm/yy convention. Does '04/01/16' represent Apil 1 2016, or does it represent January 4, 2016?
Assuming it is mm/dd/yy (i.e., April 1, 2016), change your where clause to
where rm.ProgID <> 70
and (rm.progid = 71)
and rm.rmloggeddate >= CONVERT(datetime, '04/01/16', 1)
and rm.rmloggeddate < CONVERT(datetime, dateadd(day, 1, '04/11/16'), 1)
Hi james that is correct MM/DD/YY format i used. i used your condition, still taking too long. The moment i remove the dates clause and the run the query straight against full table, result is coming in 6 seconds.
Is it good to create a composite index with both columns based on the Progid, rmloggeddate?
When creating index on a datetime, i want to make it as straight date only excluding the time part.
Examine what indexes you have on the table now. An index on progid + rmloggeddate would help. However, I don't think you can create an index on a datetime column and have it index only the date part. But there is no reason to restrict it to date part only. You are not saving any index storage space even if you were able to do that..
I doubt that progid is very selective, so if that is correct then it would be better to have the index on rmloggeddate. You could include progid too (i.e. put rmloggeddate first, and then progid second, or only have progid as an INCLUDE column.