Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

SQL Question

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
isames
Asked:
isames
  • 3
  • 2
  • 2
  • +2
1 Solution
 
YZlatCommented:
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
 
ste5anSenior DeveloperCommented:
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
 
gaspanCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
isamesAuthor Commented:
@ste5an

This is a SQL test question
1
 
ste5anSenior DeveloperCommented:
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
 
PortletPaulCommented:
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
 
isamesAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
Then both queries are identical in performance and execution plan..

Capture.PNG
0
 
PortletPaulCommented:
Yes. In recent sql server versions that is true... hence my point about the inadequacy of the test question :)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now