Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 9 of 9 total |
Performance problem in multi-user mode |
Fri, Feb 3 2006 4:25 AM | Permanent Link |
Bruno | 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 |
Fri, Feb 3 2006 4:59 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Feb 3 2006 5:08 AM | Permanent Link |
Bruno | 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 |
Fri, Feb 3 2006 5:44 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Feb 3 2006 5:38 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Sat, Feb 4 2006 4:59 AM | Permanent Link |
"Erik Bellaard" | 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 |
Mon, Feb 6 2006 11:37 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Mon, Feb 6 2006 11:56 AM | Permanent Link |
"Erik Bellaard" | 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 > > |
Tue, Feb 7 2006 5:49 AM | Permanent Link |
Bruno | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |