Poor performance against ADLA table


  • Hi

    Could someone please help with a performance issue. This table has less than a million rows. It takes approx 30 seconds to prepare and nearly 2 mins to run. Only Using 1 AU but why use more for such a simple compute.

    Any tips?

    @signalOut =
    MAX(Int32.Parse(AnswersId ?? "0")) AS Id
    FROM stg.Answers;

    Thursday, February 15, 2018 11:34 AM

All replies

  • OK ... I changed the data type on the table to Int32 and did the MAX without parsing. Now takes 20 secs :) That's still quite a long time compared to say ... SQL server.
    Thursday, February 15, 2018 12:05 PM
  • I'd be happy to help.  Your comparision is pretty apples to oranges, what I mean by that is that ADLA is a job service and each time you submit a job we assign the AU resources for that job.  Comparing with SQL Server, where the resources are assigned when you start SQL and aren't released until you either stop the VM, or the Azure SQL DB.

    What performance are you hoping for with ADLA?

    Thursday, February 15, 2018 5:08 PM
  • Well, I am creating a delta pipeline in ADFv2. This is running every 15 minutes. I need to get the max id or date from the ADLA table then pass that as a paramter to the next data factory action ... this action brings the delta accross from the source database.

    So I have a 15 minute window to get stuff from A to B and do some transformation (also in USQL). So....... using 3 minutes of that window just to return a Max(id) is making things a little tight.

    It is a bit odd as I have got the transformation USQL to complete quite quckly, using mixture of ADLA and JSON. Its just the MAX operation I cant seem to optimise. Any best pratice would be welcome.

    Monday, February 26, 2018 12:55 PM