Session variables and response times

Question

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

Answer 1

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,

Answer 2

0

Hi Serdar,

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

Kind regards, Jan Willem

link

answered 17 Sep, 06:49

Jan Willem V...
143●1●16●58
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 ♦♦

Session variables and response times

Follow this question

Related questions