Solved

speed of inserting records into a Paradox table.

Posted on 2009-05-12
9
313 Views
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?
0
Comment
Question by:QurbanDurrani
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 37

Accepted Solution

by:
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
0
 

Author Comment

by:QurbanDurrani
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_Table4.Insert;
 T_Table4field1.AsInteger := data.AsInteger;
 T_Table4field2.AsInteger := data.AsInteger;
.
.
.
..
T_Table4field30.AsString := data.AsString;
T_Table4.post.
Hope it makes sense.

0
 
LVL 13

Assisted Solution

by:rfwoolf
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.
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 13

Expert Comment

by:rfwoolf
ID: 24370246
Out of interest, here is a list of BDE and Paradox Specs:
http://www.thedbcommunity.com/index.php?option=com_content&task=view&id=84&Itemid=59 
0
 

Author Comment

by:QurbanDurrani
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"?
0
 
LVL 13

Expert Comment

by:rfwoolf
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.
0
 
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);
var
  Qry: TQuery;
  Items: TStringList;
begin
  Items := TStringList.Create;
  try
    Qry := TQuery.Create(Self);
    try
      Qry.SQL.Text := 
        'INSERT INTO TABLE (FIELD1, FIELD2, FIELD3, FIELD4, ...) '+
        'VALUES (:F1, :F2, :F3, :F4, ...)                        ';
      Qry.Prepare;
      while not Eof(AFile) do 
      begin
        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];
        Qry.ExecSQL;  
      end;
    finally
      FreeAndNil(Qry);
    end;
  finally
    FreeAndNil(Items);
  end;
end;

Open in new window

0
 
LVL 8

Assisted Solution

by:BdLm
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
0
 

Author Comment

by:QurbanDurrani
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.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Magic Software info 18 130
URL for downloading Google Chrome for Win XP 2 152
select query - oracle 16 100
Breakpoint doesn't stop in my variable 3 23
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…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

816 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now