Bug report #5322
ODBC connection window queries all database content
|Affected QGIS version:||master||Regression?:||No|
|Operating System:||All||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||15024|
odbc interface in QGis does "SELECT * from mytable" for ALL tables when trying to collect table list, just before allowing user to choose which table to open.
This is very slow, unefficient, and dangerous for big databases with number of tables and huge ones. I almost crashed one Oracle instance this morning, waiting for Qgis to unfreeze.
IMHO it needs urgent fixing since that bad behaviour could lead to very bad advertising on QGis in system administrator and database administrator communities.
SQL "LIMIT " clause is not implemented on every database.. Indeed, qgis should retrieve table lists with ODBC standard function "SQLTables()" http://msdn.microsoft.com/en-us/library/windows/desktop/ms711831(v=vs.85).aspx. Is it available through OGR?
Sorry, not able to sponsor right at the moment (2 months to go?)
tested on 1.7.4 and trunk f3b78ef.
#2 Updated by Regis Haubourg over 10 years ago
partially answering to myself , ogr does support odbc SQLTables() : [http://www.osgeo.org/pipermail/gdal-dev/2009-October/022477.html]
#4 Updated by Hugo Mercier over 10 years ago
I couldn't find any explicit "SELECT *" in the ODBC handling code. So I am not sure SQLTables() is the solution.
I confirm ODBC connection can be very slow, which is not something we want.
From my own investigations, it seems a call to OGR_L_GetFeatureCount() is made (qgsprovider.cpp, method 'subLayers()') with the 'FORCE' option (see http://www.gdal.org/ogr/ogr__api_8h.html) which can, as expected, be very slow. I don't know if the number of features is a critical information to retrieve, but a quick work-around would be not to force the feature counting.