From user:

perhaps just don't display the modal dialog while the query is executing.  That is typically what takes the majority of the time.  We could then interact with the visualization of the old data while fetching the new stuff.  Then after the query completes lock things down by disabling the screen temporarily or something.  

Auto refresh cannot be executed in parallel with many user-triggered actions such as the ones listed below. Hence, VA currently does not allow user to interact with VA while auto refresh is being executed.
   * "File > Save / SaveAs / Revert to Saved / Export Packaged Workbook..."
   * "Data Source > Refresh / Extract Data / Use Extract / Extract Refresh / Extract Remove"
   * "Data Source > Edit Query > Preview / Save / Regenerate Extract"
   * "Data > Paste Data / Refresh All Data and Extracts / Refresh All Extracts"
   * VA is performing model calculation
   * ... (see BI #5400 for complete list).

If the modal dialog displayed by auto refresh is removed so that user can interact with VA when auto refresh is being executed, then disabling user-triggered actions listed above as well as those user-actions that would trigger a model calculation (e.g. moving fields between decks in a worksheet using DnD) is not a trivial task.

@funfun: when an auto-refresh first starts, what if you trigger a background thread to do the database query. Once ADS receives the query results, you then initiate the modal dialog to do all the auto-refresh related processing. The biggest delay is the DB query processing time. Its almost as if the auto refresh process isn't really starting until the DB results are received. What are your thoughts on such an approach?

perhaps just don't display the modal dialog while the query is executing.  That is typically what takes the majority of the time.  We could then interact with the visualization of the old data while fetching the new stuff.  Then after the query completes lock things down by disabling the screen temporarily or something.

This suggests a different approach which is similar to:
(1) Always execute auto refresh when scheduled auto refresh is due. In the current VA implementation, auto refresh will not be executed if any of user-triggered actions listed here that conflict with auto refresh is running; when this happens, VA will re-try auto refresh a few times.
(2) After auto refresh finished query execution in non-EDT thread, disables VA to not allow user interaction.
(3) Auto refresh waits for all user-triggered actions completed, if there is any, and then replaces old data by new data.

This certainly requires many changes: changing current flow, handling different error scenarios, tracking data source specific data, etc.

when an auto-refresh first starts, what if you trigger a background thread to do the database query. Once ADS receives the query results, you then initiate the modal dialog to do all the auto-refresh related processing. The biggest delay is the DB query processing time. Its almost as if the auto refresh process isn't really starting until the DB results are received. What are your thoughts on such an approach?

As mentioned here, many things need to be changed: how auto refresh task to be scheduled, how error is handled (current error handling probably will interact with the modal dialog and show/hide the model dialog when necessary), how data source specific data to be tracked and swapped, etc. It is not that simple as you described.

Another use case is as follows:

In VA, I go edit query dialog & modify the query & then click save.  VA presents a modal dialog during query execution which prevents the user from interacting w/ VA. Ideally, VA allow user interaction up to the time that query execution is finished & only then present a modal dialog to update the VA metadata and model.

This is somewhat related to #14830. #14830 mentions that we should get the cancel button to actually cancel the query. So we keep the modal dialog but allow the cancel button to cancel the query execution. Allowing the user to proceed w/ using VA when the user has specifically initiated an edit query action could lead to user confusion about which data set is being used.

This impacts all VA operations that involves executing a query.

Background: Potentially long operations in VA are executed in a background thread to avoid locking up the application. However in order to avoid the various data models getting out of sync, a modal progress dialog box is shown to prevent the user from making changes to the chart configurations. The only options that the user has while this is happening is either wait for the operation to complete, or cancel the operation. Even though the application is not “locked up”, the user is still prevented from most interactions with the application. There are 2 types of long operations: 1) Pivot calculations after the user makes a chart configuration change, and 2) query execution. We will handle #2 for case #13172. For example when the user edits the query for the dataset, VA will re-execute this query, and then re-calculates all data models that uses this query result set. While this is happening, a modal dialog box prevents the user from doing other VA operations. In many scenarios the query execution can be very time consuming.

