speed of inserting records into a Paradox table.

Posted on 2009-05-12
Last Modified: 2013-11-23
Hi Experts,
I am using Delphi7 and Paradox. My application(standalone,single user) uses mostly TTables and some TQueries. The paradox database is arranged in a master/detail relationship(several levels deep). For example table1 is master of table2 which is a master of table3 which is a master of table4 and so on.. One of the functionality of the application is to bring in backed up informations. As you keep bringing in backedup information, the application gets slower and slower. I might mention that when I bring in chunks of backed up information, most of it is inserted into lets say table4. So when the number of records in table4 gets to about 230,000 and I try to bring in the next chunk which contains about 16 to 18 thousand records for table4, the process takes about 25 minutes. The inserting of these records into table4 is done by TTable. So my question:
1) Is this speed normal of Paradpx database at this volume or is it an indication of structural and design weekness in my application and if so, what could be some strategies to speed things up?
2) My application often errors out at these volumes and the database is corrupted. I know Paradox is prone to corruption, but could these volumes also be contributing to corruption?
Question by:QurbanDurrani
  • 3
  • 3
  • 2
  • +1
LVL 37

Accepted Solution

Geert Gruwez earned 200 total points
ID: 24369438
a frequent problem i came across with paradox and dbase tables was slowness and corruption also
the indexes more often than not got corrupted
so we created a tool to recreate the table dropping all indexes and then recreating them by night
basically what database desktop does with restructure

then we moved on to MSSQL desktop engine, problem was fixed
now i would still suggest this or Oracle Express.

the slowness is an intrinsic part for these volumes of paradox and dbase
but not *that* slow ...

how are you inserting records ?
fastest is with insert queries

Author Comment

ID: 24369588
Thanks for your prompt  response Geert_Gruwez.
I probably should have also mentioned that table4 has about 30 fields, and one of them is a memo field, however, there is no data for the memo field in these tests.
I am not sure what you mean by "how are you inserting records ?", but i'll try to answer it as follows:
TTable is used for this part of functionality. so it would go something as follows:
 T_Table4field1.AsInteger := data.AsInteger;
 T_Table4field2.AsInteger := data.AsInteger;
T_Table4field30.AsString := data.AsString;
Hope it makes sense.

LVL 13

Assisted Solution

rfwoolf earned 200 total points
ID: 24370227
I would definitely not recommend paradox for handling this volume of records. If your application is simple enough you should look at transferring to firebird or another database as soon as possible - I found the changeover from paradox to firebird using Zeoslib components pretty impressive, with the exception of Boolean fields - how your application code works with boolean will determine this - one workaround is to create a calculated field of type TBooleanField and use that to equal your other field -- I can explain further if you make the move.
What Geert is saying about how you insert records - these days it's often better to do some things using SQL instead of letting the datasets do all the work. So you would use a query to INSERT a record into your database table with the required fields. This will always be faster than using a dataset directly, but, in most applicaitons it usually doesn't matter.
"1) Is this speed normal of Paradpx database at this volume or is it an indication of structural and design weekness in my application and if so, what could be some strategies to speed things up? "
I'm not sure, I never worked with paradox using that many records, and funnily enough I never got corruption, but I would say that paradox could be slow, and that having that many master-detail levels could also do that - they are dependant on indexes - so you should consider creating indexes on fields that you are using as keys for your master-detail table - but there again, having too many indexes on giant tables can really slow things down especially with something like paradox, but it may be worth a shot.
Another option is to avoid master-detail relationships in datasets. Try use SQL to 'show' master-detail relationships, for example, if Table1 has selected Customer # 12345, then to show all of his orders, instead of using master-dteail datasets, you could use SQL to say SELECT * FROM Orders WHERE CustomerNumber = 12345.
This way your query will fetch only the records it needs, instead of fetching the entire database table and only 'showing' the detail records.
2) My application often errors out at these volumes and the database is corrupted. I know Paradox is prone to corruption, but could these volumes also be contributing to corruption? Again I don't really know but I'd like to assume so. When I asked questions about Paradox and everyone said "yaknow you really should move away from it" I didn't really understand, and I must actually add that for simple small aplications with small databases I think you can live with it, but ultimately, having used REAL databases now, Paradox was a real headache for me.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 13

