Bug report #19713

Connection to Oracle is very slow QGIS 3.x

Added by Carlos Lopez Quintanilla over 1 year ago. Updated over 1 year ago.

Status:Closed
Priority:High
Assignee:-
Category:Data Provider/Oracle
Affected QGIS version:3.2.2 Regression?:Yes
Operating System:Windows 7 Easy fix?:No
Pull Request or Patch supplied:No Resolution:wontfix
Crashes QGIS or corrupts data:No Copied to github as #:27538

Description

We have QGIS 2.18 with several maps with connection to Oracle, in Barcelona's town hall.
The connection is fine, and the time to load a layer typical is a few seconds.

But, We're testing to implementing QGIS 3.2, but the connection to Oracle is so slow.
The time to load the same layer is 45 minutes, it's not acceptable for users.

we have a standalone application developed in python, using api from QGIS 3.2, in this case the connection is very fine.

So, we think that the problem is the GUI of QGIS 3.x, it spends a lot of time to load one layer.

We have a computer to test here in town hall, if assigned person need to test or have questions no doubts to contact with us.
We will facilitate remote access, if necessary

Carlos López Quintanilla:
Xavier Llinares:

DebugView.LOG Magnifier - log DebugView with "estimatedMetadata" = False (7.38 KB) Carlos Lopez Quintanilla, 2018-08-28 11:33 AM

URBANISME_QCOLOR (Oracle PROD).qgs (742 KB) Carlos Lopez Quintanilla, 2018-08-28 11:33 AM

parameters_conection_Oracle_eng.png - Parameters for large tables (28.1 KB) Carlos Lopez Quintanilla, 2018-09-03 01:22 PM

History

#1 Updated by Regis Haubourg over 1 year ago

Hi,
do you have any oracle log tracking what queries are issued by QGIS and how long they take? This could help to see directly if the queries are different with those issued by 2.18.

#2 Updated by Carlos Lopez Quintanilla over 1 year ago

We don't have any log, because not crash, it spends a lot of time, but not crash!

Once layer are in canvas, the behavior is correct.

#3 Updated by Jürgen Fischer over 1 year ago

Are you using the "use estimated metadata" setting?

#4 Updated by Luigi Pirelli over 1 year ago

Carlos Lopez Quintanilla wrote:

We don't have any log, because not crash, it spends a lot of time, but not crash!

Once layer are in canvas, the behavior is correct.

if you need a log try to use master and view log with DbgView windows application...

#5 Updated by Carlos Lopez Quintanilla over 1 year ago

Jürgen Fischer wrote:

Are you using the "use estimated metadata" setting?

"estimatedMetadata" = False

#6 Updated by Jürgen Fischer over 1 year ago

Carlos Lopez Quintanilla wrote:

Jürgen Fischer wrote:

Are you using the "use estimated metadata" setting?

"estimatedMetadata" = False

Then enable it and check again. You should see better performance - esp. if you're using large datasets.

#7 Updated by Carlos Lopez Quintanilla over 1 year ago

Jürgen Fischer wrote:

Carlos Lopez Quintanilla wrote:

Jürgen Fischer wrote:

Are you using the "use estimated metadata" setting?

"estimatedMetadata" = False

Then enable it and check again. You should see better performance - esp. if you're using large datasets.

Done, but the performance is the same that before

#8 Updated by Carlos Lopez Quintanilla over 1 year ago

Jürgen Fischer wrote:

Carlos Lopez Quintanilla wrote:

Jürgen Fischer wrote:

Are you using the "use estimated metadata" setting?

"estimatedMetadata" = False

Then enable it and check again. You should see better performance - esp. if you're using large datasets.

Done, but the performance is the same that before

#9 Updated by Carlos Lopez Quintanilla over 1 year ago

Carlos Lopez Quintanilla wrote:

Jürgen Fischer wrote:

Carlos Lopez Quintanilla wrote:

Jürgen Fischer wrote:

Are you using the "use estimated metadata" setting?

"estimatedMetadata" = False

Then enable it and check again. You should see better performance - esp. if you're using large datasets.

Done, but the performance is the same that before

We install DbgView.
You can see the log and the project QGIS

#10 Updated by Jürgen Fischer over 1 year ago

Carlos Lopez Quintanilla wrote:

Done, but the performance is the same that before

That applies to newly loaded layers. The layer in your attached project don't use it yet.

#11 Updated by Giovanni Manghi over 1 year ago

  • Category changed from GUI to Data Provider/Oracle
  • Status changed from Open to Feedback
  • Regression? changed from No to Yes

Carlos Lopez Quintanilla wrote:

We don't have any log, because not crash, it spends a lot of time, but not crash!

Once layer are in canvas, the behavior is correct.

I think Regis meant to have a look to logs/queries on the server side.

#12 Updated by Carlos Lopez Quintanilla over 1 year ago

Carlos Lopez Quintanilla wrote:

We have QGIS 2.18 with several maps with connection to Oracle, in Barcelona's town hall.
The connection is fine, and the time to load a layer typical is a few seconds.

But, We're testing to implementing QGIS 3.2, but the connection to Oracle is so slow.
The time to load the same layer is 45 minutes, it's not acceptable for users.

we have a standalone application developed in python, using api from QGIS 3.2, in this case the connection is very fine.

So, we think that the problem is the GUI of QGIS 3.x, it spends a lot of time to load one layer.

We have a computer to test here in town hall, if assigned person need to test or have questions no doubts to contact with us.
We will facilitate remote access, if necessary

Carlos López Quintanilla:
Xavier Llinares:

We have solved the problem by changing the connection parameters.

We believe that it was necessary to leave or warn that for large tables better to use these parameters.

Attached image with the selected parameters.

#13 Updated by Giovanni Manghi over 1 year ago

We believe that it was necessary to leave or warn that for large tables better to use these parameters.

closing and adding a feature request?

#14 Updated by Carlos Lopez Quintanilla over 1 year ago

Giovanni Manghi wrote:

We believe that it was necessary to leave or warn that for large tables better to use these parameters.

closing and adding a feature request?

Yes!!!

#15 Updated by Giovanni Manghi over 1 year ago

  • Resolution set to wontfix
  • Status changed from Feedback to Closed

Also available in: Atom PDF