0

I have created this below query, which is calculating sum till each date. This query gives me exact result which am looking for, But the data which it has very big. How can I speed or optimize it?

IF OBJECT_ID('TEMPDB..#temp')IS NOT NULL 
DROP TABLE #temp
IF OBJECT_ID('TEMPDB..#temp1')IS NOT NULL 
DROP TABLE #temp1

Declare    @DateFrom DateTime
Set    @DateFrom = CONVERT(DATE,DATEADD(D,-datepart(d,getdate())+1,DATEADD(M,-1,GETDATE())))
Declare    @DateTo DateTime
Set    @DateTo = CONVERT(DATE,DATEADD(D,-DATEPART(D,getdate()),GETDATE())) 

Select 
Date
,[Item No_]
,case when Sum(ILE.Quantity)>0 and [Posting Date]=Date then Sum(ILE.Quantity) else 0 end AS [In Quantity],
case when Sum(ILE.Quantity)<0 and [Posting Date]=Date then Sum(ILE.Quantity) else 0 end AS [Out Quantity],
(Sum(ILE.Quantity))Closing
into #temp
 from Calender C left Join [Snowman Logistics Limited$Item Ledger Entry]ILE on Cast(ILE.[Posting Date]as date)<=C.Date 

Group by Date,[Item No_],[Posting Date]

Select  Date,[Item No_]
,SUm([In Quantity])[In Quantity],Sum([Out Quantity])[Out Quantity],Sum(Closing)Closing from #temp where Date Between @DateFrom and @DateFrom
group by  Date,[Item No_]
Dale K
  • 25,246
  • 15
  • 42
  • 71
user10991129
  • 67
  • 6
  • Try first get the running totals using window functions https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server and then join the result with calendar table. – Serg Feb 14 '20 at 06:37

1 Answers1

0

You can try:

  1. in your first query you are interested in matches - you can try using INNER JOIN, if you still need [dates]-[items] even if there is no entry for them for the specified period in the [Snowman Logistics Limited$Item Ledger Entry] table, you can later;
  2. why you need to cast [Posting Date] as date - if it is a string and not date/datetime type, you should change it in order to skip the explicit (or implicit) cast during the calculations
  3. if 1 is OK and you can use INNER JOIN, you can try to create indexed view and have the initial query precalculated

  4. In your first query you are grouping the records by [Posting Date] and in the second one, you are grouping the records again, excluding it - can't you unite the queries?

    Select 
Date
,[Item No_]
,case when Sum(ILE.Quantity)>0 and [Posting Date]=Date then Sum(ILE.Quantity) else 0 end AS [In Quantity],
case when Sum(ILE.Quantity)<0 and [Posting Date]=Date then Sum(ILE.Quantity) else 0 end AS [Out Quantity],
(Sum(ILE.Quantity))Closing
into #temp
from Calender C 
left Join [Snowman Logistics Limited$Item Ledger Entry]ILE 
    on Cast(ILE.[Posting Date]as date) <= C.Date 
where Date Between @DateFrom and @DateFrom
Group by Date,[Item No_]

  5. Depending on how wide are your table and the types of the columns you can get performance boost if you create covering indexes - these type of indexes will include only the column you are using in your queries, so less data will be read(less IO).

gotqn
  • 42,737
  • 46
  • 157
  • 243