Delphi: Adding 38000 recs to a Paradox table is SLOW!
Christian Tiberg
Each record consists of about 50 bytes, read from a straight text file - with a
lightning fast routine I developed in BP7.. One index is used, on a alpha field
with 20 chars. This operation takes 16 minutes to complete on my (admittedly
slow) 386dx/25.
Since I can read the source text file (3.2MB) in under one minute (haven't
timed it, but the routine loads as fast as the disk can provide data), it seems
that it is the Paradox table that's slow as my grandmother.. The technique I
use now is:
Table1.Insert;
{ set all fields from values read }
Table1.Post;
Does anyone know if there is a faster way? Is, for example, Append faster than
Insert? I sure do need help on this one, can't expect my users to tie up their
computers for 16 minutes once a week :(
Best regards,
Christian // cti...@silver.ct.se
... I used to be indecisive. Now I'm not sure.
Marc Palmer
writes:
Try setting Table1.Exclusive := True; before you start.
Multi-user sucks doesn't it.
********************************************************************
* Marc P. E-Mail me for PGP 2.6 key *
* *
* "Most of the ideas I have at the moment have to do with things *
* that are completely impossible, so I am wary about sharing them. *
* They are, however, the only thoughts I have." - Dirk Gently *
********************************************************************
Vince Risi
christia...@silver.ct.se (Christian Tiberg) wrote:
>Hello All!
>
>Each record consists of about 50 bytes, read from a straight text file - with a
>lightning fast routine I developed in BP7.. One index is used, on a alpha field
>with 20 chars. This operation takes 16 minutes to complete on my (admittedly
>slow) 386dx/25.
This is not slow at all, try running this sort of insert into an
Oracle Database on a RS6000-580 Risc Box running AIX. Your 386dx/25
is flying by comparison. Your getting 40 records inserted per second,
pure luxury.
Vince
=====
Erik Turner
: In article: <ftn_2.200.602.0_32d...@silver.ct.se> christia...@silver.ct.se (Christian Tiberg)
: writes:
: >
: > Hello All!
: >
: > This operation takes 16 minutes to complete on my (admittedly
: > slow) 386dx/25.
: >
: > timed it, but the routine loads as fast as the disk can provide data),
: > The technique I use now is:
: >
: > Table1.Insert;
: > { set all fields from values read }
: > Table1.Post;
: >
: > Does anyone know if there is a faster way?
: > Is, for example, Append faster than Insert?
No. It says in the manual that Append is eqivalent to a Insert on a
table with a primary key.
: > I sure do need help on this one, can't expect my users to tie up their
: > computers for 16 minutes once a week :(
Let me give you a benchmark. I have a 486/33 ISA bus computer with a
3 year old 250MB hard drive. I timed record insertion to be about
300/second (which for your problem works out to be about 126 seconds or
2 minutes). I would guess that this is in the ballpark for the relative
speed of the two processors.
There are some things that you can check:
- Make sure that you have disabled all controls or even better, use
a table which has nothing attached to it.
- Make sure that you haven't attached slow validation routines (I did this one)
- Make sure that you have no secondary indexes or referential integrity
defined for the table (Database Desktop).
If this doesn't help, consider that on-line client-server databases are
actually slower for insertions than Paradox (300/second is really moving for
Sybase on an IBM RS/6000 390).
Christian Tiberg
> with 20 chars. This operation takes 16 minutes to complete on my (admittedly
> slow) 386dx/25.
>
>
> Table1.Insert;
> { set all fields from values read }
> Table1.Post;
MP> Try setting Table1.Exclusive := True; before you start.
MP> Multi-user sucks doesn't it.
I've tried that too, yes. It shaved an amazing half minute of the time :((
Best regards,
Christian // cti...@silver.ct.se
... Some men are not fools, they stayed bachelors.
Luke Webber
>Marc Palmer wrote in a message to Christian Tiberg:
>MP> Try setting Table1.Exclusive := True; before you start.
>MP> Multi-user sucks doesn't it.
>I've tried that too, yes. It shaved an amazing half minute of the time :((
You don't by any chance have write-behind caching turned off on the drive
concerned do you? Look for the /X option for smartdrv or a LazyWrites
line in system.ini.
--
Luke Webber
* Note: The opinions expressed by Luke Webber are in no way supported *
* by his employers, Luke Webber Consulting Services *
The Nomad
>If this doesn't help, consider that on-line client-server databases are
>actually slower for insertions than Paradox (300/second is really moving for
>Sybase on an IBM RS/6000 390).
This is the second time I have seen someone slam the performance of
client-server databases. I agree that for 38000 insert statements,
nearly any client-server database would be slow. But much of this
slowness has to do with network topology and traffic.
But the bottom line is, I wouldn't want to do 38000 inserts into a Sybase
database. I would use the bulk-copy routines to send records in chunks.
The fact remains that if you are doing 38000 inserts into a client-server
client-server database, you are using the wrong method.
************************************
* The Nomad *
* tno...@digital.net *
************************************
Hallvard Vassbotn
>Hello All!
>Each record consists of about 50 bytes, read from a straight text file - with a
>lightning fast routine I developed in BP7.. One index is used, on a alpha field
>with 20 chars. This operation takes 16 minutes to complete on my (admittedly
>slow) 386dx/25.
Lucky you!
I'm updating a Paradox database every night. It does 20-30 thousand inserts
and 20-30 thousand deletes and takes 4-5 _hours_!
Admittedly, I use four secondary indexes and the tables are rather large (20-40
Mb) and the recordsize is 120 bytes. I'm using Paradox Engine for DOS in a real
mode program to do this.
It seems that deletes are very slow...
--
Hallvard Vassbotn | Falcon AS (a Reuters company) | Without programmers,
hall...@falcon.no | Stranden 1, 0250 OSLO, Norway | the world would stop!
Christian Tiberg
VR> From: vin...@iaccess.za (Vince Risi)
VR> In article
VR> <ftn_2.200.602.0_32d...@silver.ct.se>,
VR> christia...@silver.ct.se (Christian Tiberg) wrote:
>Hello All!
>
>Each record consists of about 50 bytes, read from a straight text file - with
a
>lightning fast routine I developed in BP7.. One index is used, on a alpha
field
>with 20 chars. This operation takes 16 minutes to complete on my (admittedly
>slow) 386dx/25.
VR> This is not slow at all, try running this sort of insert
VR> into an Oracle Database on a RS6000-580 Risc Box running
VR> AIX. Your 386dx/25 is flying by comparison. Your getting 40
VR> records inserted per second, pure luxury.
My problem is that FrontDoor (using the age-old Database toolkit from Borland)
performs the same operation in MUCH less time, something like half :(
Best regards,
Christian // cti...@silver.ct.se
... Phasors locked on target, Captain.
Mike Frisch
: This is not slow at all, try running this sort of insert into an
: Oracle Database on a RS6000-580 Risc Box running AIX. Your 386dx/25
: is flying by comparison. Your getting 40 records inserted per second,
: pure luxury.
Don't forget that Oracle is probably a little more 'sure' of
itself when it writes a record by doing database validation and checking
integrity. Paradox is quick, but it's not near as secure as the real SQL
servers.
Mike.
--
====================================================================
Mike Frisch Email: mfr...@saturn.tlug.org
Northstar Technologies Compuserve: 76620,2534
Newmarket, Ontario, CANADA
Quang Pham
>In article <ftn_2.200.602.0_32d...@silver.ct.se>,
>christia...@silver.ct.se (Christian Tiberg) wrote:
>>Hello All!
>>
>>Each record consists of about 50 bytes, read from a straight text file - with a
>>lightning fast routine I developed in BP7.. One index is used, on a alpha field
>>with 20 chars. This operation takes 16 minutes to complete on my (admittedly
>>slow) 386dx/25.
did you try powerbuilder? then you will know what 'slow' will really
mean! they can not even load pass 1000 records.
to improve loading time. table should have index on key field(s) and
sorted as index-key defined and post to table as append instead of
insert. or inport from output file direct to paradox will be much
faster.
>This is not slow at all, try running this sort of insert into an
>Oracle Database on a RS6000-580 Risc Box running AIX. Your 386dx/25
>is flying by comparison. Your getting 40 records inserted per second,
>pure luxury.
>Vince
>=====
Christian Tiberg
: > Does anyone know if there is a faster way?
: > Is, for example, Append faster than Insert?
ET> No. It says in the manual that Append is eqivalent to a
ET> Insert on a table with a primary key.
: > I sure do need help on this one, can't expect my users to tie up their
: > computers for 16 minutes once a week :(
ET> - Make sure that you have disabled all controls or even
ET> better, use a table which has nothing attached to it.
Nothing is attached to it right now, it's on a form with only a button and a
TTable...
ET> - Make sure that you have no secondary indexes or referential integrity
ET> defined for the table (Database Desktop).
But what if I NEED a secondary index??? Is it perhaps faster to call
CreateIndex later?
ET> If this doesn't help, consider that on-line client-server
ET> databases are actually slower for insertions than Paradox
ET> (300/second is really moving for Sybase on an IBM RS/6000
ET> 390).
Terribly sorry, but can't compare to SH*T :) My comparison is against another
program doing the exact same operation in less than half the time :(
Best regards,
Christian // cti...@silver.ct.se
... A fool and his money is my kind of customer!
Christian Tiberg
TN> But the bottom line is, I wouldn't want to do 38000 inserts
TN> into a Sybase database. I would use the bulk-copy routines
TN> to send records in chunks.
TN> The fact remains that if you are doing 38000 inserts into a
TN> client-server client-server database, you are using the
TN> wrong method.
I'm using a local Paradox table, but what kind of bulk-copy routines are you
talking about? I've looked at TBatchMove, but can't seem to find any way to use
that either... The source of my data is a straight ASCII file (the FidoNet
nodelist) that I parse.. That part of the program is NOT the problem, it's
faster than my harddrive can feed it (using huge blocks and ASM to read the
file)...
Best regards,
Christian // cti...@silver.ct.se
... 'Forty-two,' said Deep Thought, with infinite majesty and calm.
The Nomad
>talking about? I've looked at TBatchMove, but can't seem to find any way to use
>that either... The source of my data is a straight ASCII file (the FidoNet
>nodelist) that I parse.. That part of the program is NOT the problem, it's
>faster than my harddrive can feed it (using huge blocks and ASM to read the
>file)...
>
My note was in response to people saying that inserting thousands of rows
into a *client-server* database is slow. My response is "Of course it's
slow - don't do that. Use the right tool". For local databases, insert
is probably your only option.
Vince Risi
christia...@silver.ct.se (Christian Tiberg) wrote:
> My problem is that FrontDoor (using the age-old Database toolkit from Borland)
> performs the same operation in MUCH less time, something like half :(
What happens if you load the 38000 records without an Index and reindex
later?
Are you appending 38000 records or replacing 38000 records?
Vince
=====