Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

export resultset very slow #1059

Closed
tcoudert opened this issue Dec 13, 2016 · 17 comments
Closed

export resultset very slow #1059

tcoudert opened this issue Dec 13, 2016 · 17 comments

Comments

@tcoudert
Copy link

with 3.7.2 , an export (Resulset) takes about 800 ms for about 4000 lines.
Since 3.7.8, the same export (Resulset) takes about 5 minutes for the same number of lines
We are on RHEL 6 and I've tried different versions of Java (1.7 and 1.8)

@tcoudert tcoudert changed the title export resulset very slow export resultset very slow Dec 13, 2016
@serge-rider
Copy link
Member

Are you sure the problem is in DBeaver?
On my local machine I can export 1 million of rows from PostgreSQL in about 8 seconds (short records).
You can check actual queries performance in Query Manager view.

@tcoudert
Copy link
Author

tcoudert commented Dec 13, 2016 via email

@serge-rider
Copy link
Member

Ok. Maybe it is about configuration.

  • Do you use the same PostgreSQL driver version in 3.7.2 and 3.7.8+?
  • Are there any non-standard column types?
  • Check CPU load during CSV export. How much CPU time DBeaver consumes?
  • Check what's going on in Session Manager (there should be just one SELECT query running)

@tcoudert
Copy link
Author

tcoudert commented Dec 14, 2016 via email

@serge-rider
Copy link
Member

Dunno why I thought it is PostgreSQL. Anyhow I can't reproduce that with DB2 too (or with any other database). 10k rows are exported in 0.5sec.

I can't figure out what may affect bad performance. CSV export is just a single SQL select + consequent fetch of all rows. How long whole data fetch takes in your case (click on Fetch all rows in data editor toolbar)?

@theGreatPretender
Copy link

I see the same issue. I have a db2 query that takes 7.5 sec for 1237 rows. When I try to 'export resultset', it's taking on the order of 8 mins and consuming 12-13% CPU on my W520. This is unusual. Before Friday, exporting the resultset would take just a tad longer than the original pull since it's writing to disk. (I'm running 3.7.7 after having run 3.8.1 to see if the latest version would resolve the issue. - Still seeing the issue.)

@serge-rider
Copy link
Member

serge-rider commented Dec 14, 2016

I'd wish to fix that but I need more details (as I can't reproduce this on 3 different machines + different databases).

@tcoudert
Copy link
Author

tcoudert commented Dec 15, 2016 via email

@serge-rider
Copy link
Member

Well, this is something. Although I still can't reproduce (even a query with a thousand elements in IN exports fast).

Please do this: run export and once UI will freeze make a thread dump (https://github.com/serge-rider/dbeaver/wiki/Making-a-thread-dump). Then post it here.

Or just post an example of a query which freezes UI. Maybe it has something very special inside.

@tcoudert
Copy link
Author

tcoudert commented Dec 16, 2016 via email

@serge-rider
Copy link
Member

Thanks. Now I have a clue...

@serge-rider
Copy link
Member

Will be fixed in 3.8.2.

@theGreatPretender
Copy link

Perhaps this will help. My initial query which resulted in 1027 rows for full initial pull took 11.5 sec. The export resultset for that query took 583 mins. When I look at the csv file produced, there is a 14 sec difference between the created and modified date (Win7).

In that time period, I see in dbeaver-debug.log a recurring 37-line entry from 13 sec before the created date to the modified date. Here I show the first of 38 such 37-line entries --the last of which coincides with the modified date of the csv file:

2016-12-16 19:01:22.707 - org.eclipse.jface.text.BadLocationException
org.eclipse.jface.text.BadLocationException
at org.eclipse.jface.text.TreeLineTracker.fail(TreeLineTracker.java:1055)
at org.eclipse.jface.text.TreeLineTracker.lineByOffset(TreeLineTracker.java:267)
at org.eclipse.jface.text.TreeLineTracker.getLineNumberOfOffset(TreeLineTracker.java:1137)
at org.eclipse.jface.text.AbstractLineTracker.getLineNumberOfOffset(AbstractLineTracker.java:161)
at org.eclipse.jface.text.AbstractDocument.getLineOfOffset(AbstractDocument.java:915)
at org.eclipse.core.internal.filebuffers.SynchronizableDocument.getLineOfOffset(SynchronizableDocument.java:397)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor.getCurrentLines(SQLEditor.java:174)
at org.jkiss.dbeaver.ui.editors.text.ScriptPositionColumn$1.runInUIThread(ScriptPositionColumn.java:98)
at org.eclipse.ui.progress.UIJob$1.run(UIJob.java:97)
at org.eclipse.swt.widgets.RunnableLock.run(RunnableLock.java:35)
at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Synchronizer.java:135)
at org.eclipse.swt.widgets.Display.runAsyncMessages(Display.java:4155)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3772)
at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$4.run(PartRenderingEngine.java:1127)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:337)
at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1018)
at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:156)
at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:694)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:337)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:606)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:150)
at org.jkiss.dbeaver.core.application.DBeaverApplication.start(DBeaverApplication.java:203)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:134)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:104)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:380)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:235)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:669)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:608)
at org.eclipse.equinox.launcher.Main.run(Main.java:1515)
at org.eclipse.equinox.launcher.Main.main(Main.java:1488)

@theGreatPretender
Copy link

Sorry - didn't see that 3.8.2 will address this.

@serge-rider
Copy link
Member

This error above is just a warning but it may produce huge log file.
Will fix this as well.

serge-rider added a commit that referenced this issue Dec 18, 2016
@stongaHotmailCom
Copy link

Hello.
I'm using DBeaver v4.0.8 and I succeed to reduce athe execution time of a query on a DB2 server from 350 secondes to 8 secondes with the following change : Window->Preferences->Database->Result Sets->ResultSet fetch Size: 600 (200 by default).
Notice : a good way to know if there is a problem is to try the same request with another SQL client (such as DbVisualizer) using the same drivers (in my case : db2jcc.jar and db2jcc_license_cisuz.jar).

@llnk
Copy link

llnk commented Aug 1, 2018

DBeaver version 5.0.4

I am having issue to export large resultset in CSV. The query works fine because is a simple SELECT but it's very big (>10 billion of record) It seems that dbeaver completed the operation (on the status bar the progress is 100%) but he is not writing the file to the disk.

The CPU usage of DBEaver is 0%.

I wonder why if the resultfetch size can improve the performace so much as said by stongaHotmailCom the default is just 200.

Any clue here ?

http://prntscr.com/kdaovt

Edit: I didn't open a new Issue because the topic is the same, and also for the trick suggested by stonga. If you want i can open a new issue.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

5 participants