Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
Use extents table for ms sql layers for faster loading
- Also move feature count logic
  • Loading branch information
NathanW2 committed Jul 2, 2013
1 parent df398a1 commit 702b14a
Showing 1 changed file with 82 additions and 57 deletions.
139 changes: 82 additions & 57 deletions src/providers/mssql/qgsmssqlprovider.cpp
Expand Up @@ -568,52 +568,55 @@ void QgsMssqlProvider::uniqueValues( int index, QList<QVariant> &uniqueValues, i
}


// update the extent, feature count, wkb type and srid for this layer
// update the extent, wkb type and srid for this layer
void QgsMssqlProvider::UpdateStatistics( bool estimate )
{
mNumberFeatures = 0;
// get features to calculate the statistics
QString statement;

QSqlQuery query = QSqlQuery( mDatabase );
query.setForwardOnly( true );

// Get the extents from the geometry_columns table to speed up load times.
statement = QString( "SELECT min_x, min_y, max_x, max_y from geometry_columns where f_table_schema = '%1' and f_table_name = '%2'" ).arg( mSchemaName ).arg( mTableName );
// Get the extents from the spatial index table to speed up load times.
// We have to use max() and min() because you can have more then one index but the biggest area is what we want to use.
QString sql = "SELECT min(bounding_box_xmin), min(bounding_box_ymin), max(bounding_box_xmax), max(bounding_box_ymax)"
" FROM sys.spatial_index_tessellations WHERE object_id = OBJECT_ID('[%1].[%2]')";

statement = QString (sql).arg( mSchemaName )
.arg( mTableName );

if ( query.exec( statement ) )
{
QgsDebugMsg("Found extents in spatial index");
if ( query.next() )
{
if ( !query.value( 0 ).isNull() || !query.value( 1 ).isNull() ||
!query.value( 2 ).isNull() || !query.value( 3 ).isNull() )
{
mExtent.setXMinimum( query.value( 0 ).toDouble() );
mExtent.setYMinimum( query.value( 1 ).toDouble() );
mExtent.setXMaximum( query.value( 2 ).toDouble() );
mExtent.setYMaximum( query.value( 3 ).toDouble() );
return;
}
}
}

// If we can't find the extents in the geometry_columns table just do what we normally do.
bool readAll = false;
QgsDebugMsg(query.lastError().text());

// If we can't find the extents in the spatial index table just do what we normally do.
bool readAllGeography = false;
if ( estimate )
{
if ( mGeometryColType == "geometry" )
statement = QString( "select min([%1].STPointN(1).STX), min([%1].STPointN(1).STY), max([%1].STPointN(1).STX), max([%1].STPointN(1).STY), COUNT([%1])" ).arg( mGeometryColName );
statement = QString( "select min([%1].STPointN(1).STX), min([%1].STPointN(1).STY), max([%1].STPointN(1).STX), max([%1].STPointN(1).STY)" ).arg( mGeometryColName );
else
statement = QString( "select min([%1].STPointN(1).Long), min([%1].STPointN(1).Lat), max([%1].STPointN(1).Long), max([%1].STPointN(1).Lat), COUNT([%1])" ).arg( mGeometryColName );
statement = QString( "select min([%1].STPointN(1).Long), min([%1].STPointN(1).Lat), max([%1].STPointN(1).Long), max([%1].STPointN(1).Lat)" ).arg( mGeometryColName );
}
else
{
if ( mGeometryColType == "geometry" )
statement = QString( "select min([%1].STEnvelope().STPointN(1).STX), min([%1].STEnvelope().STPointN(1).STY), max([%1].STEnvelope().STPointN(3).STX), max([%1].STEnvelope().STPointN(3).STY), count([%1])" ).arg( mGeometryColName );
statement = QString( "select min([%1].STEnvelope().STPointN(1).STX), min([%1].STEnvelope().STPointN(1).STY), max([%1].STEnvelope().STPointN(3).STX), max([%1].STEnvelope().STPointN(3).STY)" ).arg( mGeometryColName );
else
{
statement = QString( "select [%1]" ).arg( mGeometryColName );
readAll = true;
readAllGeography = true;
}
}

Expand All @@ -633,52 +636,45 @@ void QgsMssqlProvider::UpdateStatistics( bool estimate )
QgsDebugMsg( msg );
}

