Database

When to avoid Between statement in sql query


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.

Function: GetDates

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

Query

SELECT * from TableA  a INNER JOIN GetDates('20120101','201200205') 
d on d.Date = a.executiondate

sql profiler stats for 631203 records

Query Type CPU Reads Duration
Between 247792 213318 263397
Without Between 20436 7617 39106
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s