For case #13172, we want to perform the query execution in a background thread without the modal dialog box, thus allowing user interaction with VA while this is happening. After the query execution is successfully completed, then present a modal dialog box while VA performs the rest of the business logic, which is to apply the new dataset to all worksheets that uses this dataset.

1. Data Pane -- show hour glass icon to the right of datasources that are currently updating
2. Data menu
   • Refresh All Data and Extracts...
   • Refresh All Extracts...
   • Auto Refresh...
   • In the above dialog boxes, we need to disable all datasources that are currently updating, and show "(updating...)" next to the datasource.
3. Individual data source menu
   • Refresh
   • Use Extract -- uncheck action
   • Edit Query -- Save action
   • Edit Connection
   • Replace Connection
   • Extract > Refresh
   • Extract > Remove
   • If the data source is currently updating, then the data source menu will show only a single "Cancel Current Query" option.

The above user actions will need to be broken up into the query execution and the actual business logic for each action. The query execution will need to happen in the background, without actually modifying the current in memory data rows for the datasource. When the query is done, then present a model dialog box to block other user interactions while we merge in the new data rows and perform the business logic.

Hi Nhi,

We have some questions regarding auto refresh :

Current implemention for 

• Mark all datasources as "updating".
• Then serially run query for each datasource in the background without any progress dialog.
• As each datasource query is done, show progress dialog for that datasource then merge. When merge is done, remove the "updating" status for this datasource.
• Thus we might see progress dialog multiple times, which is acceptable.

HI Nhi,

Do we need to provide any cancel query menu while serially running datasources in backgroung?

Thanks,

Shyam

[Nhi] Hi Shyam,

Yes. Please see #3 in this comment, last bullet point.

Hi Nhi,

Added implementation as per your comment and added screenshot

Refresh all,  Auto refresh, Refresh data source and Cancel current query

Please have a look 

Thanks

Shyam

Before attempting the following scenarios, you'll need to download the DB connection file and place it it your [User Home]/.datastudio/connections directory. Then change the password in the Server Properties.

Scenario 1:

1. Open auto-refresh.vizw in VA
2. Perform Data > Refresh All Data and Extracts... menu option, and click the Refresh button
3. The query will take 15 seconds each to execute
4. Quickly right click on Data menu
5. Result: The "Refresh All Data and Extracts..." is enabled, which is incorrect
6. Expect: While "Refresh All Data and Extracts..." or "Refresh All Extracts..." is executing in the background, both of these options should be disabled to avoid complications

Scenario 2:

1. Open auto-refresh.vizw in VA
2. Perform Data > Refresh All Data and Extracts... menu option, and click the Refresh button
3. The query will take 15 seconds each to execute
4. Quickly right click on one of the datasource and select Cancel Current Query
5. Result: The query is cancelled for both datasources, which is incorrect
6. Expect: I should be able to individually cancel each query

Scenario 3:

1. Open auto-refresh.vizw in VA
2. Perform Data > Refresh All Data and Extracts... menu option, and click the Refresh button
3. The query will take 15 seconds each to execute
4. Result: Both datasources show the spinning icon for more than 30 seconds
5. Expect: As each datasource finishes query execution, its spinning should be removed so that the user can see the progress

Scenario 1:

1. Open auto-refresh.vizw in VA
2. Right-click on the 1st datasource and select Refresh
3. Quickly perform Data > Refresh All Data and Extracts... menu option. Wait for the 1st refresh to elapse 10 seconds and then click the Refresh button in this dialog.
4. Result: The 1st datasource is refreshed twice, which is incorrect.
5. Expect: The "Refresh All Data and Extracts..." or "Refresh All Extracts..." should exclude any datasource that is in progress of being refreshed.

Scenario 2:

1. Open auto-refresh.vizw in VA
2. Perform Data > Refresh All Data and Extracts... menu option, and click the Refresh button
3. Quickly right click on the 2nd datasource and select Cancel Current Query
4. Result: The "updating..." icon and "cancelling..." status remain with the 2nd datasource until the 1st datasource is finished updating, which is incorrect.
5. Expect: While cancelling an in-progress datasource refresh can take a while, the cancelling of a datasource that has not started should be immediate.

