Hi Tim,

In ElevateDB in the Session component the property KeepTablesOpen is by
default False. When using a C/S setup with only TEDBQuery components,
according to the manual, it should speed things up considerably if set to
True. I was wondering though, being False by default, what are the downsides
or risks when setting the KeepTablesOpen property to True.

The second question. If set to True, the table buffer will probably be more
useful. Or, does ElevateDB in C/S setup remains its table buffer, even if
the table is closed. If yes, than setting the KeepTablesOpen property to
True will be less useful, especially if the risks are too high.

A third question about this property. If set to true and 5 users are
connected and each of them queried a particular table, the table will only
be closed when the last user disconnects from the server. What if only 4
users accessed this table (using TDBEQuery) and they all disconnected, but
user number 5 is still connected (but did not query this table) the table
will be closed internally even though user number 5 is connected to the
server. I am correct in both cases?

Finally, a fourth question. The Configuration Database and Information
Schema provide a lot of information. Unless I am mistaken, it does not store
a list of tables which are opened internally (with regard to the
KeepTablesOpen property). Can this info be made available? The necessity of
this info greatly depends on the answers of previous questions.

Thanks in advance.

Best regards,
Fons

Fons,

<< In ElevateDB in the Session component the property KeepTablesOpen is by
default False. When using a C/S setup with only TEDBQuery components,
according to the manual, it should speed things up considerably if set to
True. I was wondering though, being False by default, what are the downsides
or risks when setting the KeepTablesOpen property to True. >>

The risk is that an operation that would require exclusive access to the
table may fail because the table is still open with shared access.

<< The second question. If set to True, the table buffer will probably be
more useful. Or, does ElevateDB in C/S setup remains its table buffer, even
if the table is closed. If yes, than setting the KeepTablesOpen property to
True will be less useful, especially if the risks are too high. >>

Once a table is closed for good (all references to the table are gone and
KeepTablesOpen=False), all resources dedicated to that table are freed.

<< A third question about this property. If set to true and 5 users are
connected and each of them queried a particular table, the table will only
be closed when the last user disconnects from the server. What if only 4
users accessed this table (using TDBEQuery) and they all disconnected, but
user number 5 is still connected (but did not query this table) the table
will be closed internally even though user number 5 is connected to the
server. I am correct in both cases? >>

Yes, you are correct.  The table can only be "kept open" if it is opened by
the session with the KeepTablesOpen property set to True.

<< Finally, a fourth question. The Configuration Database and Information
Schema provide a lot of information. Unless I am mistaken, it does not store
a list of tables which are opened internally (with regard to the
KeepTablesOpen property). Can this info be made available? The necessity of
this info greatly depends on the answers of previous questions. >>

In C/S mode, you can use the ServerSessionLocks table to find this
information out.   Any tables that are opened shared or exclusive will show
up in that table with the appropriate lock indicated in the LockType column:

http://www.elevatesoft.com/edb1sql_serversessionlocks_table.htm

Try it in the EDB Manager and you'll see what I mean.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thanks for the info. Still, I have some additional questions.

> The risk is that an operation that would require exclusive access to the
> table may fail because the table is still open with shared access.

May fail in the sense that an exception is thrown, right?

I use ElevateDB is C/S mode and only use twoTEDBQuery components (one for my
database and one for the config database) and not a single TEDBTable. If I
understand correctly, when KeepTablesOpen = False, the effectiveness of the
buffer is almost nil, because the Query component is accessing a different
"table" everytime and thus kills the buffer of the previous accessed
"table". Correct?

If yes, than I will almost certainly need to set KeepTablesOpen to True,
because this way the buffer of each accessed "table" will remain. Correct?

> In C/S mode, you can use the ServerSessionLocks table to find this
> information out.   Any tables that are opened shared or exclusive will
> show up in that table with the appropriate lock indicated in the LockType
> column.

Let's assume that all users except myself have closed my app (and therefore
their sessions) but all accessed "tables" remain open when KeepTablesOpen is
set to True (since my session is still active). If by now I need to perform
an operation that needs exclusive access, it will not fail ?   I hope not,
because my session is the only one, so I should be able to use it in
exclusive mode.

Apart from the risks of failing an exclusive operation, there is no higher
risks of corruption? Again, I hope not, because as I see it, setting
KeepTablesOpen to True is almost certainly favourable for the way my app
works with ElevateDB.

