[mssql] Speed up loading of larger views (fixes #27618)

The extent estimation was taking a long time for views because we can't use spatial index statistics - the code was doing full scan of the view. Now we try to scan just 1% of the rows to get a good estimate. If that fails (e.g. when the table is not very big) we fall back to the full scan. Test results: a view with 90k rows was taking 2.5s to estimate extents during load - now it's <0.1s
qgis · Oct 11, 2019 · 043dc61 · 043dc61
1 parent 1a8ca96
commit 043dc61
Showing 1 changed file with 32 additions and 0 deletions.
diff --git a/src/providers/mssql/qgsmssqlprovider.cpp b/src/providers/mssql/qgsmssqlprovider.cpp
@@ -726,6 +726,10 @@ void QgsMssqlProvider::UpdateStatistics( bool estimate ) const
       else
         statement = QStringLiteral( "select min(case when ([%1].STIsValid() = 1) THEN [%1].STPointN(1).Long  else NULL end), min(case when ([%1].STIsValid() = 1) THEN [%1].STPointN(1).Lat else NULL end), max(case when ([%1].STIsValid() = 1) THEN [%1].STPointN(1).Long else NULL end), max(case when ([%1].STIsValid() = 1) THEN [%1].STPointN(1).Lat else NULL end)" ).arg( mGeometryColName );
     }
+
+    // we will first try to sample a small portion of the table/view, so the count of rows involved
+    // will be useful to evaluate if we have enough data to use the sample
+    statement += ", count(*)";
   }
   else
   {
@@ -750,6 +754,34 @@ void QgsMssqlProvider::UpdateStatistics( bool estimate ) const
     statement += " where (" + mSqlWhereClause + ')';
   }
 
+  if ( estimate )
+  {
+    // Try to use just 1% sample of the whole table/view to limit the amount of rows accessed.
+    // This heuristic may fail (e.g. when the table is small or when primary key values do not
+    // get sampled enough) so in case we do not have at least 10 features, we fall back to full
+    // traversal of the table/view
+
+    const int minSampleCount = 10;
+
+    // See https://docs.microsoft.com/en-us/previous-versions/software-testing/cc441928(v=msdn.10)
+    QString sampleFilter = QString( "(ABS(CAST((BINARY_CHECKSUM([%1])) as int)) % 100) = 42" ).arg( mFidColName );
+
+    QString statementSample;
+    if ( mSqlWhereClause.isEmpty() )
+      statementSample = statement + " WHERE " + sampleFilter;
+    else
+      statementSample = statement + " AND " + sampleFilter;
+    if ( query.exec( statementSample ) && query.next() &&
+         !query.value( 0 ).isNull() && query.value( 4 ).toInt() >= minSampleCount )
+    {
+      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 ( !query.exec( statement ) )
   {
     QgsDebugMsg( query.lastError().text() );