1

I am working in a type 2 data vault and currently working on a function to try and simulate a data cube for certain users to do fast data pulls from specific snapshots.

At the moment i can modify the code to make it work by convert the paramerter on the join (or where clause) however this results in a 3 second performance reduction across 100,000 records.

For performance is it possible to perform a

set @AsAt = dbo.AdjustLocalToUtc(@AsAt)

somewhere before the return statement or pehaps at the very start.

Code Block - Works if UTC value passed

ALTER FUNCTION dbo.fn_Ticket_AsAt
(
    @AsAt datetime      --Local Time for User Confort
)
RETURNS TABLE 
AS 
RETURN
(
    Select * 
    from dvr.hub_ticket hub
    join dvr.sat_ticket sat
        on hub.hub_ticket_sk = sat.hub_ticket_sk
            and @AsAt between sat.load_datetime and coalesce(sat.load_end_datetime,getutcdate())
                --Load_datetime and load_end_datetime are UTC values 
)

What would be a desired form of the query as the conversion only needs to be performed once and not for each record. 

ALTER FUNCTION dbo.fn_Ticket_AsAt
(
    @AsAt datetime      --Local Time for User Confort
,   declare @dv datetime = dbo.AdjustLocaltoUtc(@AsAt)

)
RETURNS TABLE 
AS 
RETURN
(
    Select * 
    from dvr.hub_ticket hub
    join dvr.sat_ticket sat
        on hub.hub_ticket_sk = sat.hub_ticket_sk
            and @dv between sat.load_datetime and coalesce(sat.load_end_datetime,getutcdate())
                --Load_datetime and load_end_datetime are UTC values 
)
Dheebs
  • 398
  • 1
  • 6
  • 19
  • 1
    what's the question?....... – Mitch Wheat Oct 12 '18 at 01:01
  • Sorry mitch will modify when I get back to my desk. – Dheebs Oct 12 '18 at 01:11
  • How do I set the asat parameter from what the user passed to a different value – Dheebs Oct 12 '18 at 01:12
  • If you want your function to remain an inline function (which, spoiler alert; you would), you cant. I'd suggest either doing the conversion before it's passed in, or break out the conversion logic from the function you're describing and just bake it into `fn_Ticket_AsAt`. I'm guessing it's something as simple as `and dateadd(hour, datediff(hour, getdate(), getdateutc()), @AsAt ) = sat.load_datetime...` or something along those lines – Xedni Oct 12 '18 at 01:18
  • Hi @Xedni unfortunately that won't work in this instance as I live in a country with day light savings so for half the year it will be out by 1 hour. – Dheebs Oct 12 '18 at 01:22
  • Well what does your utc datetime conversion function do? If you can't do it the way I mentioned, what would your function do differently? – Xedni Oct 12 '18 at 01:23
  • You could also accept the date as a `datetimeoffset` instead so it captures the local timezone information faithfully (again, assuming you're unable to convert it before passing it into the function) – Xedni Oct 12 '18 at 01:26

0 Answers0