search

qgis / QGIS

QGIS is a free, open source, cross platform (lin/win/mac) geographical information system (GIS)
https://qgis.org
GNU General Public License v2.0
10.03k stars 2.92k forks source link

Oracle provider - speed of listing #17410

Open qgib opened 10 years ago

qgib commented 10 years ago

Author Name: Jonathan Moules (Jonathan Moules) Original Redmine Issue: 8689

Redmine category:data_provider/oracle

Not sure if this qualifies as a bug or feature request.

My Oracle is listing several hundred tables (500?), the problem is that the scanning for the metadata for these tables can take an unreasonably long time. Take the following, all done during the same QGIS-session:

First time.
- 117seconds to list everything on our live database
"Only look in meta data table"
"use estimated table metadata"
"only existing geometry types"
were all selected.

Second time (without having closed QGIS remember!)
- 63 seconds.

The third time (instantly after the second)
- 3 seconds.

Fourth time
- 11 seconds

There are two things here:

a) If a full scan of everything must be made, can the results be stored locally/in memory for the session? If QGIS feels paranoid, when a user has selected a layer to add it, QGIS can re-scan that table to make sure stuff hasn't changed.

b) The better option IMHO - don't scan the metadata until the user is actually adding it to QGIS. Most users probably don't care what the SRID, Geometry column, or datatype is when they're adding their data so won't miss it on that screen. You could add a checkbox for it to show it (or not show it, depends on the default).

I think both of these would provide considerable increases to the speed of listing tables in the QGIS Oracle provider where there are lots of tables.

Related issue(s): #21862 (relates) Redmine related issue(s): 13838

qgib commented 10 years ago

Author Name: Jürgen Fischer (@jef-n)

qgib commented 10 years ago

Author Name: Martin Dobias (@wonder-sk)

The option a) has been implemented in 5b72da

For the option b) there is still area for improvement - similarly to Postgres provider, detect the layer metadata in a second step (in ba ckground thread)

qgib commented 8 years ago

Author Name: Ken Karlsson (Ken Karlsson)

Hi!

We have the same problem, but we have to wait for hours before all tables are listed when trying to add an Oracle Spatial-layer. We would like to be able to first choose the table we want to add, and then only scan that table's metadata when added if necessary. This issue seems to be open, but is anyone working on this? We are using QGIS 2.14.1. Thanks in advance!

Best regards, Ken Karlsson

qgib commented 7 years ago

Author Name: Giovanni Manghi (@gioman)

qgib commented 6 years ago

Author Name: Russell Foster (Russell Foster)

Hi All, At work we have recently been trialling QGIS as an alternative to some proprietary GIS's. The problem outlined above is our main issue with the software. It takes so long to scan the database tables, gathering all the info it wants. We have connections to databases that have hundreds of spatial objects available, and directories with hundreds of files. It would make sense to use Jonathan's option 'b' to us (mentioned above). We know what we're looking for in the databases, we know it's going to be in the projection we made it, all we need are a list of names so we can click on one to add. A simple query on a spatial db could simply cross check with say ALL_SDO_GEOM_METADATA (for Oracle) just to make sure something is spatial, and that would be enough. The details only become important when trying to add the layer. I hope this is something that gets fixed in later versions, because so much about QGIS is positive, and a great piece of software. I'd hate to see it not implemented fully at work because of this one issue, but we don't have time to sit around waiting for it to finally list the one layer we're interested in adding.

adepoch commented 3 years ago

Hi All,

I work in a government department that would like to use QGIS more, but we have the problem that it becomes virtually unusable for a long time because of this issue when connecting to our Oracle database. I would like to propose an alternative solution.

First the problem as I understand it: So what it is doing is trying to discover layer information so that it can display the layer type before you select the layer from the list. But most people don't need to know the layer in the list. They already know. Anyway the app doesn't really need to know a layer type until someone selects it from the list for displaying. MapInfo did this well in that it just scanned the ALL_SDO_GEOM_METADATA view to find the spatial objects available. Then it would interpret the geometry once the layer was selected. Listing was very fast. ArcGIS is somewhere between the two pieces of software. I've encountered the same problem when pathing my way through directories with thousands of files. It halts to check every file in the directory before you can continue. Does it really need to know before a layer is selected?

