none
Questions about timing queries

    Question

  • Hi all (and especially the dev team),

    I've been having an interesting discussion with Colin Banfield in the comments section of a blog post I wrote some time ago about timing query execution:

    http://blog.crossjoin.co.uk/2014/11/17/timing-power-query-queries/#comment-26861

    It has raised a couple of questions that both Colin and I would love to know the answers to:

    1) Is the method described in the blog post correct as a way of measuring query duration?

    2) What time does DateTime.FixedLocalNow() actually return: the time that the function is first called in the query, the time that overall query execution starts at, or something else completely?

    3) Is it normal to see variation in query execution times, as in Colin's example? I know this is a pretty vague question because there could be lots of factors in play here besides Power Query.

    4) Is there any chance that, in the future, we could see the amount of time a query took to execute in the UI somewhere?

    Thanks,

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Wednesday, March 09, 2016 4:17 PM

Answers

All replies

  • Regarding #2, DateTime.FixedLocalNow computes its "now" value when the evaluation of a query begins.

    Ehren

    Thursday, March 10, 2016 11:27 PM
    Owner
  • With respect to #3, what kind of variation are you (or Colin) talking about? 10%? 25%? 100%?
    Friday, March 11, 2016 1:24 PM
    Owner
  • Regarding #2, DateTime.FixedLocalNow computes its "now" value when the evaluation of a query begins.

    Ehren

    I wonder how a mortal can make that explicit deduction from this description:

    "Returns a DateTime value set to the current date and time on the system. This value is fixed and will
    not change with successive calls, unlike DateTime.LocalNow, which may return different values over
    the course of execution of an expression."


    I am not at all surprised though - I've concluded that at least 50% of all M function descriptions are either unhelpful, misleading, or downright incorrect - and that percentage doesn't include functions with no description at all!

    Friday, March 11, 2016 9:40 PM
  • With respect to #3, what kind of variation are you (or Colin) talking about? 10%? 25%? 100%?

    With the generation of all prime numbers up to 10,000 (1229 primes), I did 10 refreshes using DateTime.FixedLocalNow() for the start time, and another 10 refreshes using DateTime.LocalNow(). These are statistically small sample sizes, but here are the times I got (in seconds):

    FixedLocalNow

    Max = 0.4135848

    Min = 0.3755802

    Diff (Max-Min) = 0.0380046

    STDEV (sample) = 0.0105097

    LocalNow

    Max = 0.4355807

    Min = 0.3690867

    Diff (Max-Min) = 0.0664940

    STDEV (sample) = 0.0173686


    Friday, March 11, 2016 10:08 PM
  • For a benchmark that finishes in under a second, I don't think that this variation is out-of-the-ordinary.
    Friday, March 11, 2016 11:38 PM
    Owner
  • Hi Curt, thanks for the reassurance. :)
    Sunday, March 13, 2016 6:16 PM