1 Reply Latest reply on Jun 2, 2014 3:43 AM by wimdecorte

    Complex ExecuteSQL Statement

    russbrad

      Hi

       

      I have asked a friend with SQL skills to help me construct a complex SQL expression. In essence we are trying to construct a report that totals line item costs (accross various order components) by account codes.

      Currently the script does work - except it takes 5 minutes at least!!! The file is being shared via FMS 12. How can a script take 5 minutes to run??? There are only about 100 orders in the database at the moment. Are we expecting too much from ExectuteSQL or misusing it??? Any comments welcome :-)

       

       

      Let([

      oid = Orders::id;

      vid = ExecuteSQL("select "t%CurrentVariationID" from Orders where id = ?";"";"";oid);

      val = 0 + ExecuteSQL("select "t%CurrentVariation" from Orders where id = ?";"";"";oid) ;

      sum = ExecuteSQL("select Sum("t%Cost" * C.nQuantity) from

      LineItemCost L

      inner join ComponentProduct P on P.id_LineItem = L.id_Lineitem

      inner join Components C on P.id_Component = C.id

      inner join VariationComponent V on C.id = V.id_Component

      Where V.id_Variation = ? and "t%Cost" > 0";"";"";vid);

      com = 0 +ExecuteSQL("select nCommission from Variations where id = ?";"";"";vid);

      cam = (com/100) * val;

      val0 = val;

      val = val - cam;

      lid = ExecuteSQL("select tAccountCode, Sum(Round("t%Cost" * C.nQuantity/ ? * ?,2)) from

      LineItemCost L

      inner join ComponentProduct P on P.id_LineItem = L.id_Lineitem

      inner join Components C on P.id_Component = C.id

      inner join VariationComponent V on C.id = V.id_Component

      Where V.id_Variation = ? and "t%Cost" > 0 Group by tAccountCode";" 0";"";sum;val ;vid);

      lid0 = lid;

      lid = If(cam>0; "4-0600 0" & #.##(cam) & ¶;"") & lid;

      gst = ExecuteSQL("select nGST from Admin";"";"");

      cnt = ValueCount(lid);

      lvs = LeftValues(lid;cnt-1);

      slvs = val0-SumRightWords(lvs);

      rv = RightValues(lid;1);

      rvrw = LeftWords(rv;1) & " 0" & slvs;

      rtn = lvs & rvrw & ¶;

      wgst = InsertExGSTRightWords(rtn;gst)

      ];

      wgst

      )

        • 1. Re: Complex ExecuteSQL Statement
          wimdecorte

          There are at least 4 tables involved, what's the record count for each of those?

           

          I would start by breaking up this big calc and executing each sql individually to see which one is responsible for the slowdown.  It is very likely going to be the two big ones that SUM() and have a bunch of JOINs.  ExecuteSQL() has a real performance dropoff linear with its complexity.  You may be able to do multiple simpler SQL calls to get the same result as the complex one but faster.