My proposition, which you can shoot down if it's truly unworkable, is that you don't scan the layer until a user clicks on it. Before that point it is just unknown. That way the only time taken is in finding the names of the objects themselves.

QGIS would be the perfect fit for our organisation's needs if it wasn't for this list discovery problem (we deal with thousands of layers, whether in a database, or in a directory). As it stands we can't recommend our users use QGIS because it will become unworkable with our load of data. Otherwise it would be the perfect data browser, if naught else.

Thanks anyway, even if it's a bad idea.

gioman commented 3 years ago

I would like to propose an alternative solution.

@adepoch feel free to submit a pull request with the changes that you suggest. Or maybe just consider supporting the work to improve the situation, look at "commercial support" in QGIS's web site.

adepoch commented 3 years ago

I would like to propose an alternative solution.

@adepoch feel free to submit a pull request with the changes that you suggest. Or maybe just consider supporting the work to improve the situation, look at "commercial support" in QGIS's web site.

Although I personally would support our organisation commercially supporting the project, that's a decision above my pay grade. I'm just the lowly developer and spatial data manager that gets to play with the toys they allow me to use :-)

As to your idea of a "pull request" I'm afraid I'm not familiar enough with GitHub's mechanics to do that. You may have to tell me a bit about what is involved.

gioman commented 3 years ago

I'm just the lowly developer and spatial data manager that gets to play with the toys they allow me to use :-)

@adepoch so who give you the this toy didn't told you that it didn't cost them a dime and that because of that they are likely saving thousands of $? May be you can't take the decision but if this change makes your (company) work/life better isn't it worth a little investment?

As to your idea of a "pull request" I'm afraid I'm not familiar enough with GitHub's mechanics to do that. You may have to tell me a bit about what is involved.

would you be able to create the necessary patch in the fist place?

jef-n commented 3 years ago

As to your idea of a "pull request" I'm afraid I'm not familiar enough with GitHub's mechanics to do that. You may have to tell me a bit about what is involved.

https://qgis.org/en/site/getinvolved/development/bugreporting.html#submitting-a-patch-to-qgis-projects

adepoch commented 3 years ago

@adepoch so who give you the this toy didn't told you that it didn't cost them a dime and that because of that they are likely saving thousands of $? May be you can't take the decision but if this change makes your (company) work/life better isn't it worth a little investment?

would you be able to create the necessary patch in the fist place?

@gioman , You're being quite unnecessarily confrontational and there's no need to rant at me over something out of my control. That decision would be made several tiers above me, and you know it? I won't be able to find time to look into code patches myself sorry, I'm just trying to give some constructive feedback and suggestions on an issue that many people seem to have an issue with. I'm trying to help make the product better and more usable, because it has so much potential and I believe in its potential. I think if certain issues were addressed more people, and organisations, would find it the perfect product to use. Maybe if the product worked better in certain situations more organisations would help fund it?

@jef-n Thanks for the link. Much appreciated.

nyalldawson commented 3 years ago

Hey @adepoch - have a read over http://nyalldawson.net/2016/08/how-to-effectively-get-things-changed-in-qgis/. It's just my personal opinion, but in that post I've tried to clear up all the different ways things will get implemented in QGIS (based on my 15+ years as an open source developer, and 10+ years as a core qgis contributor). Open source is a strange beast, but when approached with the right mind-set of participatory contribution then the system works fantastically.

adepoch commented 3 years ago

@nyalldawson . Thanks for that. I really appreciate that you've got this written up. I've had a read. Unfortunately I've got so many other projects on the go it'll take me a few years before I have the time to devote to studying the existing code base, then making some changes :-( But I'll check it out of the repository and have a look.