Hello

I'm using Delphi 5 and DBISAM 3.24 Standard.

For some reason I'm running into massive perfomance problems when there's more than one user accessing the database. Here are some details:
I have two apps, the MainApp that writes to the database and the PublisherApp that acceses the same database in order to display data only. The PublisherApp does not modify any data. Depending on it's
mode data are shown on a monitor, on a big screen using a beamer or written to a mysql-database on the internet. It is easily possible that there are 5 or 10 PCs with the PublisherApp running and each of it
is publishing data in one way or the other. Therefore all computers are in a network and the PublisherPCs have access to the MainPC over a mapped drive where the database resides.

The thing is now when only the MainApp is running and modifying data everthing runs smoothly. As soon as I start one of the PublisherApps the performance in the MainApp goes down to the cellar.
In the MainApp I'm using TDBISAMTables and TDBISAMQueries. The tables run well but the queries slowdown everyting. The following query takes 0.5 secs in single-user mode and 26 secs when one other
app is accessing the database:
SELECT AddressNo, Name, (Name + ' ' + Firstname) as Fullname FROM address
WHERE Type IN (2, 3, 10, 11, 18)
ORDER BY Name, AddressNo

The addresstable has a "Name, AddressNo"-Index and contains 2500 records

I tried playing around with LifeResult and RemoteReadSize but that did not change anything. At programstart I enlarge all the MaxTableDataBuffer* on the Session.
I also get the same result when there are two MainApps (each on it's own computer) accessing the same database. Then it is the secondly started one the has bad perfomance.
If this is something that is known and solved in DBISAM 4 I would do the upgrade, off course. But only if...

Any help is appreciated
Thanks, Bruno

Bruno


A couple of things

1. If Type doesn't have an index create one.
2. You will always suffer a performance degradation going from single to multi use access - its to do with Windows and how it handles locking. There have been a few posts in the newsgroups about it so you might want to search them.


Also I can never remember if you need separate indices for the order by columns or not - I think not but I'm not sure.

Roy Lambert

Thank you, Roy.

Type does not have an index, didn't see this one and I will do it. But there are seperate indices for the order by columns, whether they are used or not in that case.
I agree that performance always suffers in multi-user access. But it should not make such a big difference.

Bruno




Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Bruno


A couple of things

1. If Type doesn't have an index create one.
2. You will always suffer a performance degradation going from single to multi use access - its to do with Windows and how it handles locking. There have been a few posts in the newsgroups about it so you
might want to search them.


Also I can never remember if you need separate indices for the order by columns or not - I think not but I'm not sure.

Roy Lambert

Bruno


I don't think V3 had the capability of generating a query plan but you might want to download the V4 DBSys, upgrade a copy of your table to V4 and play with it to ensure that the query is optimised.

Roy Lambert

Bruno,

<< The thing is now when only the MainApp is running and modifying data
everthing runs smoothly. As soon as I start one of the PublisherApps the
performance in the MainApp goes down to the cellar. In the MainApp I'm using
TDBISAMTables and TDBISAMQueries. The tables run well but the queries
slowdown everyting. The following query takes 0.5 secs in single-user mode
and 26 secs when one other app is accessing the database:

SELECT AddressNo, Name, (Name + ' ' + Firstname) as Fullname FROM address
WHERE Type IN (2, 3, 10, 11, 18)
ORDER BY Name, AddressNo >>

This is somewhat normal in the sense that the 0.5 seconds is an anomaly, and
the 26 seconds is probably closer to reality.  This is due to the
opportunistic locking in Windows file-sharing that permits a single-user
using a file to cache the file heavily to the local workstation until
another user opens the file for access.  At that point, things drop back to
"normal" performance by transferring data across the network as needed.  The
way to help alleviate this access time penalty is to increase the memory
buffering in DBISAM (which you're doing) and to ensure that you've got your
queries, filters, and searches optimized using available indexes.  That will
reduce the amount of data that will be transferred across the network and
will help performance immensely.  You can find out some pointers on the
optimizations here:

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

<< If this is something that is known and solved in DBISAM 4 I would do the
upgrade, off course. But only if... >>

Version 4 has reduced the amount of locking a bit, which does help with
opportunistic locking.  However, it won't provide a "magical" increase in
the performance.  There's only so much that can be done in such a situation
apart from moving to the C/S version.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

You wrote:
>  The way to help alleviate this access time penalty is to increase the
> memory buffering in DBISAM (which you're doing) and to ensure that you've
> got your queries, filters, and searches optimized using available indexes.

about indexes: if I have a filter, let say

accept := fieldbyname('status').asstring = 'X';

than - I understand - there might be performance increase if I have am index
on the Status field.
But:
must that be a index on this single field, or will the next index also be
OK:

index:  ixStatus  : fields: Status+CustNo


Regards,

Erik

Erik,

<< But:
must that be a index on this single field, or will the next index also be
OK:

index:  ixStatus  : fields: Status+CustNo >>

As long as the field is the first field in the index, then DBISAM will use
it to optimize the filter.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Thanks,

regards,

Erik

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:BFEA1DFC-CCAE-42EC-AD29-44F67C3FAC96@news.elevatesoft.com...
> Erik,
>
> << But:
> must that be a index on this single field, or will the next index also be
> OK:
>
> index:  ixStatus  : fields: Status+CustNo >>
>
> As long as the field is the first field in the index, then DBISAM will use
> it to optimize the filter.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Tim, Roy

thanks a lot. I defined a few new indices which increased perfomance in single-user mode. Multi-user mode did not change its performance.

Bruno