Database View slow with large database

I am currently evaluating DataGrip and am finding that it works really well for relatively small databases containing a small amount of objects, however the main database I work with is very large containing thousands of objects and I'm having real performance issues.

After creating a new datasource connection (I've been testing with Oracle 11g), the object synchronisation takes upwards of 7 mins (getting on for 4,000 tables). This in itself isn't much of a problem because it only needs to be run every now and then, however I am experiencing a much larger issue (to me at least):

When opening the table listing (~3800 objects at present), the IDE freezes for approx 40 seconds while it is loading the table and index listing. This would be okay if it only happened the first time the listing was opened, however it appears to happen every time there is an interaction with the Database View. With the entire IDE locked up while this happenes (there is a large CPU spike during this process) I just have to wait until it's done. As a comparison, using Oracle SQL Developer, similar interactions are almost instentaneous.

Now, I understand that DataGrip is doing much more than SQL Developer... but is there any way to speed this interaction up?

For reference, I'm running DataGrip v1.0.1 on the Oracle v1.8 Java VM.

Thanks in advance.

6 comments
Comment actions Permalink

Darren a couple of questions.

What do you mean by Datagrip doing much more than SQL developer? I believe the most recent version of SQL developer has many more features than Datagrip but is specifically targeted towards Oracle databases.

I also work with an Oracle database which houses many tables and have found that if I load one schema at a time the load process is not too intense. However I have also noticed that loading DICTIONARY views and SYS tables can be tedious. Out of curiosity, what are you using for connection settings? TNS, Service Name or URL?

0
Comment actions Permalink

Hi Nathan,

Thanks for your reply.

Apoligies, re-reading my original post I should have added 'while loading the tree view' ... I understand your point that SQL Developer is more feature complete and optimised for Oracle databases, but I was thinking about what DG is trying to do when loading the tree view by allowing the table and column listing to be instantly searchable - Including incides, keys etc (after retesting SQL Dev this morning, this behaviour is also supported but is not quite as obvious).

I just wanted to be clear that I understood that the direct comparison was unfair. Maybe I should have just left it as I understand that DG is not specifically optimised for Oracle connections.

To answer your question about the datasource, I am using a Service Name as that was the default connection type. I'm also using the driver that DG provided via it's own download method.

Interestingly, I've also taken a look this morning on a Microsoft SQL copy of our database (using the jTds driver rather than the MS one). The underlying structure of the DB is the same as the Oracle version and there is still a delay while loading the listings but it is nowhere near as pronounced as the delay in Oracle.

0
Comment actions Permalink

One of the things that I have done to help speed the loading of the tree is to make sure I connect only to the relevant schema. I am assuming that with a database of the size you described you have it broken into multiple schemas. If you connect to only one of them at a time you will not need to load as many objects. Selecting only the relevant object categories from the "SCHEMAS" options tab will also require fewer objects to be read by Datagrip.

Not sure any of that advice even applies to you but wanted to share since I work in a datamart environment and probably average ~10 different tables a day along with a few views. Knowing that, I only connect to those objects needed and have seen significant IDE performance improvements.

0
Comment actions Permalink

Hello Darren,

When opening the table listing (~3800 objects at present), the IDE freezes for approx 40 seconds while it is loading the table and index listing.

As I understood you mean that IDE freezes when you expand Database tool window tree for the schema which many DB objects in it, correct? Could you please attach a screenshot of Database tool window's expanded Viewing mode settings (do you have Group Schema/Contens, showing Keys etc enabled there)?

Also for the investigation of this performance problem it would be very helpfull to get a CPU snapshots at the moment of high CPU utilization and Automatic thread dumps with idea.log from the log directory. You could attach them here or submit a request in support channel .

0
Comment actions Permalink

Hi Andrey,

Yes, you have described exactly the issue I was trying to explain. I do have 'Group Schema' and 'Show Keys & etc' enabled as they are enabled as standard, here is the screenshot you requested:
2016-01-11 (1).png

I have attached the IDE log and thread dumps as requested. During the time the IDE was running, the only thing I did was to open the tables tree, close it after the IDE had settled down and then re-opened the tree again.

I am happy to provide a CPU usage profile but I am having issues following the information you linked to. The document says to select an option in the tools menu - The trouble is, there is no tools menu in DataGrip. I've looked around but cannot see an option anywhere. Do I need to download the 'YourKit Java Profiler' as suggested for IntelliJ and PyCharm Community editions or is the menu entry available somewhere else?

Thanks in advance.



Attachment(s):
log_and_thread_Dumps.zip
0
Comment actions Permalink

Sorry for a delayed reply, there's been a busy week. And thanks for provided info! Filled the issue: https://youtrack.jetbrains.com/issue/DBE-2151 . You can follow it for the updates form the developers.

To activate CPU profiling in DataGrip please invoke Ctrl+Shift+A and type Start CPU profiling as action name. This would be helpful for the investigation. Thank you!

0

Please sign in to leave a comment.