Bug report #5322

ODBC connection window queries all database content

Added by Regis Haubourg over 12 years ago. Updated over 12 years ago.

Status:Closed
Priority:High
Assignee:-
Category:Data Provider
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

Description

Hi all,
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?)
Régis

tested on 1.7.4 and trunk f3b78ef.

Related issues

Related to QGIS Application - Bug report #5844: When adding a gpx the dialog shows "Nb of features" all s... Closed 2012-06-21

Associated revisions

Revision bf82454d
Added by Jürgen Fischer over 12 years ago

cache sublayer feature counts and don't retrieve expensive ones (fixes #5322)

History

#1 Updated by Regis Haubourg over 12 years ago

I forgot to check "causes crach" checkbox.. Not true, but freezes Qgis until OGR as read every single line in all database.. or until database crashes.

#2 Updated by Regis Haubourg over 12 years ago

partially answering to myself , ogr does support odbc SQLTables() : [http://www.osgeo.org/pipermail/gdal-dev/2009-October/022477.html]

#3 Updated by Giovanni Manghi over 12 years ago

  • Priority changed from 6 to High

#4 Updated by Hugo Mercier over 12 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.

#5 Updated by Regis Haubourg over 12 years ago

SQL "select * " was seen when logging QGIS ODBC connection queries to postgresql. Maybe this is not coded so, but this is how it behaves.
If it is an ogr behaviour, please confirm so that I submit an issue there.
regis

#6 Updated by Hugo Mercier over 12 years ago

Not sure yet if it comes from the ODBC driver.

What is done in QGIS is something like OGR_L_GetFeatureCount( OGR_GetLayer( ... ), 1 ). Which has great chance of involving a "SELECT *".

Maybe we could use something more "direct" in QGIS to count features.

#7 Updated by Jürgen Fischer over 12 years ago

  • Status changed from Open to Closed

Also available in: Atom PDF