Firstly, for file base databases you really want to try and use components which properly marry up to the underlying access model. This means for example file based databases you want to use TTable with Indexes and SetRange wherever possible to access, in preference to TQuery, since the BDE isn't really a very good "database engine" and doesn't really optimise queries. As a result, it will often effectively perform an full linear file read/"table scan" whereas TTable with SetRange and FindKey/Locate would effectively merely position a cursor on a file handle. However, In your case I'm not sure if you'll get full benefit via your access path (ie BDE to Access) as there might be some unholy translation going on. A lot better would indeed probably be ADO, where the TADOTable is almost certain to properly access the underlying JET engine. While I do not recommend anyone to use Paradox anymore, due to the status of it and the BDE, it is true that you'd have been far happier with it and the BDE than with Access and the BDE. You can do some pretty efficient things with Paradox and the BDE, over even a slow network, when used carefully. (But, as I've said, stay away from TQuery, and optimise access by using indexes and locates on the file yourself...)
In actuality, 30Mb is IMO an almost paltry amount of data, compared to the amount of RAM and resources available on todays PC's and networks. What I mean by that is that if you are having complaints, it suggests to me that more data (all of it?) might perhaps be read by client machines than are really neccesary when going about their business, more frequently than is necessary (probably due to the inefficiencies introduced by the data access layer/components themselves.) To put this comment in perspective: A few years ago one of the system we look after was still Paradox/file based. The total database size was at that point approximately 800Mb-900Mb or so. The largest file/table was probably about 400Mb and contained approximately 1.8 million records, there were about 2 or 3 others near on a million records and most of the other significant ones were tens of thousands. There were about 10-15 concurrent users using this database over a 100Mbit LAN, and for the most part this worked exceedingly well, generally... Looking up records in the largest files/tables were still nearly instantaneous despite the size as they used suitable indexes together with TTable, FindKey and SetRange. Things bogged down however with complicated reporting queries where tables had to be related to each other and you thus had no choice but to read large amounts of information over the network from multiple tables to produce reports (since obviously there were no centralised database server with the bandwidth to cope with such things). The point is this is an order of magnitude more information that in your database and was still quite workable (just not with respect to the reporting requirements for the most part as well as a few other irritating management/maintenance problems), so I'm fairly sure you can improve your situation. Having said all that, it might just be worth your while to move to a client server based database sometime soon... (but that might be a lot of work...) I guess I should've asked: What type of machines are running this system (spec), how fast is the network, and can you give an idea of how you access the data in the database (what type of queries and so on)? Hope that helps, Walter Dave Murray wrote: >Hi, > >I've got a Delphi app that uses an Access database via BDE (Delphi 4 >so no ADO). At the moment there are about 600,000 records in the main >table and it is growing by about 12,000 - 15,000 records a week. The >database itself is currently over 30Mb in size. The app runs a lot of >queries against the main table and has been slowing down as it grows. >The speed is now getting to the point where the users are complaining. > >So am I reaching the limits of what I can do with an Access database? >Would I get a speed increase by switching to Delphi 2006 and using ADO? >Is it time to start looking at SQL Server? > >Thanks, >Dave. > > > ----------------------------------------------------- Home page: http://groups.yahoo.com/group/delphi-en/ To unsubscribe: [EMAIL PROTECTED] Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/delphi-en/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
