Solved

SQL Question

Posted on 2016-09-20
9
38 Views
Last Modified: 2016-09-21
When I ran both queries below, I get the same answer in the same amount of time. Why is Query B considered to be "more efficient"? Of course, I think it's "A".


--A--  
  Select Count(INVOICE_N) as NumberOfInvoices, convert(money, sum(RSID_EXTENDED)) as TotalRevenue
  From HistoryV2
  Where rsih_date = convert(date,getdate())

  --B--
  Select Count(INVOICE_N) as NumberOfInvoices, convert(money, sum(RSID_EXTENDED)) as TotalRevenue
  From HistoryV2
  Where rsih_date >= convert(date,getdate()) and rsih_date < dateadd(day,1,convert(date,getdate()))
0
Comment
Question by:isames
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 41807521
Why not use BETWEEN?

Select Count(INVOICE_N) as NumberOfInvoices, convert(money, sum(RSID_EXTENDED)) as TotalRevenue
  From HistoryV2
  Where rsih_date BETWEEN convert(date,getdate()) and dateadd(day,1,convert(date,getdate()))

Open in new window

0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
ID: 41807541
Where did you get this "more efficent" from?

btw, when rsih_date is of type DATE, then both queries are logical equivalent.

What indice do you have on this table? How does the actual execution plan looks like? What does SET STATISTICS IO ON; report in the message pane?
0
 
LVL 1

Accepted Solution

by:
gaspan earned 500 total points
ID: 41807560
I believe it has to do with indexing. But using ranges you can skip large sets of dates within the index.

There is a discussion that explains the SQL search process when you are using ranges here:

http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/greater-less-between-tuning-sql-access-filter-predicates
0
 

Author Comment

by:isames
ID: 41807680
@ste5an

This is a SQL test question
1
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 32

Expert Comment

by:Stefan Hoffmann
ID: 41808222
First of all:

When it is all Information you've got, then you cannot decide. Cause you need information about the physical table and its indices.  Clustered vs. heap, covering vs. non-covering indices. This is necessary to predict whether we can expect an table scan, clustered index seek or whether we will even see a RID lookup.

btw, depending on the test, the points above are the correct answer.

E.g. take a look at the actual execution plans:

DECLARE @HistoryV1 TABLE
    (
      INVOICE_N INT ,
      RSID_EXTENDED INT ,
      rsih_date DATE
    );

SELECT  COUNT(INVOICE_N) AS NumberOfInvoices ,
        CONVERT(MONEY, SUM(RSID_EXTENDED)) AS TotalRevenue
FROM    @HistoryV1
WHERE   rsih_date = CONVERT(DATE, GETDATE()); 

  
SELECT  COUNT(INVOICE_N) AS NumberOfInvoices ,
        CONVERT(MONEY, SUM(RSID_EXTENDED)) AS TotalRevenue
FROM    @HistoryV1
WHERE   rsih_date >= CONVERT(DATE, GETDATE())
        AND rsih_date < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())); 

DECLARE @HistoryV2 TABLE
    (
      INVOICE_N INT ,
      RSID_EXTENDED INT ,
      rsih_date DATE ,
      UNIQUE ( rsih_date )
    );

SELECT  COUNT(INVOICE_N) AS NumberOfInvoices ,
        CONVERT(MONEY, SUM(RSID_EXTENDED)) AS TotalRevenue
FROM    @HistoryV2
WHERE   rsih_date = CONVERT(DATE, GETDATE()); 

  
SELECT  COUNT(INVOICE_N) AS NumberOfInvoices ,
        CONVERT(MONEY, SUM(RSID_EXTENDED)) AS TotalRevenue
FROM    @HistoryV2
WHERE   rsih_date >= CONVERT(DATE, GETDATE())
        AND rsih_date < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())); 

DECLARE @HistoryV3 TABLE
    (
      INVOICE_N INT ,
      RSID_EXTENDED INT ,
      rsih_date DATE ,
      PRIMARY KEY ( rsih_date )
    );

SELECT  COUNT(INVOICE_N) AS NumberOfInvoices ,
        CONVERT(MONEY, SUM(RSID_EXTENDED)) AS TotalRevenue
FROM    @HistoryV3
WHERE   rsih_date = CONVERT(DATE, GETDATE()); 

  
SELECT  COUNT(INVOICE_N) AS NumberOfInvoices ,
        CONVERT(MONEY, SUM(RSID_EXTENDED)) AS TotalRevenue
FROM    @HistoryV3
WHERE   rsih_date >= CONVERT(DATE, GETDATE())
        AND rsih_date < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())); 

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41808470
The test question is most probably trying to make a point about "sargable predicates"

But the examples do not make the point very clearly,  a more obvious example would apply a function to the date (in bold) in example A

Select Count(INVOICE_N) as NumberOfInvoices, convert(money, sum(RSID_EXTENDED)) as TotalRevenue
  From HistoryV2
  Where convert([rsih_date],date) = convert(date,getdate())

and then compare that to a "sargable" alternative where the data in not subjected to alteration

Regretably for the examiner an added complexity arises because in recent versions of SQL Server you can still get use of an index on [rsih_date] even if subjected to that function.

Looking for "sargable predicates" is good practice, I think it is the exam designer who has failed.
0
 

Author Comment

by:isames
ID: 41808764
I failed to mention that the test question stated that the data column had a clustered index.

I will do better next time.

I'm sorry about that.
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
ID: 41808848
Then both queries are identical in performance and execution plan..

Capture.PNG
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41809752
Yes. In recent sql server versions that is true... hence my point about the inadequacy of the test question :)
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now