Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Question

Posted on 2016-09-20
9
Medium Priority
?
57 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 35

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 35

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 35

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

670 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