Hi formspider team,

The API api_session.add and api_session.getValueXX procedures handle the session variables. These session variables are stored in the database in table T_BDF_WEBSESSIONITEM. I guess the procedures work this way:

api_session.add('variable-name') ==> do a select on the table to check if 'variable-name' exists; if not create the session variable record; else update it.

api_session.getValueXX('variable-name') ==> do a select on the table for 'variable-name' and return the value.

The table T_BDF_WEBSESSIONITEM has indexes on the columns BDF_WEBSESSION_OID (which is the session-id) and BDF_WEBSESSIONITEM_OID. So, whenever i call api_session.getValueXX the database will do an index-range-scan with a cost of 5 (according to SQL Developer).

I think it would be best to minimize the number of session variables as much as possible. For example: i think it is better to create a custom table with columns for all session variables and only create one session variable that points to the record in the custom table. Then only one database call is needed to get the record with all session variables. Instead of the 30-40 calls that are currently made.

Am i right about this?

By the way, this insight has clarified the problems with response times with views calling api_session.getValueXX, because the optimizer can be totally mixed up.

Kind regards, Jan Willem

asked 12 Sep, 10:17

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
14311658
accept rate: 0%


Hi Jan Willem,

api_session.getValueXX('variable-name') calls a bdf_websessionitem_qry and it executes a SELECT query which uses a unique index. This is the fastest way to fetch a single entry.

You can also call the underlying query directly to fetch as many variables as you like:

v_webSesssion_oid := nvl(in_webSession_oid, bdf_support.getCurrentWebSession);
select wsi.value_nr into v_value_nr
from t_bdf_websessionitem wsi
where wsi.bdf_websession_oid=v_webSesssion_oid
and wsi.name_tx=in_name_tx;

As you thought already your issue is related to the fact that you are calling these functions directly from view for multiple session variables. This slows down your query execution.

As a solution, you can create a custom table with columns for all session variables, as you suggested, and a column for Formspider web session id. To tie this record to a specific session. Then you can join this table into your view.

You can get web session id using bdf_support.getCurrentWebSession.

Kind regards,

link

answered 16 Sep, 10:18

Serdar's gravatar image

Serdar ♦♦
100k4
accept rate: 12%

Thanks Serdar, i am glad i understand the mechanism :)

By the way, bdf_websessionitem_qry does NOT use a unique index because there is no index on session-id+variable-name. It selects on the index on the session-id and then a range scan for the variable-name. In my case often multiple variable-names are needed for queries. One select for a variable takes a fraction of a second time, but altogether all selects take a second and sometimes that's too much.

Kind regards, Jan Willem

(17 Sep, 03:39) Jan Willem V...

Hi Serdar,

is bdf_support.getCurrentWebSession the same function as api_session.getID ?

Kind regards, Jan Willem

link

answered 17 Sep, 06:49

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
14311658
accept rate: 0%

Just to be sure... Until now i have always seen the same values.

(19 Sep, 05:48) Jan Willem V...

Yes, it is the same value and it is a good idea to use api_session.getID API.

(19 Sep, 07:14) Serdar ♦♦
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:

×17
×5
×2

Asked: 12 Sep, 10:17

Seen: 105 times

Last updated: 19 Sep, 07:14


© Copyright Gerger 2017