[SQL Query] any efficient way ?

[stockton]

hello,
(I am using MySQL) There i use SUM a lot of times.. so..
1.- the mysql engine is calculating each time the value of these
or
2.- use a cached value
is the answer is the 1st, is there any way more efficient than doin this query ??


sSQL = "SELECT "
sSQL = sSQL & "cue_id as 'Nro Cuenta', "
sSQL = sSQL & "cue_descripcion as 'Descripcion Cuenta', "
sSQL = sSQL & "SUM(IFNULL(asidet_debe,0)) as 'Debe', "
sSQL = sSQL & "SUM(IFNULL(asidet_haber,0)) as 'Haber', "
sSQL = sSQL & "IF(SUM(IFNULL(asidet_debe,0)) - SUM(IFNULL(asidet_haber,0)) > 0,SUM(IFNULL(asidet_debe,0)) - SUM(IFNULL(asidet_haber,0)),0) as 'Saldo Deudor', "
sSQL = sSQL & "IF(SUM(IFNULL(asidet_debe,0)) - SUM(IFNULL(asidet_haber,0)) < 0,SUM(IFNULL(asidet_haber,0)) - SUM(IFNULL(asidet_debe,0)),0) as 'Saldo Acreedor' "
sSQL = sSQL & "FROM "
sSQL = sSQL & "contab_cuentas_mayor LEFT JOIN contab_asientos_detalle "
sSQL = sSQL & "ON asidet_cue_id = cue_id "
sSQL = sSQL & "GROUP BY cue_id"


thanks in advance,
Ricardo

[si_the_geek]

Thankfully most (maybe all?) SQL interpreters have the intelligence to realise that you are asking for the same information, and will effectively use cached values. I would assume that MySQL works this way.

If you want to check, just compare times with two versions of the query. For the second version, replace the values such as SUM(IFNULL(asidet_debe,0)) (after the first instance) with a fixed number such as 0. If the query times are very similar then it will be caching them.

If the second is faster, try changing the 0's to SUM(IFNULL(0,0)), and see how the speed is then.

[stockton]

thanks for your reply, tomorrow i'll try to do that kind of benchmark to see what are the times