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

  • How Google Develops New Managers
    Alex Langshur, host of Google Partners Podcasts, has organized the podcast Google HR secrets: identifying & developing great managers, interviewing Sarah Calderon, People Development at Google, on how Google selects, trains, and develops their managers. By Abel Avram
  • Presentation: Cognitive Services, Next Step in Creating Our Robot Overlords
    Harold Pulcher discusses Cognitive Services, how to get started using them, and how to incorporate speech, image, and facial recognition into an application. By Harold Pulcher
  • Presentation: Control Flow Integrity Using Hardware Counters
    Jamie Butler and Cody Pierce discuss a new system for early detection and prevention of unknown exploits. Their system uses Performance Monitoring Unit hardware to enforce coarse-grained Control Flow Integrity (CFI). They intend to prove that their approach is effective and suitable for practical use, while staying resistant to bypass. By Jamie Butler
  • JetBrains Launches GoLand Go IDE
    JetBrains has moved its Go IDE from its early access programme to market. Now branded as GoLand, the IDE extends the IntelliJ platform making its core functionality specific to Go. This follows suit with their other language-specific tools such as PyCharm for Python and RubyMine for Ruby. By Andrew Morgan
  • Panel on the Future of AI
    An SF QCon panel on the future of AI explored some issues facing machine learning today. The areas explored: critical issues facing AI right now, how has technology changed the way people are hired, how non-leading edge companies make the best use of current technologies, what the role of humans in relation to AI is, and exciting new breakthroughs on the imm […]
  • Microsoft Updates Cosmos DB with Cassandra Support and Provides Better Availability Guarantees
    Last month at Microsoft Connect 2017, Azure Cosmos DB received several new updates, including support for using the Cassandra NoSQL database API and increased guarantees for availability. With the Cassandra NoSQL database API, customers can run operations inside Cosmos DB on a data model. The availability guarantee moves from 99.99 percent to 99.999 percent. […]
  • Article: Approximate Computing on WSO2: Explaining Approximation Algorithms in an Applied Setting
    In this article, we describe an example real world application of API monitoring which gets benefit by using approximate stream processing. We developed the application on top of WSO2 Stream Processor as Siddhi extension. Siddhi is the complex event processing library which acts as the event processing engine of WSO2 Stream Processor. By Chamod Samarajeewa
  • Rust in Visual Studio and VS Code
    Daniel Griffen has released a preview version of a Rust language service for Visual Studio. This plugin requires Visual Studio 2017 Preview, an experimental release stream for testing new VS features. By Jonathan Allen
  • Article: Key Takeaway Points and Lessons Learned from QCon San Francisco 2017
    The eleventh annual QCon San Francisco was the biggest yet, bringing together over 1,800 team leads, architects, project managers, and engineering directors. By Abel Avram
  • Article: Q&A With Eberhard Wolff On the Book “A Practical Guide to Continuous Delivery”
    Eberhard Wolff speaks with InfoQ about his work "Continuous Delivery: A Practical Guide", where we detail some of the major concepts behind successful CD adoption and the ripple-effect it can have on developer productivity and quality of service. By Dylan Raithel

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: