Performance tuning for SQL Query

Question

Hi here I am attaching my sample table structure which I want to use in my project

CREATE TABLE TESTSALESVOLUMETABLE
(
ID INT IDENTITY(1,1),
AMOUNT DECIMAL(18,2),
CREDITEDDATE DATETIME
)

and the queries I used like this

DECLARE @CURRENTDATE AS DATETIME = GETDATE()
DECLARE @PSV AS INT = 0
DECLARE @TOTAL AS INT = 0

IF (DATEPART(DAY, @CURRENTDATE) <= 15)
BEGIN
    SELECT @PSV = (
            SELECT Sum(AMOUNT)
            FROM TESTSALESVOLUMETABLE
            WHERE DATEPART(DAY, CREDITEDDATE) <= 15
                AND MONTH(CREDITEDDATE) = MONTH(@CURRENTDATE)
                AND YEAR(CREDITEDDATE) = YEAR(@CURRENTDATE)
            )
END
ELSE
BEGIN
    SELECT @PSV = (
            SELECT Sum(AMOUNT)
            FROM TESTSALESVOLUMETABLE
            WHERE DATEPART(DAY, CREDITEDDATE) > 15
                AND MONTH(CREDITEDDATE) = MONTH(@CURRENTDATE)
                AND YEAR(CREDITEDDATE) = YEAR(@CURRENTDATE)
            )
END

SELECT @total = (
        SELECT Sum(Amount)
        FROM TESTSALESVOLUMETABLE
        )

SELECT @PSV 'PSV',
    @total 'TOTAL'

Is there any way to increase the performance of this query

Answer 1

I thing this will work fine

DECLARE @PSV AS INT = 0
DECLARE @TOTAL AS INT = 0

IF (DATEPART(DAY,GETDATE()) <= 15)
BEGIN
 SELECT @PSV = Sum(AMOUNT)
FROM TESTSALESVOLUMETABLE
WHERE CREDITEDDATE >= DATEADD(DAY, 1 - DAY(GETDATE()), CAST(GETDATE() as DATE)) AND
      CREDITEDDATE < DATEADD(DAY, 16 - DAY(GETDATE()), CAST(GETDATE() as DATE));
END
ELSE
BEGIN
 SELECT @PSV = Sum(AMOUNT)
FROM TESTSALESVOLUMETABLE
WHERE CREDITEDDATE >= DATEADD(DAY, 16 - DAY(GETDATE()), CAST(GETDATE() as DATE)) AND
      CREDITEDDATE < DATEADD(DAY, 31 - DAY(GETDATE()), CAST(GETDATE() as DATE));
END

SELECT @total = (
        SELECT Sum(Amount)
        FROM TESTSALESVOLUMETABLE
        )

SELECT @PSV 'PSV',
    @total 'TOTAL'

Answer 2

First, you don't need a subquery for setting the variable. Second, the use of functions on columns usually prevents the use of indexes. So, I would recommend something like this:

SELECT @PSV = Sum(AMOUNT)
FROM TESTSALESVOLUMETABLE
WHERE CREDITEDDATE >= DATEADD(DAY, 1 - DAY(GETDATE()), CAST(GETDATE() as DATE)) AND
      CREDITEDDATE < DATEADD(DAY, 16 - DAY(GETDATE()), CAST(GETDATE() as DATE));

Then, you want an index on TESTSALESVOLUMETABLE(CREDTEDDATE, AMOUNT).

Answer 3

Following the guidelines from: Bad habits to kick : mis-handling date / range queries - Aaron Bertrand - 2009-10-16

First, we want to get rid of:

where datepart(day, crediteddate) <= 15 
and month(crediteddate)=month(@currentdate) 
and year(crediteddate)=year(@currentdate) 

because:

[...] you've effectively eliminated the possibility of SQL Server taking advantage of an index. Since you've forced it to build a nonsargable condition, this means it will have to convert every single value in the table to compare it to the [value] you've presented on the right hand side [...]

