Solved

speed of inserting records into a Paradox table.

Posted on 2009-05-12
9
304 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 36

Accepted Solution

by:
Geert Gruwez earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 13

Expert Comment

by:rfwoolf
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:QurbanDurrani
Comment Utility
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
Comment Utility
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 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

11 Experts available now in Live!

Get 1:1 Help Now