Scenario 1:

1. Open auto-refresh.vizw in VA
2. Perform Data > Refresh All Data and Extracts... menu option, and click the Refresh button.
3. Result: After both datasources finished refreshing, the following message is displayed in the log file: BIRequestManager.resetRunningRequest Attempt to reset running request in request manager while tracked request is null.
4. Expect: No such message should be logged

Scenario 2:

1. Open auto-refresh.vizw in VA
2. Refresh one or all datasources
3. While datasource is refreshing, try to close the VA window
4. Result: The close command is ignored
5. Expect: If there are any query execution in progress, a dialog modal to this VA window should be displayed saying "Cancelling database query...", then cancel all datasource refresh and wait for all queries to finish cancelling, then close the VA window.

Scenario 3:

1. Open auto-refresh.vizw in VA
2. Select Data > Auto Refresh... menu option
   • Set Refresh Frequency to 10 seconds
   • Select both datasources
   • Click Save
3. Result: After a few minutes, a bunch of the following messages is displayed in the log file:

   10/3 16:44:12.701 BIRequestManager.resetRunningRequest Attempt to reset running request in request manager while tracked request is not the same as the one just processed.
   10/3 16:44:12.702 BIRequestManager.clearAutoRefreshTaskAndAddToHistory Attempt to clear auto refresh task in request manager while tracked task is not the same as the one just processed.
   10/3 16:44:23.132 BIRequestManager.resetRunningRequest Attempt to reset running request in request manager while tracked request is not the same as the one just processed.
   10/3 16:44:23.132 BIRequestManager.clearAutoRefreshTaskAndAddToHistory Attempt to clear auto refresh task in request manager while tracked task is not the same as the one just processed.
   10/3 16:44:40.254 BIRequestManager.resetRunningRequest Attempt to reset running request in request manager while tracked request is not the same as the one just processed.
   10/3 16:44:40.255 BIRequestManager.clearAutoRefreshTaskAndAddToHistory Attempt to clear auto refresh task in request manager while tracked task is not the same as the one just processed.
   10/3 16:44:50.777 BIRequestManager.resetRunningRequest Attempt to reset running request in request manager while tracked request is not the same as the one just processed.
   10/3 16:44:50.777 BIRequestManager.clearAutoRefreshTaskAndAddToHistory Attempt to clear auto refresh task in request manager while tracked task is not the same as the one just processed.
   

4. Expect: No such message should be logged

Scenario 1:

1. Open auto-refresh.vizw in VA
2. Right-click on any datasource in the Data pane, and select Close
3. Result: The following exception is logged:

   java.lang.NullPointerException
   	at com.aquafold.bistudio.menu.BIMenuDataSource.actionPerformed(BIMenuDataSource.java:247)
   	at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
   	at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
   	at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
   	at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
   	at javax.swing.AbstractButton.doClick(AbstractButton.java:376)
   	at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:842)
   	at com.apple.laf.AquaMenuItemUI.doClick(AquaMenuItemUI.java:157)
   	at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:886)
   	at java.awt.Component.processMouseEvent(Component.java:6533)
   	at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
   	at java.awt.Component.processEvent(Component.java:6298)
   	at java.awt.Container.processEvent(Container.java:2238)
   	at java.awt.Component.dispatchEventImpl(Component.java:4889)
   	at java.awt.Container.dispatchEventImpl(Container.java:2296)
   	at java.awt.Component.dispatchEvent(Component.java:4711)
   	at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4897)
   	at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4534)
   	at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4475)
   	at java.awt.Container.dispatchEventImpl(Container.java:2282)
   	at java.awt.Window.dispatchEventImpl(Window.java:2746)
   	at java.awt.Component.dispatchEvent(Component.java:4711)
   	at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:760)
   	at java.awt.EventQueue.access$500(EventQueue.java:97)
   	at java.awt.EventQueue$3.run(EventQueue.java:709)
   	at java.awt.EventQueue$3.run(EventQueue.java:703)
   	at java.security.AccessController.doPrivileged(Native Method)
   	at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
   	at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:90)
   	at java.awt.EventQueue$4.run(EventQueue.java:733)
   	at java.awt.EventQueue$4.run(EventQueue.java:731)
   	at java.security.AccessController.doPrivileged(Native Method)
   	at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
   	at java.awt.EventQueue.dispatchEvent(EventQueue.java:730)
   	at com.intellij.ide.IdeEventQueue.defaultDispatchEvent(IdeEventQueue.java:866)
   	at com.intellij.ide.IdeEventQueue._dispatchEvent(IdeEventQueue.java:650)
   	at com.intellij.ide.IdeEventQueue.dispatchEvent(IdeEventQueue.java:381)
   	at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:205)
   	at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
   	at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
   	at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
   	at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
   	at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
   

