Hi,

I am working to convert a bunch of apps, to use DBISAM. Currently they
use something ancient called BTFiler (which is working only local or
LAN shared drives, and is fracking fast BTW).

The thing in this apps, it was very important to show almost at all
times that a record is being edited by someone else. For example, when
you login, the login dialog shows a lot of names and you pick one and
hit ok. The "names" are user names, but actually they carry a lot of
configuration with them and so people not necesarily log in as
themselves, but they login as a "user" with certain rights. Anyway,
people probably didn't like hitting Ok and being told after some wait
that a record is being locked, or, worse, two people colide on the same
configuration and don't want to lose the data. And so, when user logs
in, its record is hard-locked with BTFiler locks and the login dialog
will display such record greyed. Same applies for a lot of other tables
where user can pick a record for editing (addresses, jurisdictions,
sales, mortgages, principals, lots of stuff).

Now, to "lock" a record in DBISAM, you simply put the table into edit
mode on that record. That means I have to use TDBISAMTables everywhere,
but I would actually like to go full SQL (why open big tables and load
the network..., even though that's what probably happens now and users
are happy).

I would appreciate any suggestions on how to use "locking" records with
DBISAM, server or no server ... or how to tell people "locking" is not
such a great idea when migrating to a database.

thanks,

Lucian

Lucian,

<< Now, to "lock" a record in DBISAM, you simply put the table into edit
mode on that record. That means I have to use TDBISAMTables everywhere, but
I would actually like to go full SQL (why open big tables and load the
network..., even though that's what probably happens now and users are
happy). >>

When you open a table, only the required rows are read, which are usually
very minimal.  Also, there's really no way to grab a lock and hold it via
SQL since it is optimistic by its very nature.  The only way to lock a
record in DBISAM is to call TDBISAMTable.Edit while using pessimistic
locking (the default).

BTW, this is the EDB newsgroup.

--
Tim Young
Elevate Software
www.elevatesoft.com

> BTW, this is the EDB newsgroup.

Ooops...

Tim, the thing is I'd like to push DBISAM/ElevateDB here at work, but
the apps currently are using this BTFiler crap and in my tests they're
actually faster then DBISAM. I am talking about tables having around
500 fields. Opening them requires 1 second when the files are on my
drive, while the real app works instantly when the (BT) files are on
lan. So, I am having a delay right here, at the beginning of
everything. My boss really does not care about what I use, but he'll
notice speed delays immediately.

So, I have a question. Since I don't have (yet) ElevateDB, I can't test
this. Do you guys, or anyone else, have something similar like tables
with 4-500 fields (8-9K record size, no BLOBs) and can tell if
ElevateDB is faster than DBISAM 4 ??


thanks,

Lucian

BTW, if needed, I have some sample tables with so many fields so
someone could compare it in ElevateDB/DBISAM

Lucian

Lucian,

> Tim, the thing is I'd like to push DBISAM/ElevateDB here at work, but
> the apps currently are using this BTFiler crap and in my tests they're
> actually faster then DBISAM. I am talking about tables having around
> 500 fields. Opening them requires 1 second when the files are on my
> drive, while the real app works instantly when the (BT) files are on
> lan.

Have you written your own TDataset interface to BTFiler? If not, then
DBISAM (and any other VCL compliant database come to that) is going to
have to construct 500 TField objects when you open the table whereas
BTFiler will just BlockRead some data into memory. This overhead comes
from TDataset->DB.PAS->VCL and there isn't anything that a database
engine vendor can do about that. You could construct persistent TFields
at design time - that might cut down the (percieved) initialisation delay.

Eryk

> Have you written your own TDataset interface to BTFiler?

Nope. Apps are very old. BlockRead rulz indeed.

> DBISAM (and any other VCL compliant database come to that) is going
> to have to construct 500 TField objects when you open the table

Yeah. I was stepping through the code while it was doing the
initialization and noticed a lot of work being done there. I was hoping
to find out it if that portion of the engine is faster in EDB...


> You could construct persistent TFields at design time - that might
cut down the (percieved) initialisation delay.

I think I tried that and it actually looks even slower.


thx,

Lucian

Lucian,

<< Yeah. I was stepping through the code while it was doing the
initialization and noticed a lot of work being done there. I was hoping to
find out it if that portion of the engine is faster in EDB... >>

It really can't be - this is all part of the standard DB.PAS unit.  There's
no way around Delphi constructing 500 TField objects if you want to
reference all of the those fields.

--
Tim Young
Elevate Software
www.elevatesoft.com

> It really can't be - this is all part of the standard DB.PAS unit.

Well, I think I am talking about TDataFile.CheckHeader, in
dbisamen.pas, where there is a loop

     for I:=1 to FHeader.FieldCount do

and down below you have

           for J:=1 to FHeader.FieldCount do
              begin ...
              end;
           for J:=1 to FHeader.FieldCount do
              begin ...
              end;

where practically it checks for duplicate FieldName and duplicate
FieldNum. Code raises DBISAM_HEADERCORRUPT if a duplicate is found.

I mean how can a file become corrupted and the corruption would mean
duplicating something... How can one get duplicates in a table? DBSYS
certainly does not allow it and hopefully CreateTable should not allow
that either (I didn't check).

So, for a start, I have 500 X 500, done twice. Half a second stays
here. I don't have a datamodule and I don't have any fields in my code
at this point. It's all TDBISAMTable.Open.

Than, because I am checking the table strcuture against a script and
have to check for modified indexes and fields, this stuff hapens again
when I call IndexDefs.Update and FieldDefs.Update (there's a
GetTempCursor call in TDBISAMTable.InitFieldDefs and elsewhere). So,
the 500X500 it's done first twice when opening the table, then twice
for Fielddefs.update and twice for IndexDefs.Update.

Stepping code only in dbisam folder, so far.
I guess it's fair to ask if *this* changed in EDB, no?


thanks,

Lucian

here's a possible approach (mind the wordwrap)

for I:=1 to FHeader.FieldCount do
 for J:=1 to FHeader.FieldCount do
   if (J <> I) then
     if (AnsiCompareText(Trim(FFieldDefinitions[J]^.FieldName),
FieldName)=0)
        or (FFieldDefinitions[J]^.FieldNum=FieldNum) then
       DataEngine.RaiseError(DBISAM_HEADERCORRUPT, '', '',
ProperDataDirectoryName, TableName);

cuts down 50% of my 1.5 million loops.


Lucian

After more checks, TDataFile.CheckHeader is called twice on
IndexDefs.Update. So, I now have 2 million loops. which I cut down to 1
mil, with the suggested approach. Believe me, I can see a difference.

Lucian