Again, thanks in advance for your input.

Best regards,
Fons

Fons,

<< May fail in the sense that an exception is thrown, right? >>

Yes, you'll simply get a lock error.

<< I use ElevateDB is C/S mode and only use twoTEDBQuery components (one for
my database and one for the config database) and not a single TEDBTable. If
I understand correctly, when KeepTablesOpen = False, the effectiveness of
the buffer is almost nil, because the Query component is accessing a
different "table" everytime and thus kills the buffer of the previous
accessed "table". Correct? >>

Yes, closing the query each time will cause the last reference to the table
to be closed, thus causing the table to be closed by EDB because
KeepTablesOpen=False;

<< If yes, than I will almost certainly need to set KeepTablesOpen to True,
because this way the buffer of each
accessed "table" will remain. Correct? >>

Correct.   The table will remain open with a usage count of 0 but will
retain a shared lock on the table so that it can be simply opened again by
incrementing its reference count (a very quick operation .

<< Let's assume that all users except myself have closed my app (and
therefore their sessions) but all accessed "tables" remain open when
KeepTablesOpen is set to True (since my session is still active). If by now
I need to perform an operation that needs exclusive access, it will not fail
?   I hope not, because my session is the only one, so I should be able to
use it in exclusive mode. >>

Yes, it will fail.  However, I've looked into this further and I can modify
it for 1.07 so that it doesn't have this issue.  The workaround for now is
to simply close and re-open the database being used before performing the
operation that needs exclusive access.   That will flush out the
previously-cached tables.

<< Apart from the risks of failing an exclusive operation, there is no
higher risks of corruption? >>

Nope, not at all.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

> Correct.   The table will remain open with a usage count of 0 but will
> retain a shared lock on the table so that it can be simply opened again by
> incrementing its reference count (a very quick operation .

Great!! That is certainly a big plus as speed is always of the essence  

But the fact that the buffer remains is also very important to me, because
some of my tables are small enough to fit entirely into the buffer and thus
ElevateDB is as fast as possible (working soly from memory with these
tables). Then there are of course some that will never fit into the buffer,
but some parts of the indexes will and since it now only has to increment
its reference, ElevateDB will perform at peek efficiency.

> Yes, it will fail.  However, I've looked into this further and I can
> modify it for 1.07 so that it doesn't have this issue.  The workaround for
> now is to simply close and re-open the database being used before
> performing the operation that needs exclusive access.   That will flush
> out the previously-cached tables.

The workaround aint needed, because my app is still in development and if
all goes well will be ready for testing somewhere in March or April 2008.
So, if you can modify it for version 1.07 it will be much appreciated. Thank
you!!

> << Apart from the risks of failing an exclusive operation, there is no
> higher risks of corruption? >>
>
> Nope, not at all.

Exactly what I was hoping you would say. The KeepTablesOpen property will be
set to True now for sure.

Thanks again for your help and the future update.

Best regards,
Fons

Fons,

<< But the fact that the buffer remains is also very important to me,
because some of my tables are small enough to fit entirely into the buffer
and thus ElevateDB is as fast as possible (working soly from memory with
these tables). Then there are of course some that will never fit into the
buffer, but some parts of the indexes will and since it now only has to
increment its reference, ElevateDB will perform at peek efficiency. >>

Yes, keeping the buffers around will definitely improve performance.

<< The workaround aint needed, because my app is still in development and if
all goes well will be ready for testing somewhere in March or April 2008.
So, if you can modify it for version 1.07 it will be much appreciated. Thank
you!! >>

It's already done.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

> It's already done.

You're the best!  

Seriously, the support you offer is outstanding! And very much appreciated.

Best regards,
Fons

Um, whats the trick?

I'm using a session with
   KeepTablesOpen = True
   RecordLockProtocol = lpOptimistic

and getting performance that is no better than if I used the default values for the session.  

The query that I'm testing with is already "optimized" as well as can be according to the execution plan...

Karl,

<< Um, whats the trick?

I'm using a session with
   KeepTablesOpen = True
   RecordLockProtocol = lpOptimistic

and getting performance that is no better than if I used the default values
for the session. >>

There's no guarantee that using these values will give you better
performance.  You have to have usage patterns in your application that are
helped by them in order for the performance to improve.

--
Tim Young
Elevate Software
www.elevatesoft.com