[GRASS-dev] QGIS winGRASS blank space problem

[Glynn Clements]

Hamish wrote:

> > AFAICT, $SQLTMP is unnecessary. You can just as easily use a subshell
> > with its output piped directly to db.execute; no need for a temporary
> > file.
> 
> It is used in a loop- that would make it call db.execute nine times per
> vector cat instead of once. db.execute is slow to start/stop, so it is
> much faster to use a single call with $SQLTMP.

That's incorrect.

In terms of the number of times that db.execute is run, there is no
difference between the existing code with a temporary file:

 if [ $DBFDRIVER -eq 1 ] ; then
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_n| cut -b1-10`=$n     WHERE cat=$i;" > $SQLTMP
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_min| cut -b1-10`=$min WHERE cat=$i;" >> $SQLTMP
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_max| cut -b1-10`=$max WHERE cat=$i;" >> $SQLTMP
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_range| cut -b1-10`=$range WHERE cat=$i;" >> $SQLTMP
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_mean| cut -b1-10`=$mean   WHERE cat=$i;" >> $SQLTMP
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_stddev| cut -b1-10`=$stddev     WHERE cat=$i;" >> $SQLTMP
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_variance| cut -b1-10`=$variance WHERE cat=$i;" >> $SQLTMP
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_cf_var| cut -b1-10`=$coeff_var  WHERE cat=$i;" >> $SQLTMP
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_sum| cut -b1-10`=$sum     WHERE cat=$i;" >> $SQLTMP
 else
  echo "UPDATE $VECTOR SET ${COLPREFIX}_n=$n     WHERE cat=$i;" > $SQLTMP
  echo "UPDATE $VECTOR SET ${COLPREFIX}_min=$min WHERE cat=$i;" >> $SQLTMP
  echo "UPDATE $VECTOR SET ${COLPREFIX}_max=$max WHERE cat=$i;" >> $SQLTMP
  echo "UPDATE $VECTOR SET ${COLPREFIX}_range=$range WHERE cat=$i;" >> $SQLTMP
  echo "UPDATE $VECTOR SET ${COLPREFIX}_mean=$mean   WHERE cat=$i;" >> $SQLTMP
  echo "UPDATE $VECTOR SET ${COLPREFIX}_stddev=$stddev     WHERE cat=$i;" >> $SQLTMP
  echo "UPDATE $VECTOR SET ${COLPREFIX}_variance=$variance WHERE cat=$i;" >> $SQLTMP
  echo "UPDATE $VECTOR SET ${COLPREFIX}_cf_var=$coeff_var  WHERE cat=$i;" >> $SQLTMP
  echo "UPDATE $VECTOR SET ${COLPREFIX}_sum=$sum     WHERE cat=$i;" >> $SQLTMP
 fi

 cat $SQLTMP | db.execute database=$DB_DATABASE driver=$DB_SQLDRIVER

and using a subshell:

 (
 if [ $DBFDRIVER -eq 1 ] ; then
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_n| cut -b1-10`=$n     WHERE cat=$i;"
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_min| cut -b1-10`=$min WHERE cat=$i;"
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_max| cut -b1-10`=$max WHERE cat=$i;"
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_range| cut -b1-10`=$range WHERE cat=$i;"
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_mean| cut -b1-10`=$mean   WHERE cat=$i;"
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_stddev| cut -b1-10`=$stddev     WHERE cat=$i;"
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_variance| cut -b1-10`=$variance WHERE cat=$i;"
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_cf_var| cut -b1-10`=$coeff_var  WHERE cat=$i;"
  echo "UPDATE $VECTOR SET `echo ${COLPREFIX}_sum| cut -b1-10`=$sum     WHERE cat=$i;"
 else
  echo "UPDATE $VECTOR SET ${COLPREFIX}_n=$n     WHERE cat=$i;"
  echo "UPDATE $VECTOR SET ${COLPREFIX}_min=$min WHERE cat=$i;"
  echo "UPDATE $VECTOR SET ${COLPREFIX}_max=$max WHERE cat=$i;"
  echo "UPDATE $VECTOR SET ${COLPREFIX}_range=$range WHERE cat=$i;"
  echo "UPDATE $VECTOR SET ${COLPREFIX}_mean=$mean   WHERE cat=$i;"
  echo "UPDATE $VECTOR SET ${COLPREFIX}_stddev=$stddev     WHERE cat=$i;"
  echo "UPDATE $VECTOR SET ${COLPREFIX}_variance=$variance WHERE cat=$i;"
  echo "UPDATE $VECTOR SET ${COLPREFIX}_cf_var=$coeff_var  WHERE cat=$i;"
  echo "UPDATE $VECTOR SET ${COLPREFIX}_sum=$sum     WHERE cat=$i;"
 fi
 ) | db.execute database=$DB_DATABASE driver=$DB_SQLDRIVER

In both cases, db.execute is run once per category.

There might be further performance gains from moving db.execute
outside of the loop, but that's true regardless of whether a temporary
file is used.

Also, I would be inclined to clean up the above by looping over the
column names i.e.:

 (
  for var in n min max range mean stddev variance cf_var sum ; do
  eval val=\${$var}
  if [ $DBFDRIVER -eq 1 ] ; then
   col="`echo ${COLPREFIX}_${var}| cut -b1-10`"
  else
   col="${COLPREFIX}_${var}"
  fi
  echo "UPDATE $VECTOR SET ${col}=${val} WHERE cat=$i;"
 ) | db.execute database=$DB_DATABASE driver=$DB_SQLDRIVER

-- 
Glynn Clements <glynn at gclements.plus.com>