Second, we want to make sure to avoid using between with datetimes because it can return unwanted rows or miss wanted rows, even when using something like between ... and dateadd(second, -1, @thrudate) or even between ... and 'yyyy-mm-ddT23:59:59.997'. (See Aaron Bertrand's article for more examples on this).

So the best way to do this would be to say:

• If today is the 15th or earlier, get rows >= the 1st of this month and < the 16th of this month

• If today is the 16th or later, get rows >= the 16th of this month and < the 1st of next month

Also, as Gordon Linoff mentioned, you will benefit from an index on testsalesvolumetable(crediteddate, amount). But Gordon's formulas always return the 1st and 16th of the current month.

Instead of breaking the procedure into two queries depending on the current day, we can calculate those from and thru dates and just use one query.

Here is example code both with and without using variables for the from and thru dates, along with a quick calendar test to check the resulting ranges.

---

rextester link for test setup: http://rextester.com/YVLI65217

create table testsalesvolumetable (crediteddate datetime not null, amount int not null)
insert into  testsalesvolumetable values 
 ('20161201',1) ,('20161202',1) ,('20161203',1) ,('20161204',1) ,('20161205',1)
,('20161206',1) ,('20161207',1) ,('20161208',1) ,('20161209',1) ,('20161210',1) 
,('20161211',1) ,('20161212',1) ,('20161213',1) ,('20161214',1) ,('20161215',1) 
,('20161216',1) ,('20161217',1) ,('20161218',1) ,('20161219',1) ,('20161220',1) 
,('20161221',1) ,('20161222',1) ,('20161223',1) ,('20161224',1) ,('20161225',1) 
,('20161226',1) ,('20161227',1) ,('20161228',1) ,('20161229',1) ,('20161230',1) 
,('20161231',1) ,('20170101',1) 


/* ----- without variables */
declare @psv int;
select @psv = Sum(amount)
  from testsalesvolumetable
  where crediteddate >= dateadd(day, (1- (day(convert(date,getdate()))/16)) - (day(convert(date,getdate()))%16), convert(date,getdate()))
    and crediteddate <  case 
    when day(convert(date,getdate()))>15 
      then dateadd(month, datediff(month, -1, convert(date,getdate())), 0) 
    else dateadd(day,15,dateadd(month, datediff(month, 0, convert(date,getdate())), 0))
    end;
    
select psv=@psv;
--*/

/* ----- with variables */
--declare @psv int;
declare @currentdate date;
/* change to date datatype to get rid of time portion*/
  set @currentdate = getdate(); 
  --set @currentdate = '20161212'

declare @fromdatetime datetime;
declare @thrudatetime datetime;
  set @fromdatetime = dateadd(day, (1- (day(@currentdate)/16)) - (day(@currentdate)%16), @currentdate);
  set @thrudatetime = case 
    when day(@currentdate)>15 
      then dateadd(month, datediff(month, -1, @currentdate), 0)
    else dateadd(day,15,dateadd(month, datediff(month, 0, @currentdate), 0))
    end;
select @psv = sum(amount)
  from testsalesvolumetable
  where crediteddate >= @fromdatetime
    and crediteddate <  @thrudatetime;
--/*
select 
      psv=@psv
    , CurrentDate =convert(varchar(10),@currentdate ,121)
    , FromDateTime=convert(varchar(10),@fromdatetime,121)
    , ThruDateTime=convert(varchar(10),@thrudatetime,121);
--*/

---

Rextester link for the calendar test: http://rextester.com/ESZRH30262

--/* ----- Calendar Test */
;with n as (
  select n from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t(n)
)
, cal as (
  select DateValue=convert(datetime,dateadd(day, row_number() over (order by (select 1)) -1, '20160101'))
    from n as a 
      cross join n as b 
      cross join n as c 
      cross join n as d
)
select
      --DateValue=convert(varchar(10),DateValue,121)
      minDate =convert(varchar(10),min(DateValue),121)
    , maxDate =convert(varchar(10),max(DateValue),121)
    , FromDatetime=convert(varchar(10),dateadd(day, (1- (day(DateValue)/16)) - (day(DateValue)%16), DateValue),121)
    , ThruDatetime=convert(varchar(10),case 
        when day(DateValue)>15 
          then dateadd(m, datediff(m, -1, DateValue), 0) 
          else convert(varchar(10),dateadd(day, 16 - day(DateValue), DateValue),121) 
        end,121)
    , GordonFrom  = convert(varchar(10),dateadd(day, 1  - day(DateValue), cast(DateValue as date)),121)
    , GordonThru  = convert(varchar(10),dateadd(day, 16 - day(DateValue), cast(DateValue as date)),121)
  from cal
  where datevalue >= '20160101' 
    and datevalue <  '20170101'
  --/*
  group by 
      convert(varchar(10),dateadd(day, (1- (day(DateValue)/16)) - (day(DateValue)%16), DateValue),121)
    , convert(varchar(10),case 
        when day(DateValue)>15 
          then dateadd(m, datediff(m, -1, DateValue), 0) 
          else convert(varchar(10),dateadd(day, 16 - day(DateValue), DateValue),121) 
        end,121)
    , convert(varchar(10),dateadd(day, 1  - day(DateValue), cast(DateValue as date)),121)
    , convert(varchar(10),dateadd(day, 16 - day(DateValue), cast(DateValue as date)),121)
  order by FromDateTime