Hi out there,

recently I abandoned Paradox and began rewriting one of my projects for
Interbase. It is likely that the program will mostly be used in a
multiuser environment, and Paradox lacks transaction control, which
(in my opinion) is indispensable for relyable multi-table applications.
Also local SQL seems to be too crippled for many constructions.
   But I'm beginning to realize now that I may run into serious trouble
because even the performance of the LOCAL Interbase version of my program
is horrible compared to Paradox. I realize that things must be going slower
in a client-server situation - what I was not aware of is that the
performance is that.

May I ask a few questions:

1) Is there anything I am not aware of in the BDE-setup of then
   Local Interbase driver or the SQL definition script of the
   database (caching, buffering or choosing clever index definitions
   that would speed up the application?

2) I tried so speed up indexed searching in the tables by bracketing my
   code with the following type of construction:

   Table1.DisableControls;
   ... search the table...
   Table1.EnableControls;

   But that doesn't make much difference either. Any comment?

3) Is the performance of the "big" Interbase Server for multiuser
   environments any better than that of LIBS (I am aware that this may
   depend on the kind of network and server hardware used)?

Any comment is greatfully appreciated since the future of the whole project
depends on solving the above problems. Thanks in advance

                                                     Andy

<snip>

Not that I am aware of.

This is generally a good idea.

General Comments:
1.   Remember that operating across a network in a client/server
environment will be slower than on a single machine simply because the
data must be transfered accross the network.  This can be offset by
the speed gained in using a db server.

2.  Despite Borland claims that you can take move from a single
machine environment to a client/server db just by changing the alias
it is not quite that simple.  This is accurate if a bit misleading
becuase of what was left out.  You must design the app to migrate to
the client server enviroment to begin with in order to maintain the
performance that you saw on the single machine.  This has been our
biggest stumbling block thus far.  

  a. A table in an sql database is NOT the same as a table in Paradox.
A sql database has no record numbers. ie  you can not get the 80'th or
the last record in a sql database so don't even bother.  If your
design depends on this, change your design.

  b. Don't use TTable with sql database.  See this months Delphi
Magazine for and excellent article on this subject.  Basicly a ttable,
when used with a sql database dose the following sql query
  select * from <tablename>
of course this returns the entire table as the result set and if your
table is of any size your apps performance just when down the tubes.
Use a TQuery which will return only those records that you want for
the current operation.  Yes multi-table queries are not updatable.  A
pain in the neck.  The solution is to do a read only query to obtain
the resullt set you want and then use other single table selects,
inserts and delets that are updateable to actually change the table.
NOTE: Rumor has it this will be much easier to do in Delphi 32

c Use stored procedures to do the updates.

Finally, begin questioning the way you write DB apps.  Despite what
you think at first when your new CS Database apps takes 10 secs to
find that 1 record that took a fraction of a second with Paradox it is
almost a certainty that it is your fault.  You did something or
assumed something that you shouldn't have. I know, I did many times.

Feel free to ask further.

In article <4env2s$1...@ftp.univie.ac.at>,
        atroe...@nelly.mat.univie.ac.at (Andreas Troester) wrote:

        Unfortunately, I haven't found a way to speed up Local InterBase by
adding more caching, etc.  If you find a way, please let us know.

        The only time this will make a difference is when you're stepping
through the records in a database.  All the above does is unconnect the
controls from the dataset such that the controls are not updated when the
dataset record pointer is changed.

        What performance are you complaining about?  Adding records in just
about all client/server databases is slow, searches should be relatively fast
depending on the complexity of the query.

Hope this helps!

Leave me email if you need more info.

Mike.

======================================================================
Mike Frisch                             Email: mfri...@saturn.tlug.org
Northstar Technologies                 WWW: http://www.io.org/~mfrisch
Newmarket, Ontario, CANADA                      Compuserve: 76620,2534

If I select about 100 records out of 10000, the performance should be the same or better than Paradox tables?
 Personally, I think it should since the sever is just sitting there waiting to answer queries and it has
much more resources to use to get the data.  But I have no experience with C/S, hope I will, though.

Ry

..........

:   b. Don't use TTable with sql database.  See this months Delphi
: Magazine for and excellent article on this subject.  Basicly a ttable,
: when used with a sql database dose the following sql query
:   select * from <tablename>
: of course this returns the entire table as the result set and if your
: table is of any size your apps performance just when down the tubes.
: Use a TQuery which will return only those records that you want for
: the current operation.  Yes multi-table queries are not updatable.  A
: pain in the neck.  The solution is to do a read only query to obtain
: the resullt set you want and then use other single table selects,
: inserts and delets that are updateable to actually change the table.
: NOTE: Rumor has it this will be much easier to do in Delphi 32

: c Use stored procedures to do the updates.

Thanks for responding! I'm beginning to see the point, but if I have to use
TQuery instead of TTable, in most practical situations the resulting dataset
is not updateable (as you already pointed out) and the following
problem arises:
Suppose you perform an edit on the current record displayed in e.g. a
TDBGrid connected to the non-updateable TQuery and then do a refresh.
Then (depending on a possible ORDER BY statement) the edited record might c
hange its location in relation to the imposed ordering.
To go to the first record after each edit is quite annoying for users.
But I can' really see how to make the modified record the current record
of thein DBGrid with acceptable performance:  You can't do
an indexed search since its a query! It seems to me that all one can do in
the general situation is a cycling through all the records with code like

......
Query1.First;
while not Query1.Eof do
begin
  if Query1.FieldbyName('PrimarykeyField').AsInteger=SaveKey then break;
  Query1.Next;
end;
.....

Obviously this is by no means satisfying. Any comment?

Thanks and best regards,

                                                Andy

Using DisableControls/EnableControls determines whether or not the
datasource is refreshed (and in turn the data access controls like
dbgrids).  If you are searching through a table to find a particular
record and the table is attached to a dbgrid (via a datasource) it
is a good idea.  If your table is not attached to a datacontrol,
then their is not effect.

Also, and more importantly, don't use the TTable to talk to a real
DBMS like interbase.  Instead use a TQuery and passthroughsql (a
paramter on the alias and the TDatabase Params).  This will cause
your sql to be processed by the server instead of delphi.  Limit
your result set by using 'where' clauses to reduce how many
records you get back.  The fewer the better.

The performance of the big IB server is better because it is 32-bit and
assuming it runs on a bigger box then you app does, it will be faster.  There
is not 'migration' issue with LIBS to IB/NT.  The database itself can be
moved from one database to the other with no changes.  There are a few
features that are not available in the 16-bit LIBS vs IB/NT.  I think
the main ones are Events and User-Defined Functions.

Borland has announced a 32-bit BDE and a 32-bit Local Interbase Server that
is signicantly faster and more robust and runs on Win95/NT.

Rodger Zeisler
Everest Software Corp.
r...@eversoft.com