Fwd: Sessions vs App -- Explained (for Valentina Server)

[Ruslan Zasukhin]

On 9/28/10 3:00 PM, "Thorsten Hohage" <thohage at genericobjects.de> wrote:

Hi Thorsten,

> So while I don't be 100% sure if we can do all the statements, for VServer,
> too - it would perhaps be a good idea to have such a white paper ...

I will comment this letter in regard to Valentina Server.


>> Message: 9
>> Date: Mon, 27 Sep 2010 16:02:10 -0500
>> From: Bruce Huber <bruce at biosig-id.com>
>> Subject: Re: Sessions vs App -- Explained (for SQL Server)
>> To: REALbasic Betas <realbasic-betas at lists.realsoftware.com>
>> Message-ID: <4CA10652.5030806 at biosig-id.com>
>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>> 
>>  "Communication is the hardest thing we do"...
>> 
>> NOTE::  I still don't know what happens in the case of the embedded
>> REALSQLDatabase.  I haven't had time to test that out yet.  The
>> following is all related to Microsoft SQL Server.
>> 
>> NOTE::  It turns out that this is also the way that the Java databases work.
>> 
>> After researching and testing Ian's and Erik's points, (concerning
>> database connections with SQL Server), I believe that they are both
>> correct...  and that they are both just looking at "opposite sides of
>> the same coin".
>> 
>> The original discussion centered around whether the database should be
>> opened from the App object or the Session object.  I pointed out that,
>> in the Java DB world, we open the database ONE TIME at the App level,
>> with a single user credential - and that the web app users (sessions)
>> queries are kept separate and run simultaneously.
>> 
>> Concerning this web application scenario, and supporting multiple
>> simultaneous users "with only a SINGLE user database credential"...  I
>> believe the main point of confusion in people's minds is equating "user
>> credential" and "database connection"...  So we will need to clear up
>> all of these items....
>> 
>> Concerning "SQL Server Database Connections and Multiple Recordsets"...
>> Ian and Erik can both be right... depending on:
>> 
>>   1. (Erik) - Dynamic, Keyset, or Static server-side cursor types,
>>      allow the SAME connection to be used to open MULTIPLE recordsets.

Same for Valentina .

>>   2. A client-side cursor allows the SAME connection to be used to open
>>      multiple recordsets.

Same for Valentina .

>>   3. (Ian) A ForwardOnly server-side cursor type (default), requires
>>      (and will open) a NEW connection for EACH recordset.

For Valentina Server there is no such limit.
Many cursors in the scope of single connection.


>> When dealing with multiple recordsets over the same connection, the
>> database driver and the database server assign each recordset a unique
>> ID (internally) and use that ID to ensure that result data gets to the
>> correct queue in the application.

Well, the same can be said about Vserver.

Just with correction. Cursors / RecordSets always get unique ID.
Never mind to which connection they belong.
 
>> Concerning "where to OPEN the SQL Server database connection" - App or
>> Session?:
>> 
>>   1. If you DO have DB credentials for each web app user and wish to
>>      use those credentials (for security purposes, DB logging, etc.).
>>          * Open the database connection from the web app's user Session
>>            object.

Logical, there is no other way for different logins

>>   2. If you DO NOT have DB credentials for each web app user or DO NOT
>>      wish to use such credentials.
>>          * Open the database connection from the web app's Application
>>            object.

Also I can add.  Don't know about SQL Server, but I think they do similar.

In fact Vserver if opens database, then do not close it, even if vclient
send command db.close().  Server still keep it open. So next open already
happens much faster -- no need to read schema objects into RAM and build
model. 

With this info, I do not see big difference between  open db in app or in
session. At least for VServer.


>> Concerning "SHARING a SINGLE SQL Server database connection with
>> MULTIPLE users"...  This concept is the one that creates the most
>> confusion...  But that is only because the database server is a black
>> box - and tries to make things simple for the developer...  Keep in mind
>> the above points about "Database Connections and Multiple Recordsets" as
>> we discuss this:
>> 
>>    * OPEN a SINGLE SQL Server database connection at the APPLICATION
>>      level with a valid user credential.

>>         1. Any following SQL requests that DO use a ForwardOnly
>>            (default) server-side cursor -- whether they are issued from
>>            the Application level or a Session level -- will either
>>            AUTOMATICALLY CREATE a NEW database connection (if a
>>            CONNECTION POOL exists) -- or will STALL until the original
>>            database connection is free.

>>         2. Any following SQL requests that DO NOT use a ForwardOnly
>>            server-side cursor -- whether they are issued from the
>>            Application level or a Session level -- will SHARE the SAME
>>            SQL Server database connection.

>>         3. In EITHER case -- a second EXPLICIT developer coded OPEN
>>            database connection statement is NOT required -- the
>>            credentials used in the INITIAL connection open will be used
>>            in any subsequent connection opens that are automatically
>>            made from a connection pool.
>> 
>> As you can see, although we tend to "think" about the issue as:
>> 
>>    "SHARING a SINGLE SQL Server database CONNECTION with MULTIPLE users"
>> 
>> ... in reality, we should be thinking of it as:
>> 
>>    "SHARING a SINGLE SQL Server CREDENTIAL with MULTIPLE users"
>> 
>> ... and then, just make sure we have a connection pool set up so that
>> ForwardOnly server-side cursor queries don't stall.

No comments here. I have not see start of thread and for me not clear if
here is discussed app which self works on server side, or app which works on
N client computers.

About pool of connections -- any pool, any cache - is just  an optimization
for speed. 

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]