none
Query too complex

    Question

  • Hi,

    I try to run a script in azure datalake analytics, but it sais the query is too complex (although these are only ninie statements and two or three joins). Is it possible to give azure datalake analytics some more optimization time?

    Regards,

    Filip

    DESCRIPTION
    The script is too complex and needs to be simplified.
    <label class="msportalfx-text-label" data-bind="text: resolutionLabel" style="font-size:12px;font-weight:normal;font-family:az_ea_font, wf_segoe-ui_normal, 'Segoe UI', 'Segoe WP', Tahoma, Arial, sans-serif;line-height:normal;text-transform:uppercase;color:#2d3239;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;letter-spacing:normal;orphans:2;text-align:start;text-indent:0px;white-space:normal;widows:2;word-spacing:0px;-webkit-text-stroke-width:0px;">RESOLUTION</label>
    Either separate into multiple jobs and/or reduce the number of inputs, outputs, operators, or time range.
    <label class="msportalfx-text-label" data-bind="text: detailsLabel" style="font-size:12px;font-weight:normal;font-family:az_ea_font, wf_segoe-ui_normal, 'Segoe UI', 'Segoe WP', Tahoma, Arial, sans-serif;line-height:normal;text-transform:uppercase;color:#2d3239;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;letter-spacing:normal;orphans:2;text-align:start;text-indent:0px;white-space:normal;widows:2;word-spacing:0px;-webkit-text-stroke-width:0px;">DETAILS</label>
    Compilation timeout for DeepThe job exceeded the compilation timeout of 24.9999833416667 minutes, in general this is caused by job script is too complex and needs to be simplified, you can either separate into multiple jobs and/or reduce the number of inputs, outputs, operators, or time range.; JobId: 19e45d27-8887-45ef-a6a7-f26b6accb22e; Duration: 00:25:00.0253001; Exit Code: -1; Configuration: kobo_live_signed_5186128; Runtime: kobo_live_signed_5186128; Output: Deleted file D:\data\CCS\jobs\19e45d27-8887-45ef-a6a7-f26b6accb22e_v0\securityInfo.
    Deleted file D:\data\CCS\jobs\19e45d27-8887-45ef-a6a7-f26b6accb22e_v0\jobEncryptionKey.
    Server Version: 10.2.2.0
    Client Version: 10.2.2.0
    Target: Cluster
    Stat: FetchReal
    SkipPath: False
    Syntax Parsing finished.
    Resource Resolution finished.
    Input/Output Resolution finished.
    HandleCodeBehind finished.
    Wait until structured stream metadata loading end finished.
    Binder Pass finished.
    Semantic Analyzer finished.
    Error Reporter finished.
    OptTree Generator finished.
    
    --==== CQO Optimization ====--
    Traceflag 433 is ON
    Traceflag 608 is ON
    Traceflag 551 is ON
    Traceflag 402 is ON
    Traceflag 550 is ON
    Traceflag 21 is ON
    Traceflag 552 is ON
    Traceflag 554 is ON
    Traceflag 515 is ON
    Traceflag 534 is ON
    Traceflag 541 is ON
    Traceflag 435 is ON
    Traceflag 106 is ON
    Traceflag 514 is ON
    Traceflag 568 is ON
    Traceflag 569 is ON
    Traceflag 203 is ON
    Traceflag 546 is ON
    Traceflag 562 is ON
    Traceflag 563 is ON
    Traceflag 505 is ON
    Traceflag 506 is ON
    Traceflag 542 is ON
    Timer[CQO - Init] = 0.0159387 s
    Timer[CQO - CopyIn] = 0.1403 s
    Timer[CQO - Search - Init] = 9.53671e-05 s
    Timer[CQO - Search - NNF] = 0.000500356 s
    Timer[CQO - Search - PreOptimization] = 0.000597434 s
    Pruning partitions and streamsets
    Timer[CQO - Resolve stream sets] = 70.0184 s
    Simplifying relational tree
    Timer[CQO - Search - Simplification] = 78.0139 s
    Timer[CQO - Search - Join Reordering] = 0.89406 s
    Timer[CQO - Search - Expression Sharing] = 0.378175 s
    Timer[CQO - Search - Optimization] = 92.4393 s
    Timer[CQO - Search - Subtotal] = 171.529 s
    Timer[CQO - Showplan] = 1.08519 s
    Timer[CQO - Stage Generation] = 91.7907 s
    Timer[CQO - PostOptimization] = 0.023663 s
    Timer[CQO - Total] = 266.339 s
    CQO: Optimization call took 266.3415681 seconds
    CQO: Optimization succeeded.
    *** Exiting optimization, time elapsed until this point: 00:04:31.7817171
    Optimization finished.
    
    
    ;
    <label class="msportalfx-text-label" data-bind="text: titleLabel" style="font-size:12px;font-weight:normal;font-family:az_ea_font, wf_segoe-ui_normal, 'Segoe UI', 'Segoe WP', Tahoma, Arial, sans-serif;line-height:normal;text-transform:uppercase;color:#2d3239;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;letter-spacing:normal;orphans:2;text-align:start;text-indent:0px;white-space:normal;widows:2;word-spacing:0px;-webkit-text-stroke-width:0px;">ERROR</label>
    E_USER_CCS_COMPILERTIMEOUT
    <label class="msportalfx-text-label" data-bind="text: messageLabel" style="font-size:12px;font-weight:normal;font-family:az_ea_font, wf_segoe-ui_normal, 'Segoe UI', 'Segoe WP', Tahoma, Arial, sans-serif;line-height:normal;text-transform:uppercase;color:#2d3239;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;letter-spacing:normal;orphans:2;text-align:start;text-indent:0px;white-space:normal;widows:2;word-spacing:0px;-webkit-text-stroke-width:0px;">MESSAGE</label>
    The job exceeded the compilation timeout. (-1)
    <label class="msportalfx-text-label" data-bind="text: urlLabel" style="font-size:12px;font-weight:normal;font-family:az_ea_font, wf_segoe-ui_normal, 'Segoe UI', 'Segoe WP', Tahoma, Arial, sans-serif;line-height:normal;text-transform:uppercase;color:#2d3239;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;letter-spacing:normal;orphans:2;text-align:start;text-indent:0px;white-space:normal;widows:2;word-spacing:0px;-webkit-text-stroke-width:0px;">JOB URL</label>
    https://xxx.azuredatalakeanalytics.net/jobs/19e45d27-8887-45ef-a6a7-f26b6accb22e?api-version=2015-11-01-preview


    • Edited by Filip VR Monday, December 5, 2016 1:05 PM
    Monday, December 5, 2016 1:04 PM

Answers

  • I came to the conclusion that paramters in a file path are very expensive. I reduced my compilation time from 25 to three minuse by changing

    @fileLocation = "/path/withdate/{Year:*}/{Month:*}/{Day:*}/{Hour:*}/{Filename:*}.txt";

    to

    @fileLocation = "/path/withdate/{*}/{*}/{*}/{*}/{*}.txt";

    • Marked as answer by Filip VR Monday, December 5, 2016 1:42 PM
    Monday, December 5, 2016 1:42 PM

All replies

  • I recommend you create a support ticket.

    Monday, December 5, 2016 1:41 PM
  • I came to the conclusion that paramters in a file path are very expensive. I reduced my compilation time from 25 to three minuse by changing

    @fileLocation = "/path/withdate/{Year:*}/{Month:*}/{Day:*}/{Hour:*}/{Filename:*}.txt";

    to

    @fileLocation = "/path/withdate/{*}/{*}/{*}/{*}/{*}.txt";

    • Marked as answer by Filip VR Monday, December 5, 2016 1:42 PM
    Monday, December 5, 2016 1:42 PM