Hi there,

We are currently in the process of evaluating a replacement database engine for a large
Delphi 7 application. Its is currently using the BDE and Paradox tables.

In short, the application runs as a server version, and also distributed version to
several hundred users who perform some level of data entry. The data is transferred back
to the corporate database via a number of methods.

We have started some performance testing and found that the product does not perform as
well as expected.
Our initial tests have been simple and are as follows.

Test 1
======
Open a table that contains 280,000 rows. First field is a unique Integer field with
primary index

TDBISAMDatabase
TDBISAMQuery
   Read Only := True
   Select * from tablename   

Local data file


Local = High spec PC using local IDE drive

Network = Across 11mbps LAN using Low spec workstation but still file/server mode.

Test2
=====

Same as Test 1, with the following exceptions:
   Index added to surname field
   Select * from tablename where surname = 'SMITH'
   TDataSource, linked to TDBISAMQuery
   TDBGrid Added, linked to TDataSource

Note: Surname field has an index.

All times are shown in minutes.seconds format.

Test1
=====
Local
   First run   0.08
   Second run   0.09
   Third run   0.10

Network
   First run   3.14
   Second Run   3.15
   Third run   3.17

Test2
=====
   First run   0.03
   Second run   0.03
   Third run   0.03

Network
=========
   First run   1.43
   Second run   1.40
   Third run   1.40


Although DBIsam has performed well in some of the other product comparisons we have done
to date, these performance tests (compared to the other products we are evaluating) are
not what we expected.

Any comments on why these initial tests are so slow would be appreciated.

I am happy to provide the figures comparing  products side-by-side if needed.

Regards,
Mark

Mark

a) First thing is set RequestLive to True
b) Point Session.PrivateDir to a folder in a local path not a network path
c) Remember if you use case-insensitive indexes you must compare fields with
UPPER like Upper(Field)='EDUARDO'

I hope it helps you.

Eduardo

Do a search here for 'tips' for DBISAM.  Things like having two indexes with one field in
each, instead of one index on two fields etc.

You may also notice that you don't find any complaining posts here... anywhere.

I've used DBISAM since version 1 and can tell you that the competition just haven't caught
up.  The support here from both the ElevateSoftware and other experts is astonishing.
Fast, friendly, incisive.  For commercial apps built with Delphi, there's DBISAM first,
then daylight, then some more daylight... then something else.  

HTH,
Cheers,
Graham W.

Mark


Just to expand a little on what Jose wrote.

DBISAM queries operate in two modes, live and canned. Unless you specifically ask for it the query will return a canned dataset and this involves writing the data out to a temporary table. So for your test 1 you are effectively copying all 280k records to a temporary table - its gonna take time, especially over a network in file server mode.

A live query simply opens the existing table and applies any WHERE clause as a range/filter.

You need to be careful with this because live queries can only be returned in a limited set of circumstances - eg an ORDER BY clause that doen't use an existing index.

The other thing to consider is that since DBISAM's filters and their sql are damned near identical if you're operating in file server mode then unless you're making extensive use of JOINs use TDBISAMTables rather than queries.

Roy Lambert

Jose Eduardo Helminsky wrote:
....
> c) Remember if you use case-insensitive indexes you must compare
> fields with UPPER like Upper(Field)='EDUARDO'

More exactly: UPPER(Field) = UPPER('EDUARDO')

Ralf

Roy,

....
> The other thing to consider is that since DBISAM's filters and their
> sql are damned near identical if you're operating in file server mode
> then unless you're making extensive use of JOINs use TDBISAMTables
> rather than queries.

Absolutely no need to do so. I wrote a huge system, 99.9% query based (there
are only 2 TDBISAMTable objects, in-memory tables which are a little bit
easier to set up via table than query). No problems at all.

Ralf

Mark,

<< Our initial tests have been simple and are as follows. >>

As others have already stated, make sure that all TDBISAMQuery components
have RequestLive set to True, and that any indexes defined are
case-sensitive.  If you need the indexes to be case-insensitive, then modify
the query as Eduardo indicated:

Select * from tablename where UPPER(surname) = UPPER('SMITH')

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi All,
First of all thanks to the great and prompt feedback that has been provided. It's great to
have strong vendor and community support.


I have:
   Set RequestLive to true
   Set Private Dir to a local dir
   Ensured cas-ins set to false on all indexes.
    Changed where clause to Upper(surname) = Upper('SMITH')


The new results are as follows:

Test1
=====
Local
  First run   <0.01
  Second run  <0.01
  Third run   <0.01

Network
  First run   <0.01
  Second Run  <0.01
  Third run   <0.01

Test2
=====
Local
  First run   0.07
  Second run  0.06
  Third run   0.06

Network
  First run   0.32
  Second run  <0.01
  Third run   <0.01

Most of the times have reduced but the times for test two local have increased. Can anyone
comment on this?

Also, the time taken for Test 2 network is still of concern to us, especially during our
comparative stage of our evaluation.

We have tried this with and without being bound to DB aware controls (simply to show the
result) with no noticeable change.
Any comments on this would again be appreciated.

Regards,
Mark

>    Ensured cas-ins set to false on all indexes.
>      Changed where clause to Upper(surname) = Upper('SMITH')

You have that the wrong way round!

--Bill Sparrow--

Mark Pountley wrote:
....
> Ensured cas-ins set to false on all indexes.
>  Changed where clause to Upper(surname) = Upper('SMITH')

A-ah. You do a case insensitive seach, so you need a case insensitive index
for that. If you need case sensitive and case insensitive search, define 2
indexes.

Right now, you start a brute force search, which need some time via LAN.

Ralf