Technology Corner

Home » Database » When to avoid Between statement in sql query

When to avoid Between statement in sql query

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 80 other followers

Twitter updates

Archives

RSS InfoQ Feeds

  • Article: Q&A on the Book "Humans vs Computers"
    Author Gojko Adzic has released a book, Humans vs Computers in which he tells stories about the impact of inflexible automation, edge cases and software bugs on the lives of real people. He explains the common mistakes built into the systems and provides advice on how to prevent these mistakes from being built into our systems in the first place. By Shane Ha […]
  • Q&A with Michael Coté on Devops Adoption and his Talk at DevOpsDays NZ
    Raf Gemmail talks to Pivotal’s Michael Coté about obstacles to DevOps adoption and his forthcoming talk at DevOpsDays NZ 2017 By Rafiq Gemmail
  • TensorFlow Serving 1.0 Release Detailed at Google I/O
    Google's Noah Fiedel details new programming model for TensorFlow Serving in a stable 1.0 release. Subject matter addresses common challenges with portability, servablility , and reproducibility improvements. By Dylan Raithel
  • First NetBeans Code Drop Lands at Apache
    Oracle has released the first of three NetBeans code drops to the Apache Incubator. By Matt Raible
  • Article: The Top 10 Adages in Continuous Deployment
    On the basis of discussions at the Continuous Deployment Summit, researchers derived 10 adages about continuous-deployment practices. These adages represent a working set of approaches and beliefs that guide current practice and establish a tangible target for empirical validation. By Chris Parnin
  • Podcast: Joshua Kerievsky and Heidi Helfand on High Performance via Psychological Safety
    In this podcast Shane Hastie, Lead Editor for Culture & Methods, spoke to Joshua Kerievsky, CEO of Industrial Logic, and Heidi Helfand, Director of Engineering Excellence at Procore Technologies and author of the book Dynamic Reteaming, about their talk High Performance via Psychological Safety. By Joshua Kerievsky
  • Spotify and Google Release Forseti GCP Security Tools
    Google has opened up Forseti Security, a set open source tools for GCP security, to all GCP users. The project is the result of a collaborative effort from both Spotify and Google, combining what was originally separate work together into a single toolkit. It aims to automate security processes for developers in order for them to develop more freely. By Andr […]
  • Article: Q&A on the Book SAFe Distilled
    The book SAFe Distilled breaks down the complexity of the framework into easily understood explanations and actionable guidance. It’s a resource for acquiring a deep understanding of the Scaled Agile Framework, and how to implement it successfully. By Ben Linders
  • String Interpolation in Entity Framework Raises Concerns
    One of the new features in Entity Framework Core 2 is the ability to automatically convert interpolated strings into parameterized SQL. Though designed to avoid problems with poorly written SQL, it is feared that it may actually lead to more SQL injection attacks. By Jonathan Allen
  • Podcast: Twitter's Yao Yue on Latency, Performance Monitoring, & Caching at Scale
    Yao Yue spent the majority of her career working on caching systems at Twitter. She created a performance team that deals with edge performance outliers often exposed by the enormous scale of Twitter. In this podcast, she discusses standing up the performance team, thoughts on instrumenting applications, and interesting performance issues (and strategies for […]

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

Blogs I Follow

%d bloggers like this: