Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 17 total |
Converting from Paradox: Performance problems |
Wed, Jun 28 2006 7:39 AM | Permanent Link |
Georg Ledermann | Hi,
currently I'm trying to convert an old Delphi-5-Paradox-Application (using a lot of TTable and some LocalSQL-Queries) to DBISAM (Trial of 4.24 Build 1). I have finished the most parts (converting data, replacing TTable/TQuery to TDBISAMTable/TDBISAMQuery, adjusting the SQL statements), so the application works fine as desktop application with data on the local hard disk. So far, so good. Now I'm looking for the best way to run it in the network, but got stuck with performance problems. I have tried the following: 1) Using C/S-Mode (running dbsrvr.exe on a small W2K3 box as service) - Session.RemoteType = stRemote - Session.RemoteCompression = 6 - Session.RemoteAddress Works very fast with SQL statements (great!), but is too slow with TDBISAMTable (slower than with BDE). Connecting using WLAN and 100MBit-Ethernet is nearly the same. The compression speeds up slower network like WLAN a lot. 2) Using Local Mode - Session.RemoteType = stLocal - DB.Directory = '\\ServerName\\C$\DBISAM\MyData' (pointing to the same target as above) Using 100MBit-Ethernet: Works fast with TDBISAMTable (like BDE), but is slower with SQL queries than in C/S mode Using WLAN: Works too slow with TDBISAMTable (because of missing compression?), speed of SQL queries are the same like with Ethernet So, what kind of optimization can be done? I prefer using the C/S mode, if it is possible. I know that the application has a very bad design (all tables are opened at login, only one large data module with a lot of master/detail datasets, nearly no encapsulation of data access...). But perhaps there are some kind of DBISAM tricks for speeding up such a beast. Hope someone can help me... Kind regards, Georg |
Wed, Jun 28 2006 9:10 AM | Permanent Link |
Dave M | Georg Ledermann <ledermann@completho.de> wrote:
----------------------------------------------------------------- Just a couple suggestions. I don't think compression helps with (wired) local area networks. It may even slow things down. This is from my foggy memory from some time ago. If you are doing a lot of disk writes at one time, they must be in a transaction, or performance will be bad. WinNt NTFS is slooow for disk writes. As you may be aware, the BDE, depending on how it is set up may do delayed writes. Dave M |
Wed, Jun 28 2006 11:40 AM | Permanent Link |
Georg Ledermann | Dave, thank you for the hints. In my configuration the compression does really speed up a
lot, without compression reading from the database will slow down by factor 3 or something. This is the main reason I want to use C/S mode, not Fileserver mode. After further work I can give some more precise facts: - Speed problems occur while reading and displaying the table in a grid. I have not made any write test until now, so transaction handling does not help - The speed problem can be seen in a grid: Scrolling from record to record takes a few seconds each row. The same is while doing a "Locate" - The computer is a Pentium D with 3GHz and 3GB RAM, with Windows XP SP2 - I'm using an ExpressQuantumGrid, but the same problem occurs with TDBGrid - The grid is bound to a TDBISAMTable, the table contains only 200 records in my example - There is no filter and no OnFilterRecord active - The DBISAMTable has 50 fields, some of them are lookup fields to other small table, there is no CalcField I think the main problem is this: - The DBISAMTable is referenced as master by a lot of detail tables and the details are master of other details. All detail tables are open. For a test I have closed all detail tables before displaying the grid. Now the speed is much better. But I cannot close the other tables without rewriting a lot of the application. Are there any other things to try? |
Wed, Jun 28 2006 12:18 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Georg,
<< So, what kind of optimization can be done? I prefer using the C/S mode, if it is possible. I know that the application has a very bad design (all tables are opened at login, only one large data module with a lot of master/detail datasets, nearly no encapsulation of data access...). But perhaps there are some kind of DBISAM tricks for speeding up such a beast. Hope someone can help me... >> You've run into the main issue with file-sharing vs. client/server access. They are both very good at one or the other aspect (navigational or SQL), but not both. What you should look at is the BlockReadSize property. Set it to a larger value than 1 when doing any type of while (not EOF)..Next processing. Multi-row TDBGrid population is already optimized automatically by DBISAM to grab records in chunks of the number of TDBGrid rows. Also, try to keep the number of lookup fields to a minimum in your application. They cause a lot of round-trip requests to the database server for every record navigation, refresh, etc. For example, with just one Next, having 3 lookup fields can cause 3 more request/response cycles to the database server, with each incurring the same network latency as requesting a 1 meg block of records in one request/response cycle. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Jun 28 2006 12:26 PM | Permanent Link |
"Ralf Mimoun" | Georg Ledermann wrote:
[From Paradox to DBISAM] .... > Works very fast with SQL statements (great!), but is too slow with > TDBISAMTable (slower than with BDE). What exactly is slower? Opening the table? A filter, and if, what's the filter? SetRange? Locate? .... > 2) Using Local Mode > - Session.RemoteType = stLocal > - DB.Directory = '\\ServerName\\C$\DBISAM\MyData' (pointing to the > same target as above) > > Using 100MBit-Ethernet: Works fast with TDBISAMTable (like BDE), but > is slower with SQL queries than in C/S mode Of course. In C/S mode, the server can access the tables including the index files on harddisk. Via LAN, the computer must request every byte from a file server and get it through the line, with latency etc. > Using WLAN: Works too slow with TDBISAMTable (because of missing > compression?), speed of SQL queries are the same like with Ethernet Again, what is slow? > So, what kind of optimization can be done? I prefer using the C/S > mode, if it is possible. I know that the application has a very bad > design (all tables are opened at login, only one large data module > with a lot of master/detail datasets, nearly no encapsulation of data > access...). But perhaps there are some kind of DBISAM tricks for > speeding up such a beast. Hope someone can help me... DBISAM simply needs the right indexes. It does not cache that much data like DBE/Paradox because it don't need to with a good db design. But you can expand the cache buffers by yourself, see "Buffering and Caching" and "Customizing the Engine" in the online help of DBISAM. And something almost everyone forgot at least once: if you have something like "WHERE CharField1='ABC'", you will need a case sensitive index for CharField1. For "WHERE UPPER(CharField1)=UPPER('ABC')", you will need a case insensitive index. Ralf |
Wed, Jun 28 2006 1:33 PM | Permanent Link |
Chris Erdal | Georg Ledermann <ledermann@completho.de> wrote in
news:81CBBBE7-0747-4DE3-95B2-E91B59BE5064@news.elevatesoft.com: > I think the main problem is this: > - The DBISAMTable is referenced as master by a lot of detail tables > and the details are master of other details. All detail tables are > open. > > For a test I have closed all detail tables before displaying the grid. > Now the speed is much better. But I cannot close the other tables > without rewriting a lot of the application. > > Are there any other things to try? > > I seem to remember this problem in one application using SQLServer 6.5, and I improved it by closing any child tables and resetting a timer in OnBeforeScroll, and then reopening them with the timer set to a bit longer than the master table's scroll time. -- Chris |
Wed, Jun 28 2006 6:56 PM | Permanent Link |
Georg Ledermann | Thanks to all for helping. It seems that because of the horrible TTable architecture
(development has started in 1995) my app cannot be easily converted to C/S mode. But in fileservermode the application is now working with acceptable performance. The greatest performance boost was the elimination of some lookup fields and setting LookupCache for some other lookup fields to TRUE (hm, it seems that I should try this in the BDE version, too...) The migration is not finished yet, there is a lot of work to be done - but DBISAM seems to be the best BDE replacement for me |
Thu, Jun 29 2006 7:21 AM | Permanent Link |
"Ralf Mimoun" | Georg Ledermann wrote:
> Thanks to all for helping. It seems that because of the horrible > TTable architecture (development has started in 1995) my app cannot > be easily converted to C/S mode. Huh? I have a project that use TDBISAMTables everywhere. Mostly because tt's faster for some scnearios, eg. searching on the fly via Locate while the user types in the search keyword. It runs fast as hell in local and C/S mode. No problems at all. But you should avoid while not eof/next in C/S mode. In local mode too, btw. Ralf |
Thu, Jun 29 2006 8:32 AM | Permanent Link |
> What you should look at is the BlockReadSize property. Set it to a
> larger value than 1 when doing any type of while (not EOF)..Next > processing. This is most interesting, as I've not heard of this before. The documentation on it is sparse (it being part of the base VCL classes and not yours). Could you tell me (us?) what it actually does please? Is it essentially a buffering system to speed up looping through the table? /Matthew Jones/ | |
Thu, Jun 29 2006 10:13 AM | Permanent Link |
Georg Ledermann | "Ralf Mimoun" <nospam@rad-on.de> wrote:
> Huh? I have a project that use TDBISAMTables everywhere. Mostly because tt's > faster for some scnearios, eg. searching on the fly via Locate while the > user types in the search keyword. It runs fast as hell in local and C/S > mode. No problems at all. But you should avoid while not eof/next in C/S > mode. In local mode too, btw. Perhaps the performance problems of my app in C/S mode are not caused simply by using TTables, but the "special" architecture of the app: All TTables are opened at login, all TTables are in a big datamodule and dependent on each other (master/detail and lookup fields). It's really a very bad design and the best solution would be: Write it new from scratch - but this is another project I'm working on. The goal for the old app is: Leave the BDE behind and convert the app to DBISAM - but with *minimal* changes of the design. For example, if I would close/re-open some tables during runtime (a great performance boost, I think), the whole design has to be changed and this would be much work. I agree with you that DBISAM makes it possible to develop fast C/S application with TDBISAMTable, but converting an old-school-app is another story... |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Friday, September 20, 2024 at 05:39 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |