Optimize SpatiaLite feature reading speed

wonder-sk · wonder-sk · commit 57ea79f605aa · 2011-11-17T13:41:03.000-03:00
diff --git a/src/providers/spatialite/qgsspatialiteprovider.cpp b/src/providers/spatialite/qgsspatialiteprovider.cpp
@@ -795,8 +795,7 @@ bool QgsSpatiaLiteProvider::getFeature( sqlite3_stmt *stmt, bool fetchGeometry,
         }
         if ( fetchGeometry )
         {
-          QString geoCol = QString( "AsBinary(%1)" ).arg( quotedIdentifier( mGeometryColumn ) );
-          if ( strcasecmp( geoCol.toUtf8().constData(), sqlite3_column_name( stmt, ic ) ) == 0 )
+          if ( ic == mGeomColIdx )
           {
             if ( sqlite3_column_type( stmt, ic ) == SQLITE_BLOB )
             {
@@ -3247,6 +3246,7 @@ bool QgsSpatiaLiteProvider::prepareStatement(
   QString primaryKey = !isQuery ? "ROWID" : quotedIdentifier( mPrimaryKey );
 
   QString sql = QString( "SELECT %1" ).arg( primaryKey );
+  int colIdx = 1; // column 0 is primary key
   for ( QgsAttributeList::const_iterator it = fetchAttributes.constBegin(); it != fetchAttributes.constEnd(); ++it )
   {
     const QgsField & fld = field( *it );
@@ -3258,10 +3258,12 @@ bool QgsSpatiaLiteProvider::prepareStatement(
       fieldname = QString( "AsText(%1)" ).arg( fieldname );
     }
     sql += "," + fieldname;
+    colIdx++;
   }
   if ( fetchGeometry )
   {
     sql += QString( ", AsBinary(%1)" ).arg( quotedIdentifier( mGeometryColumn ) );
+    mGeomColIdx = colIdx;
   }
   sql += QString( " FROM %1" ).arg( mQuery );
 
diff --git a/src/providers/spatialite/qgsspatialiteprovider.h b/src/providers/spatialite/qgsspatialiteprovider.h
@@ -382,6 +382,9 @@ class QgsSpatiaLiteProvider: public QgsVectorDataProvider
 
     const QgsField & field( int index ) const;
 
+    /** geometry column index used when fetching geometry */
+    int mGeomColIdx;
+
     /**
     * internal utility functions used to handle common SQLite tasks
     */

Original file line number	Diff line number	Diff line change
`@@ -795,8 +795,7 @@ bool QgsSpatiaLiteProvider::getFeature( sqlite3_stmt *stmt, bool fetchGeometry,`
`795`	`795`	`}`
`796`	`796`	`if ( fetchGeometry )`
`797`	`797`	`{`
`798`		`- QString geoCol = QString( "AsBinary(%1)" ).arg( quotedIdentifier( mGeometryColumn ) );`
`799`		`- if ( strcasecmp( geoCol.toUtf8().constData(), sqlite3_column_name( stmt, ic ) ) == 0 )`
	`798`	`+ if ( ic == mGeomColIdx )`
`800`	`799`	`{`
`801`	`800`	`if ( sqlite3_column_type( stmt, ic ) == SQLITE_BLOB )`
`802`	`801`	`{`
`@@ -3247,6 +3246,7 @@ bool QgsSpatiaLiteProvider::prepareStatement(`
`3247`	`3246`	`QString primaryKey = !isQuery ? "ROWID" : quotedIdentifier( mPrimaryKey );`
`3248`	`3247`
`3249`	`3248`	`QString sql = QString( "SELECT %1" ).arg( primaryKey );`
	`3249`	`+ int colIdx = 1; // column 0 is primary key`
`3250`	`3250`	`for ( QgsAttributeList::const_iterator it = fetchAttributes.constBegin(); it != fetchAttributes.constEnd(); ++it )`
`3251`	`3251`	`{`
`3252`	`3252`	`const QgsField & fld = field( *it );`
`@@ -3258,10 +3258,12 @@ bool QgsSpatiaLiteProvider::prepareStatement(`
`3258`	`3258`	`fieldname = QString( "AsText(%1)" ).arg( fieldname );`
`3259`	`3259`	`}`
`3260`	`3260`	`sql += "," + fieldname;`
	`3261`	`+ colIdx++;`
`3261`	`3262`	`}`
`3262`	`3263`	`if ( fetchGeometry )`
`3263`	`3264`	`{`
`3264`	`3265`	`sql += QString( ", AsBinary(%1)" ).arg( quotedIdentifier( mGeometryColumn ) );`
	`3266`	`+ mGeomColIdx = colIdx;`
`3265`	`3267`	`}`
`3266`	`3268`	`sql += QString( " FROM %1" ).arg( mQuery );`
`3267`	`3269`