I have a serious performance problem the first time a dialog is opened in my app. It takes more than 15 seconds, but the second time it opens very fast.

In order to figure out what is happening, I captured the number of rows in all dsi_xxx tables just before opening the dialog and right after the dialog is shown. The problem was in one detail table holding a large blob data of uploaded documents. It looks like when formspider first access any datasource it copies its entire contents (if there is no pagination) to the corresponding dsi_xxx table. In my case the where clause of this table was like master_id = nvl(:masterID, master_id). The first time the parameter passed to the datasource was null causing the full set of records to be copied (150+ Megabytes of BLOB data, and I'm having test data only!). In a production environment a small nvl bug like this could cause a serious problem.

I am pretty sure that my performance problem is solved, but I'm still guessing. It would be very helpfull if the formspider team explains in an article how the datasources are filled and row processing is done.

Akis.

asked 13 Oct '12, 10:58

Vlisidis%20Akis's gravatar image

Vlisidis Akis
163119
accept rate: 5%


Hi Vlisidis,

I am sure other team members will also weigh in but here is my two cents:

There are several ways I can think of solving this issue: I suspect 2. and 3. combined will help you the most.

  1. Ensure :masterId always has a value right from the start

  2. Uncheck the Query On Initialize checkbox on the data source. This way, your data source will not be automatically queried the first time it is referenced (showing the Panel on the screen in which a component is bound to your datasource would initialize the datasource and cause an automatic query).

  3. Don't query the BLOB column in this datasource. I suspect you are showing the rows in this datasource in a grid. Are you showing the content of the BLOB column for each row in the grid? Probably not. If that's the case, then simply don't query it. Query the BLOB column only for the selected row in the grid and show it under(next to or whatever) the grid.

Preventing querying large amount of data is something you need to be careful about. This might happen even if you don't have a BLOB but a big table behind the datasource.

Several team members in the company suggested we default the Query on Initialize checkbox to unchecked. This would also cause problems especially for beginners because they'd be surprised if they bind a component to a datasource and not see any data when the applications starts.

Maybe we can do something in between and allow developers change the default for each application. I don't know yet. We'll see. We are open to suggestions.

Hope this helps.

Best, Yalim

link

answered 13 Oct '12, 11:16

Yalim%20K.%20Gerger's gravatar image

Yalim K. Gerger ♦♦
1914
accept rate: 20%

edited 13 Oct '12, 11:17

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×63

Asked: 13 Oct '12, 10:58

Seen: 1,150 times

Last updated: 13 Oct '12, 11:17


© Copyright Gerger 2017