Forum Stats

  • 3,767,981 Users
  • 2,252,736 Discussions
  • 7,874,399 Comments

Discussions

Could User Reports be made to feel faster?

user9540031
user9540031 Member Posts: 129 Silver Badge
edited Nov 15, 2021 11:03PM in SQL Developer

Hello,

When using parent/child reports, refresh of the child after selecting a record in the parent is noticeably slow. How slow exactly? More than a full second—I don’t have the exact measure, but I’d say no fewer than 1.2 to 1.5 s.

Hints:

  • It’s not the database.
  • It’s not even the Java garbage collector.

TL/DR: is there a way to tune this, and make it quicker?

[Remark: long post with pictures. Unfortunately, due to Forums limitations I have to split it into this introduction and the subsequent comments. ☹️]

Answers

  • user9540031
    user9540031 Member Posts: 129 Silver Badge

    I must stress that this is not a big issue: I’ve been using reports for years—they’re a key to my productivity—and sure, at times they did feel slow... But not to such an extent that I would look into it from a performance angle.

    Recently, however, while making a live video demonstration of my reports, the thing really struck me, and I started to wonder: was it my report which had become too heavy? Or was I hitting a performance issue?

    Then I noticed suspiciously-high CPU activity, which matched exactly the times when I selected a new record in the parent report. Not tremendously high, but high enough to be noticed, and surely the sign that a lot was going on under the scene—time to monitor that JVM closely!

    But first, let’s build a test report. The screenshot in the question post above shows what I have used: a very basic report in which the parent displays 10 rows, and each child report just echoes the currently-selected row from the parent, concatenated with its own identifying number. That screenshot is from a test report with 100 (generated) children. It turns out, however, that the count of child reports is not a factor: refresh in a test with only 3 child reports is not noticeably faster than when 100 children are present.

    For completeness, the XML code of the test report (with 3 child reports) is included as a comment after this post.

    And now for the “performance investigation”. Not being a Java expert, I just ran visualvm, and attempted to gather whatever I could about SQL Developer’s JVM activity while using the test report—specifically, the test usage consists in switching from one record to the next in the parent report—and try to understand the perceived slowness, plus the above-mentioned spikes in CPU activity.

    One thing became immediately clear: GC activity happens: actually, a full GC was performed whenever a new record was selected in the parent report!

    Profiling the JVM confirmed that the AWT-EventQueue-0 thread always calls System.gc at some point in the process, causing the GC to reclaim heap memory aggressively—a lot more than usual, I’d say.

    Still, visualvm would report “stop-the-World” full GC pauses of only 100 ms to 300 ms—definitely not enough to account for the whole perceived slowness. Nevertheless, I went to product.conf, and added the following line:

    AddVMOption -XX:+DisableExplicitGC

    and restarted SQL Developer, in order to see if that would improve things much. Consistent with the account of GC pauses in visualvm, it did not, or not much: the GC activity and the corresponding spikes in CPU load were gone, yet the GUI slowness was still there: the grid in child reports refreshed only marginally faster, if at all.

    Important: I can’t tell if the above JVM option is a good idea: while calling System.gc explicitly is not considered a good thing in most Java application projects, here we’re talking of code at the heart of the Java AWT layer—I'd rather leave this to experts.

    Yet that slowness was not caused by the database either: according to the JDBC profiler in visualvm, JDBC calls would typically last from 20 ms to 50 ms at most, which again was far too short to play a significant role.

    More Java profiling would not reveal anything significant. In the end, it seemed as if nothing in SQL Developer, either in the Java side or in the database, could cause these waits...

    Then finally... Eureka! If you leave the calls to System.gc aside, in fact there isn't a performance issue at all: it’s just a timed wait, purposely built into SQL Developer’s GUI, of course!

    Think about it for a moment: imagine that the GUI would react instantly to the user selecting a new record in the parent grid (which can be achieved in several ways, e.g. using the mouse, or using the up/down arrow keys from the keyboard). Then a call to the database in order to refresh the child grid would be triggered immediately... And SQL Developer has no idea about how long that call will last. Meanwhile, the user might just wish to select yet another record in the parent grid—say, hit that up/down arrow key a few more times to reach the record of interest—and it would hurt if she had to wait at that stage because the GUI had reacted too promptly and got into a slow database call in order to refresh the child report too early. Furthermore, these unnecessary database calls could just contribute to load on the database. For these reasons, SQL Developer’s architects made a sensible design choice: wait a short time after a record has been selected in the parent report, just long enough that there’s a fair chance that the user has now finished selecting the record of interest, rather than one that’s been hit transiently. Therefore, a timed delay is necessary for the user’s selection to stabilize.

    This can be evidenced by tracing the DB session: select one record in the parent, and wait for the child grid to refresh; then, using the up/down arrow keys, move up and down repeatedly in the parent grid, quickly enough so that the refresh in the child grid does not happen, and keep doing so for some time, e.g. a dozen of seconds. In the SQL trace you’ll see that no database call at all is made until the selected record in the parent grid has been set for long enough.

    Yet that delay might seem a bit too long for some users, hence the question: could that timed wait happen to be tunable, if anything in SQL Developer’s configuration options or in JVM properties?

    (This is somewhat similar to the keyboard key-repeat speed or key-repeat delay: the right settings are a matter of personal choice, though most users will be happy with system defaults in general.)

    A possibly related question—and feeling of slowness—is about how long it takes to switch from one child tab to the next by clicking on the corresponding tab label (I’m not aware of a keyboard shortcut for that). When a new child tab is selected that way, its content is refreshed immediately, without any noticeable wait in the GUI... But then if you click on another child tab quickly enough, the GUI does not respond and that mouse click is simply ignored, making it feel like the GUI is unresponsive or lagging. Whereas actually, I suspect that again a design decision was made to impose a short pause after the newly-selected tab has been refreshed, in order to prevent from submitting repeated queries to the database too quickly. And if so, again, could that safety delay happen to be tunable?

    No big deal if none of the above is possible... It seems Reports have been working this way for a very, very long time. But now that I’ve noticed, anyway... well, just asking.

    Regards,

  • Mike Kutz
    Mike Kutz Member Posts: 5,789 Silver Crown

    Your first post sounds like it is a bug.

    Make sure you file an SR.

  • user9540031
    user9540031 Member Posts: 129 Silver Badge

    XML code for the test report (with 3 child reports):

    I'm well aware that most people don't like attachments from untrusted sources—I don't either, so I'd have just copy-pasted the XML text of that report (only 83 lines of XML code) into a code section, but the Forum software is blocking that for whatever reason.

    ("Access Denied. You don't have permission to access "http://community.oracle.com/tech/developers/post/comment/?" on this server. Reference #18.5cab645f.1637086558.51c61c1b" says the popup.)

    Anyway, as said before, this report is simple and very easily recreated, for those interested who would rule out the download.

    Regards,

  • user9540031
    user9540031 Member Posts: 129 Silver Badge

    Hello,

    @Mike Kutz

    Your first post sounds like it is a bug.

    Maybe, but what about the second post? As told, I think this was made purposely, and I would only make the timing a bit shorter if I could. And it's hard to call a change in behaviour a bug when it has been around for so long.

    Believe it or not, I have just checked:

    • SQL Developer 2.1 (release 2.1.1.64.45, 32-bit with JDK) behaved differently: in that version the child report was refreshed immediately, without a noticeable wait, upon selecting the parent record.
    • But In SQL Developer 3.2 (release 3.2.20.09.87, 32-bit with JDK), the noticeable delay before refreshing the child report was already there.

    (Yes, I have kept these old downloads, since August 2010 and November 2012, respectively.)

    I can also assure you that in either case, the user experience seems far inferior to what we enjoy now. It's hard to compare though: I had never run these old versions before on my present-day computer and OS; and back in 2012 I didn't use reports much... Nevertheless, right now when using the test report, in either version the general impression is that the GUI is really slow and lagging—at least that's how it looks now on a fairly modern computer and OS (Win 7, 64-bit). This hints that the user experience has received many improvements over the years.

    Regards,

  • user9540031
    user9540031 Member Posts: 129 Silver Badge

    Complementary note: regarding the action of clicking on the label of a child tab in order to switch from one child tab to another, and the missed click if doing this twice in a too-quick succession, that behaviour was already there in SQL Developer 2.1.

    Regards,