Tim,

Just curious... will the engine be capable of allowing
memory tables to grow as large as the application needs
regardless of physical or windows virtual memory
limitations? What would be ideal would be that an
"in-memory" database would be maintained in memory up to
some developer specified size at which time it would be
automatically swapped out to a disk based temporary table
(like one used for non-live queries) that is automatically
deleted by the engine when the application terminates. I ask
because I'm rewriting my reporting routines and it would be
great if I could always use in-memory tables which would be
very efficient for smaller reports and not worry if a large
user generates a huge amount of data for a report.

Sean

Sean,

<< Just curious... will the engine be capable of allowing memory tables to
grow as large as the application needs regardless of physical or windows
virtual memory limitations? What would be ideal would be that an "in-memory"
database would be maintained in memory up to
some developer specified size at which time it would be automatically
swapped out to a disk based temporary table (like one used for non-live
queries) that is automatically deleted by the engine when the application
terminates. >>

That's exactly what normal disk-based tables that are opened exclusively
do - there's no need to use an in-memory table in that case.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

>
> That's exactly what normal disk-based tables that are opened exclusively
> do - there's no need to use an in-memory table in that case.
>

Perhaps you misunderstood... an in-memory table ceases to
exist (essentially is dropped) when the engine is released
and is unique to the process... both qualities that make
them good to use when generating data for a user report.
These tables are invisible to other instances of the same
program & other users on different computers. An exclusively
opened disk table is permanent and is not unique to a
process, so there is extra work involved in creating and
deleting these tables. Also, since most reports are pretty
small, in-memory tables would seem to be the faster choice
since there is no overhead in creating the disk file.

What I wanted to find out is if there is a mechanism by
which an in-memory table can safely grow beyond the client
computer's memory limitations without just generating an
"out of memory" on a poorly equiped or configured PC.

Thanks,

Sean

Sean


The extra work for creating the table should be no more than specifying the directory, and the extra work in deleting is 1 line of code. It will be a bit slower writing the data to disk, but not much. If Tim altered the memory tables to keep checking if there's space left in memory for them, and ensures that he traps the problem caused when DBISAM thought there was but another program has just gobbled it up, I'm sure it would slow down creating a memory table.

I'm not usually on M$s side where they want a bigger faster PC for every new release of anything but this definitely seems like a case of a new pc needed.

Roy Lambert

Sean,

<< Perhaps you misunderstood... an in-memory table ceases to exist
(essentially is dropped) when the engine is released and is unique to the
process... both qualities that make them good to use when generating data
for a user report. These tables are invisible to other instances of the same
program & other users on different computers. An exclusively opened disk
table is permanent and is not unique to a process, so there is extra work
involved in creating and deleting these tables. >>

We're talking a minor amount of work here versus us recreating everything we
already have in the buffered, disk-based tables in a different form.  I
think what you want is simply an easy way of specifying that ElevateDB take
care of deleting the table when the process exits.  Everything else is
identical to what the buffered disk-based tables do.

<< Also, since most reports are pretty small, in-memory tables would seem to
be the faster choice since there is no overhead in creating the disk file.
>>

Creating a local disk file is a minor affair - a few hundred milliseconds at
most.  Besides, what you're asking for would require a disk file creation in
some cases also, so it's not like you're buying a whole lot of performance.

<< What I wanted to find out is if there is a mechanism by which an
in-memory table can safely grow beyond the client computer's memory
limitations without just generating an "out of memory" on a poorly equiped
or configured PC. >>

Yes, use a disk-based table that is exclusively opened.  You can even
specify the amount of buffering per table in ElevateDB, so you can pump it
up as high as you wish according to the available memory on the machine.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sean,

One other thing to remember is that in-memory tables will effectively have
their memory paged out automatically by the OS as needed, so you might not
have much to worry about to begin with.  ElevateDB, like DBISAM, separates
the memory table files into blocks of 64k internally.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


From my reading it wasn't just RAM he's bothered about but also older PC's with small page files running massive reports. Sounds about right doesn't it - old PC user producing biggest load of paper

Roy Lambert

Tim & Roy,

Thanks for the detailed responses. I wasn't asking for any
changes to V5... that probably got lost as the thread
progresed or I just wasn't clear from the start. I just
wanted to know if in-memory tables were safe from "out of
memory" errors on a poorly configured PC and Tim didn't
directly address that point the first time around.

I was inquiring because it would have been very clean to use
in-memory tables as intermediate tables for reports. I
thought it would be nice since they are automatically
segregated from other program instances & because if a user
users turn off their pc to stop printing (seen it happen
numerous times!) there would be no stray tables to clean up.

Looks like I'll just have to do some coding

Hope you guys have a nice weekend,

Sean



Tim Young [Elevate Software] wrote:

> Sean,
>
> << Perhaps you misunderstood... an in-memory table ceases to exist
> (essentially is dropped) when the engine is released and is unique to the
> process... both qualities that make them good to use when generating data
> for a user report. These tables are invisible to other instances of the same
> program & other users on different computers. An exclusively opened disk
> table is permanent and is not unique to a process, so there is extra work
> involved in creating and deleting these tables. >>
>
> We're talking a minor amount of work here versus us recreating everything we
> already have in the buffered, disk-based tables in a different form.  I
> think what you want is simply an easy way of specifying that ElevateDB take
> care of deleting the table when the process exits.  Everything else is
> identical to what the buffered disk-based tables do.
>
> << Also, since most reports are pretty small, in-memory tables would seem to
> be the faster choice since there is no overhead in creating the disk file.
>  >>
>
> Creating a local disk file is a minor affair - a few hundred milliseconds at
> most.  Besides, what you're asking for would require a disk file creation in
> some cases also, so it's not like you're buying a whole lot of performance.
>
> << What I wanted to find out is if there is a mechanism by which an
> in-memory table can safely grow beyond the client computer's memory
> limitations without just generating an "out of memory" on a poorly equiped
> or configured PC. >>
>
> Yes, use a disk-based table that is exclusively opened.  You can even
> specify the amount of buffering per table in ElevateDB, so you can pump it
> up as high as you wish according to the available memory on the machine.
>

Hi Tim,

May I restate the original question in a way, that may be
more familiar to SQL server users: will v5 have temporary tables?
Some SQL engines (including one of your competitors) have this
option, so you can have global and local (session local, I
understand) temporary tables. Will v5 have this feature?

The syntax (although I don't like it very much) is:
CREATE GLOBAL TEMPORARY TABLE
CREATE LOCAL TEMPORARY TABLE

MSSQL has different name prefixes, like # and ## for these
things (and I have to admit, that I don't like it either .

The notion of temporary table is somewhat clearer, then
memory table, cause it really isn't anyones business where the
data is stored as long as it is accessible fast and will not
cause a computer to crash in case it gets too large. This is
IMO exactly what you need, when you need to prepare a report.


Regards,
Michael



Tim Young [Elevate Software] wrote:
> Sean,
>
> One other thing to remember is that in-memory tables will effectively have
> their memory paged out automatically by the OS as needed, so you might not
> have much to worry about to begin with.  ElevateDB, like DBISAM, separates
> the memory table files into blocks of 64k internally.
>

Michael


Interesting - what defines their lifespan? Is it when the app shuts or what?

Roy Lambert