disabled_ScottKoontz

ExecuteSQL - amazing, and slow

Discussion created by disabled_ScottKoontz on Jul 10, 2013
Latest reply on Jul 11, 2013 by gdurniak

Nothing radially new to report, but I'm so enamored with what can be done with ExecutSQL, and yet I have very few uses where it remains implemented simply because of speed - especially on FMS with many records. Front-end stuff seems to be the best realistic use.

 

I was in need of 12 columns in a report, one for each month in the past. Simple (once I got past syntax errors) and worked well locally and with less than a million records.

 

ExecuteSQL ( "SELECT SUM(

CASE ? WHEN 'Standard' THEN Standard_CM_TY WHEN '9-Liter' THEN liter9_CM_TY WHEN 'Dollar' THEN Dollar_CM_TY WHEN 'Bottles' THEN Bottles_CM_TY END

)

from DATA

WHERE PRODUCT_KEY = ? and STORE_KEY = ? and Premise = ? and Yr_Mth = ?"

; "" ; "" ;

$$units ; PRODUCT_KEY ; STORE_KEY ; Premise ; Yr_Mth - 11 ) ;

 

Put the above on the server (60 million records) and it is so slow that I cannot ask the client to run reports. Even overnight can be out of the question.

Outcomes