ExecuteSQL () speed.

[grumbachr]

In a solution I'm writing I've used several ExecuteSQL () unstirred calculations.    Some of the first were written very similar to this as I have a need to use variables to grab information. Does it slow things down to incase it in a  Let function (see Let Code)? In several areas I could easily rewrite it to be more clean as I don't need to base anything off of variables (see clean code example).  

 

Let Code..

Let ( [  
$event = event_item::_kf_event; 
$item = event_item::_kf_item;
$type = 1] ; 
 
ExecuteSQL ( 

"SELECT SUM (Qty)
FROM "itemtrans" a
WHERE a."_kf_event" = ? AND a."_kf_item" = ? AND a."transtype" = ?" ; 

"    " ; " " ; 

$event ; $item ; $type


 ))

Clean Code.

ExecuteSQL (  "SELECT SUM (Qty) FROM "itemtrans" a WHERE a."_kf_event" = ? AND a."_kf_item" = ? AND a."transtype" = ?" ;  " " ; " " ;  _kf_event ; _kf_item ; 1  )

Do you think I will see any noticeable improvement with what I'll call cleaner code. 

I really do like ExecuteSQL () as it's kept my TO's way down but for some reason I was hoping it would be faster

[comment]

Do you think I will see any noticeable improvement with what I'll call cleaner code.

 

I doubt it very much. I suspect the calculation engine calculates the SQL query as text first, then submits it. The first part is negligible.

 

 

Not directly related to your question, but it is extremely bad practice to use $variables in a Let() calculation. These variables are NOT cleared out when the Let statements exits, and in addition to wasting RAM you run into a danger of variables colliding.

 

Note also that setting a variable to a field or to a constant does not really accomplish anything.

[Lee Smith]

I moved your topic here "The Left Brain" to "FQL or FileMaker Query Language” because this is the topic for SQL questions.

[Wim Decorte]

If you hit any performance issues it will be because:

 

- ExecuteSQL() is very slow when there is an open record in the user's session in the target table; and since you are doing this at the schema level you don't really have control over open records

 

- aggregate functions like SUM() in a SQL query make it slower, sometimes very much so

 

Internally we're setting our own standards to avoid ExecuteSQL() in

- field definitions

- tooltips

- conditional formatting

-...

and limit it to scripts, where we have more control over the environment

 

and to try and avoid SQL functions like COUNT(), MAX(),...

and to try and avoid JOINs

 

On the plus side, simple SELECTs are blazingly fast.

[grumbachr]

Thanks for the great feedback.  I'll take a look at cleaning things up a bit more.

For now I think I'll stick to letting ExecuteSQL() do the SUM  as a rewrite on that would just be to time consuming.  As I don't even know if it will be a huge benefit in speed to get away from it at this point. 

 

Sorry about posting in the wrong area, I forgot all about the FQL section.  

[Fitch]

Yes to what Wim and comment said. I'll add that if you're trying to write clean (i.e. readable, maintanable) code, I'd suggest something more like:

Let ( query = 
"SELECT SUM (Qty)
FROM "itemtrans" a
WHERE a."_kf_event" = ? AND a."_kf_item" = ? AND a."transtype" = ?" ;

ExecuteSQL ( 
 query ; " " ; " " ;
 event_item::_kf_event ; event_item::_kf_item ; 1
 )
)