AnsweredAssumed Answered

ExecuteSQL Slow Performance MAX function

Question asked by taylorsharpe on Feb 1, 2019
Latest reply on Feb 8, 2019 by jormond

I know this has been documented before, but certain functions in ExecuteSQL are excruciatingly slow.  The SQL interpreter engine in FM needs a major overhaul.  Here is an example for creating the next Invoice number using SQL:

 

ORIGINAL CALC

 

     Let ( [

 

     T1 = Get ( CurrentTimeUTCMilliseconds ) ;

     SQL1 = "SELECT ¶

       MAX ( invoice_no ) ¶

       FROM ¶

       COMPANY_invoices" ;

     R1 = ExecuteSQL ( SQL1 ; ¶ ; ¶ ) + 1 ;

     T2 = Get ( CurrentTimeUTCMilliseconds ) ;

     T3 = T2 - T1

 

     ] ; R1

 

Takes 3260995 milliseconds to run with a database of 109175 records. 

 

 

NEW CALC:

 

     Let ( [

 

     T1 = Get ( CurrentTimeUTCMilliseconds ) ;

     F10 = GetFieldName ( invoices::invoice_no ) ;

     F11 = Substitute ( F10 ; "::" ; ¶ ) ;

     F12 = Quote ( GetValue ( F11 ; 1 ) ) ;

     F13 = Quote ( GetValue ( F11 ; 2 ) ) ;

     F14 = F12 & "." & F13 ;

 

     SQL1 = "SELECT ¶

            COUNT ( * ) ¶

       FROM ¶

            " & F12 ;

     R1 = ExecuteSQL ( SQL1 ; ¶ ; ¶ ) ;

 

     SQL2 = "SELECT ¶

            " & F14 & " ¶

       FROM ¶

            " & F12 & " ¶

       OFFSET " & R1 - 100 & " ROWS" ;

     R2 = ExecuteSQL ( SQL2 ; ¶ ; ¶ ) ;

     R3 = SortValues ( R2 ; -2 ) ;

     R4 = GetValue ( R3 ; 1 ) ;

     R5 = R4 + 1 ;

 

     T2 = Get ( CurrentTimeUTCMilliseconds ) ;

     T3 = T2 - T1

 

     ] ; R5 )

 

 

This New Calc with TWO SQL calls takes 95 milliseconds to run.  Basically it grabs the last 100 records and outside of SQL it sorts them and grabs the highest number.  It's a workaround for what should be incredibly fast on the first Calc. 

 

The New Calc is 34326 times faster than the ExecuteSQL MAX function.  Goodness. 

 

The MIN and AVG functions have the same issue and JOINS are really slow too. 

 

This is just ridiculous performance and not useable.  And I expect FM to handle tables with 100,000 reasonably well.  

Outcomes