I was playing with sql queries for performance improvement and got to know that use of Between statement in Sql query for table with huge data degrades performance because it scans all the records within range.
Let’s take a scenario where Table A with columns like id, executiondate, name etc. Suppose this table has millions of records.
If we run query like this:
SELECT * FROM TABLEA WHERE executiondate BETWEEN '20120101' AND '20120205'
This statement scans all the dates in table and it can take more time to execute.
We know that ‘=’ operator is faster that BETWEEN statement so to avoid use of BETWEEN keyword. I created a temp table which has dates and put INNER JOIN on dates in TableA.
First I created a function GetDates which returns results of dates.
CREATE FUNCTION [dbo].[GetDates] ( @StartDate [datetime] , @EndDate [datetime] ) RETURNS @datesTable TABLE ( [Date] DATETIME NULL ) WITH EXECUTE AS CALLER AS BEGIN WHILE @StartDate <= @EndDate BEGIN INSERT @datesTable VALUES ( @StartDate ) SET @StartDate = DATEADD(day, 1, @StartDate) END RETURN END
SELECT * from TableA a INNER JOIN GetDates('20120101','201200205') d on d.Date = a.executiondate
sql profiler stats for 631203 records