Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Question

Posted on 2016-09-20
9
Medium Priority
?
60 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 36

Expert Comment

by:ste5an
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 2000 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 1

Author Comment

by:isames
ID: 41807680
@ste5an

This is a SQL test question
1
 
LVL 36

Expert Comment

by:ste5an
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 49

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
 
LVL 1

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 36

Expert Comment

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

Capture.PNG
0
 
LVL 49

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

886 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