QGIS » QGIS Application

The use of bind variables in SQL is considered best practice (see Tom Kyte's articles).

Currently the QGIS Oracle Data Provider does not use bind variables.

This can be shown by:

1. Looking at qgsoracleprovider.cpp
2. Turning on SQL Tracing when QGIS (Server) is connected to the database.

qgsoracleprovider.cpp contains code like this:

        if ( exec( qry, QString( "SELECT sdo_lb,sdo_ub FROM mdsys.all_sdo_geom_metadata m, table(m.diminfo) WHERE owner=%1 AND table_name=%2 AND column_name=%3 AND sdo_dimname='X'" )
                   .arg( quotedValue( mOwnerName ) )
                   .arg( quotedValue( mTableName ) )
                   .arg( quotedValue( mGeometryColumn ) ) ) && qry.next() )
        {
          mLayerExtent.setXMinimum( qry.value( 0 ).toDouble() );
          mLayerExtent.setXMaximum( qry.value( 1 ).toDouble() );

          if ( exec( qry, QString( "SELECT sdo_lb,sdo_ub FROM mdsys.all_sdo_geom_metadata m, table(m.diminfo) WHERE owner=%1 AND table_name=%2 AND column_name=%3 AND sdo_dimname='Y'" )
                     .arg( quotedValue( mOwnerName ) )
                     .arg( quotedValue( mTableName ) )
                     .arg( quotedValue( mGeometryColumn ) ) )  && qry.next() )
          {
            mLayerExtent.setYMinimum( qry.value( 0 ).toDouble() );
            mLayerExtent.setYMaximum( qry.value( 1 ).toDouble() );
            return mLayerExtent;
          }
        }

Which produces the following queries in the database:

SELECT sdo_lb,sdo_ub FROM mdsys.all_sdo_geom_metadata m, table(m.diminfo) WHERE owner='QGIS' AND table_name='FOO' AND column_name='GEOM' AND sdo_dimname='X'

SELECT sdo_lb,sdo_ub FROM mdsys.all_sdo_geom_metadata m, table(m.diminfo) WHERE owner='QGIS' AND table_name='FOO' AND column_name='GEOM' AND sdo_dimname='Y'

Oracle sees both statements as different (unless bind variable peeking is on) when they are actually the same.
When you add in the fact that these queries are multiplied across multiple Oracle tables per project the number of individual statements that Oracle sees is greatly increased.

The C++ code should be changed to use bind variables so that Oracle will place only one statement in its library cache which it will reuse (hence inprove performance):

SELECT sdo_lb,sdo_ub FROM mdsys.all_sdo_geom_metadata m, table(m.diminfo) WHERE owner=:1 AND table_name=:2 AND column_name=:3 AND sdo_dimname=:4;

The problem affects all SQL generated by the QGIS Oracle Data Provider.

regards
Simon