4. Expected: No exception should be observed

We have verified all the above scenarios and its working fine on Dev-54 build.

Reopened for the following:

1. Please take a look at the attached 13172-code-review.patch for code review feedback, and apply the patch if you don't see anything issues with it.
2. The following scenario results in much more memory leak than in V19.0.
   • Edit the ADS launcher to increase VM max memory to 2048 MB
   • Lauch ADS and remove all open tabs to minimize memory usage
   • Click the trash icon at the bottom right of the ADS window 3 times which will run the garbage collector
   • Note that there are 3 numbers (X : Y : Z) to the left of the trash icon. X is the current memory usage, which is around 70 MB.
   • Open link-params.vizx in VA
   • Right-click on each datasource and select Edit Connection...
     • Verify that you have a registered server in ADS with that name
     • If not, do Replace Connection for each datasource and point it to a registered server for MySQL on 172.24.1.145
     • Otherwise the steps below will result in error
   • Open Data > Auto Refresh... menu option
   • In the Auto Refresh dialog, check Refresh Frequency and set value to 5 seconds, and check all 3 datasources, then click Save.
   • Let this run for 25 minutes
   • Open Data > Auto Refresh... menu option
   • In the Auto Refresh dialog, uncheck Refresh Frequency, then click Save.
   • Close VA
   • Click the trash icon at the bottom right of the ADS window 3 times which will run the garbage collector
   • Result: The current memory in use is now around 920 MB after garbage collection, which indicates that there is memory leakage of about 850 MB.
     • In ADS V19.0, the above scenario results in current memory usage of 430 MB, which is a leakage of 360 MB.
   • Expected: V19.5 should not introduce additional memory leakage. The memory leakage after the above scenario should remain at the same level as in V19.0. The root cause probably due to the no-modal-refresh implementation since this is the difference between v19.0 and v19.5.

1. When ADS  started and click(3) on GC  X-Y-Z = 70-615-1820
2. Open VA X-Y-Z = 460-640-1820
3. Close click(3)on GC  X-Y-Z = 189-920-1820
4. Again open VA
5. After executing 25 mins AutoRefresh then stop it and close VA  and click(3) GC X-Y-Z = 212-907-1820

Hi Nhi
We tried mentioned scenarios on Windows 7 / 10, MacBook Pro, Mac high Sierra and also in Linux machine.
and not able to reproduce such memory leakage. Can you please try once again in your machine and provide us more detail steps, if you still found the same issue.  As per our observation, there are no such changes committed to this issue that may cause memory leakage.
 

Thanks
Pankaj & Shyam

[Nhi] Just to recap the Skype conversation. The reason that the memory leak was not reproduced is that the Refresh was not doing anything due to missing DB connection. You have to make sure to register a MySQL connection to 172.24.1.145. Then after opening link-params.vizx, right click on each datasource and Replace Connection with the 172.24.1.145 connection. Then save the workbook again so that you don't have to repeat these steps.

I am still observing the memory leak in both iMac and Windows 10 VM environments with build dev-59. Please see the attached memory-leak.png.

