Solved

SQL Question

Posted on 2016-09-20
9
42 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 33

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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:isames
ID: 41807680
@ste5an

This is a SQL test question
1
 
LVL 33

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 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 33

Expert Comment

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

816 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

11 Experts available now in Live!

Get 1:1 Help Now