0

This is direct continuation of this question, then solved with this answer.

The problem lies with the logic here:

,case when
  Cast(o.[CreationDate] as time) > '16:00:00' 
     then (select top 1 [PKDate] from [calendar].[dbo].days
           where is_workday = 1 and continuous_day > da.continuous_day
           and continuous_day < da.continuous_day+7 order by PKDate asc)
     else (select top 1 [PKDate] from [calendar].[dbo].days
           where is_workday = 1 and continuous_day >= da.continuous_day
           and continuous_day < da.continuous_day+7 order by PKDate asc)
     end  OpDate

This fragment tries to find next workday date based on time part of o.[CreationDate] in [calendar].[dbo].[days]. If I could replace that subquery with a join I think it would make the results faster, which is my aim.

However the only solution I could think of in this case is to add another column to [calendar].[dbo].[days], which would hold NextWorkdayDate. I can do that, but I would like query solution, as I may not always be able to amend schema.

The dataset to be crunched has doubled in size since last time (yeah, we had a great year), the full query also grew a bit, so it's taking too long to execute (~20min).

The above bit of code is now my main problem, as all its instances and variations account for 70% of the whole query's actual cost. Smoothing it out would be great.

This is for a Data Warehouse, so actually adding column makes a lot of sense. The Data Warehouse is getting bigger and more sophisticated so cutting down on cycles everywhere I can is good long-term strategy. Would you agree?

Community
  • 1
AcePL
  • 349
  • 1
  • 3
  • 15
  • The bits here are parts of much bigger stored procedure. Can't extract that specific query from whole plan and running just it is unusable as it returns completely different plan than when part of whole SP. Unfortunately not as fluent in XML as I would like, all attempts at editing it failed (xml not validating). Sorry, we have to stick to general statements. – AcePL May 03 '18 at 10:09
  • If the question is deemed to be too broad or to general, please let me know and I'll close it (or remove). Please let me know how should I proceed, but as I think it raises interesting point about data warehousing It still may be very worthwhile to engage in some speculation? I'm sure I'm not unique with my problems... – AcePL May 03 '18 at 11:25

0 Answers0