Additionally the following scenario is observed:

1. Open link-params.vizx in VA
2. Right-click on the 1st datasource and select Edit Connection
3. Note the name of the connection
4. Make sure you don't have a registered connection in ADS with that name. If there is, then temporarily rename the registered server to something else.
5. Right-click on the 1st datasource and select Extract > Refresh
6. Result: The refresh appears to be successful, which is incorrect.
7. Expected: When the connection referred to by the datasource does not exist, the refresh operation should fail and display an error dialog box. See the attached fail-refresh.png. For example, this is the case in earlier builds of ADS, such as build dev-6.

We have verified all the above scenarios and it is working fine on Dev-61 build.

I am still seeing the memory leakage in Dev-61 QA build in both my iMac and Windows 10 environments. Please see still-leaking.png.

@Juhi: Did you verify the memory leak scenario using the QA build?

@Shyam: Were you using your own dev build or were you using the QA build to validate the memory leak?

Hi Nhi,

I ran the above test scenario on my Windows 10 box and here are my numbers:

66.526.1820

987.1524.1820

I had to run the test twice because the first time I ran it, ADS hung after about twenty minutes. I could not cancel or exit. Had to kill ADS in the task window. I think it might have run out of memory.

Hope that helps,

Tom

Hi Nhi,

I have tried the above memory leak scenario  using the QA build Dev-62 on Windows7 and Windows10 machine.I have run auto-Refresh for 25 mins for each machine and observed the X:Y:Z result for different machine as below :

@Tom : I did not find any hang issue for ADS application

Thanks

I am now observing consistent memory usage between v19.0 and v19.5.0-dev-62.

Hi,

I have verified all the above scenarios on Dev-65 build and it is working fine so marking as verified.

Thanks

Scenario 1:

1. Open auto-refresh2.vizw in VA
   • Note that auto-refresh2.vizw is the same as auto-refresh.vizw, except that I've removed the delay in auto-refresh2.vizw so that the refresh will occur faster.
2. Perform Data > Refresh All Data and Extracts...
3. Wait until the refresh is done
4. Exit VA
5. Result: VA fails to exit with the following exception in the log file:

java.lang.NullPointerException
    at com.jidesoft.pivot.PivotDataModel.sortCompoundKeys(PivotDataModel.java:2037)
    at com.jidesoft.pivot.CalculatedPivotDataModel.createRowHeaderTableModel(CalculatedPivotDataModel.java:510)
    at com.jidesoft.pivot.PivotDataModel.getRowHeaderTableModel(PivotDataModel.java:1587)
    at com.aquafold.bistudio.model.BIChartConfigAPI.buildRowTree(BIChartConfigAPI.java:573)
    at com.aquafold.bistudio.model.BIChartConfigAPI.reconfigure(BIChartConfigAPI.java:534)
    at com.aquafold.aquacore.open.chart.bi.BIChartPanel.clearPreferredSizes(BIChartPanel.java:1967)
    at com.aquafold.bistudio.sheet.BIWorksheetPanel.clearManualSizing(BIWorksheetPanel.java:996)
    at com.aquafold.bistudio.sheet.BIWorksheetPanel.clearWorksheet(BIWorksheetPanel.java:983)
    at com.aquafold.bistudio.sheet.BISheetsPane.onWindowClose(BISheetsPane.java:1112)
    at com.aquafold.bistudio.BIStudio.closeWindow(BIStudio.java:1223)
    at com.common.ui.CBaseFrame$1.windowClosing(CBaseFrame.java:111)
    at java.awt.AWTEventMulticaster.windowClosing(AWTEventMulticaster.java:349)

Note that based on the above stack trace, this problem seems to be related to Issue #15499. However, this problem does NOT occur if you use auto-refresh3.vizx. The only difference is that auto-refresh2.vizw uses live DB connections, and auto-refresh3.vizx uses data extracts.

Hi,

All the above scenarios are working fine as per the expectations on  ADS19.5.0-dev-66 build. Hence marking it as Verified.