if ( query.isActive() )
if ( !query.isActive() )
{
return;
}

QgsGeometry geom;
if ( !readAllGeography )
{
QgsGeometry geom;
if ( !readAll )
if ( query.next() )
{
if ( query.next() )
{
mExtent.setXMinimum( query.value( 0 ).toDouble() );
mExtent.setYMinimum( query.value( 1 ).toDouble() );
mExtent.setXMaximum( query.value( 2 ).toDouble() );
mExtent.setYMaximum( query.value( 3 ).toDouble() );
mNumberFeatures = query.value( 4 ).toInt();
}
mExtent.setXMinimum( query.value( 0 ).toDouble() );
mExtent.setYMinimum( query.value( 1 ).toDouble() );
mExtent.setXMaximum( query.value( 2 ).toDouble() );
mExtent.setYMaximum( query.value( 3 ).toDouble() );
return;
}
else
{
// read all features
while ( query.next() )
{
QByteArray ar = query.value( 0 ).toByteArray();
unsigned char* wkb = parser.ParseSqlGeometry(( unsigned char* )ar.data(), ar.size() );
if ( wkb )
{
geom.fromWkb( wkb, parser.GetWkbLen() );
QgsRectangle rect = geom.boundingBox();
}

if ( mNumberFeatures > 0 )
{
if ( rect.xMinimum() < mExtent.xMinimum() )
mExtent.setXMinimum( rect.xMinimum() );
if ( rect.yMinimum() < mExtent.yMinimum() )
mExtent.setYMinimum( rect.yMinimum() );
if ( rect.xMaximum() > mExtent.xMaximum() )
mExtent.setXMaximum( rect.xMaximum() );
if ( rect.yMaximum() > mExtent.yMaximum() )
mExtent.setYMaximum( rect.yMaximum() );
}
else
{
mExtent = rect;
mWkbType = geom.wkbType();
mSRId = parser.GetSRSId();
}
++mNumberFeatures;
}
}
// We have to read all the geometry if readAllGeography is true.
while ( query.next() )
{
QByteArray ar = query.value( 0 ).toByteArray();
unsigned char* wkb = parser.ParseSqlGeometry(( unsigned char* )ar.data(), ar.size() );
if ( wkb )
{
geom.fromWkb( wkb, parser.GetWkbLen() );
QgsRectangle rect = geom.boundingBox();

if ( rect.xMinimum() < mExtent.xMinimum() )
mExtent.setXMinimum( rect.xMinimum() );
if ( rect.yMinimum() < mExtent.yMinimum() )
mExtent.setYMinimum( rect.yMinimum() );
if ( rect.xMaximum() > mExtent.xMaximum() )
mExtent.setXMaximum( rect.xMaximum() );
if ( rect.yMaximum() > mExtent.yMaximum() )
mExtent.setYMaximum( rect.yMaximum() );

mWkbType = geom.wkbType();
mSRId = parser.GetSRSId();
}
}
}
Expand All @@ -704,7 +700,36 @@ QGis::WkbType QgsMssqlProvider::geometryType() const
*/
long QgsMssqlProvider::featureCount() const
{
return mNumberFeatures;
// Return the count that we get from the subset.
if ( !mSqlWhereClause.isEmpty() )
return mNumberFeatures;

// If there is no subset set we can get the count from the system tables.
// Which is faster then doing select count(*)
QSqlQuery query = QSqlQuery( mDatabase );
query.setForwardOnly( true );

QString sql = "SELECT rows"
" FROM sys.tables t"
" JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0,1)"
" WHERE SCHEMA_NAME(t.schema_id) = '%1' AND OBJECT_NAME(t.OBJECT_ID) = '%2'";

QString statement = QString (sql).arg( mSchemaName )
.arg( mTableName );

if ( query.exec( statement ) )
{
if ( query.next() )
{
return query.value(0).toInt();
}
}
else
{
// We couldn't get the rows from the sys tables. Can that ever happen?
// Should just do a select count(*) here.
return -1;
}
}

const QgsFields & QgsMssqlProvider::fields() const
Expand Down

1 comment on commit 702b14a

@NathanW2
Copy link
Member Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Should have been "Use spatial index table for ms sql layers for faster loading"

Please sign in to comment.