Expert Comment

ID: 24370246
Out of interest, here is a list of BDE and Paradox Specs: 

Author Comment

ID: 24370392
Thanks rtwoolf for your detailed response.
I read up on a few discussion threads regarding Paradox before posting this question. One of the threads was your question about converting paradox which was very informative(and funny the way you were describing your strugles with MS SQL). I am definately leaning towards moving away from paradox and will open another question for the pros and cons of Firebird vs DBISAM. But for now I want to identify the root cause and maybe some short term solutions so I can build a case and convince the powers to be of a conversion. I understand that converting to Firebird or DBISAM is not a small task. My understanding is that it is easily a 6 week project, which will probably take me longer given my level of expertise. Another compelling reason for moving to a different database is that eventually we would like to move this application to a Client/Server, multi user environment.
So anyway, What did you mean by "they are dependant on indexes - so you should consider creating indexes on fields that you are using as keys for your master-detail table"?
LVL 13

Expert Comment

ID: 24370753
1) Porting my app from Paradox to Firebird took me about 2 weeks of solid work, a maximum of about 3 weeks. I had a third party component made up of many many units that liked boolean fields - so that was one major obstacle (I couldn't just change the code to accept bit fields). I'm not saying your conversion wouldn't take 6 weeks, it very well might, it depends on the app.
2) When you link a master and detail dataset, you do so on a 'key'. If Table1 is your master table and it has your customer codes, and Table2 is your detail table and has orders for all the customer, if you tell Table 2 that its key is CustomerID, then it will need to sort Table 2 by 'CustomerID' to bring you the matching records. If you provide an index, it can do this faster, on the other hand, maintaining too many indexes can also slow things down because the DB has to maintaing them.
LVL 37

Expert Comment

by:Geert Gruwez
ID: 24372196
sometimes inserting 1 record triggers a event, and this event refreshes the dataset

the fastest would be to give a script with all the inserts in.
Paradox can only handle 1 insert at a time, so that's not an option

so the next is off course 1 insert at a time
preferably with a prepared insert querie

in this example the stringlist would be the bottleneck for speed !
so don't look at only the database querie, look at the whole app when inserting,
is it doing anything else except inserting, like gathering the records to insert ...

this sample will insert data from a CSV in a table using a query (no checks)
just an alternative way of inserting data
procedure TForm1.InsertData(AFile: File);
  Qry: TQuery;
  Items: TStringList;
  Items := TStringList.Create;
    Qry := TQuery.Create(Self);
      Qry.SQL.Text := 
        'VALUES (:F1, :F2, :F3, :F4, ...)                        ';
      while not Eof(AFile) do 
        Items.DelimitedText := ReadLn(AFile);
        Qry.ParamByName('F1').AsString := Items[0];
        Qry.ParamByName('F2').AsString := Items[1];
        Qry.ParamByName('F3').AsString := Items[2];
        Qry.ParamByName('F4').AsString := Items[3];

Open in new window


Assisted Solution

BdLm earned 100 total points
ID: 24372598
BTW:  I also suffer with D7  and Paradox
If the table gets corrupted I made the best experience with regeners paradox repair tool

Author Comment

ID: 24379728
BdLm, Thanks for your comment. I tried regeners paradox repair tool and I must say it is nice.
Thanks to all for your help. I'll split the points.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dev express lookupcombo 3 47
Drag & Drop... Data from one grid to another 2 47
delphi popmenu non latine charcters 3 35
Firemonkey Tcameracomponent resultion 1 26
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question