Feature request #16251

Use bind variables in Oracle SQL

Added by Simon Greener over 3 years ago. Updated over 3 years ago.

Status:Closed
Priority:Normal
Assignee:Jürgen Fischer
Category:Data Provider/Oracle
Pull Request or Patch supplied:No Resolution:
Easy fix?:No Copied to github as #:24161

Description

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

Associated revisions

Revision fb409b59
Added by Jürgen Fischer over 3 years ago

oracle provider: use prepared statements with parameters (implements #16251)

History

#1 Updated by Jürgen Fischer over 3 years ago

  • Subject changed from Lack of use of bind variables in Oracle SQL to Use bind variables in Oracle SQL
  • Tracker changed from Bug report to Feature request

#2 Updated by Jürgen Fischer over 3 years ago

  • Status changed from Open to Closed

#3 Updated by Simon Greener over 3 years ago

Jurgen,

I have scanned the changeset and what you have done looks good.

Thanks for the fast response.

regards
Simon

Also available in: Atom PDF