Board index » delphi » Perfomanceproblems with tabels containing 600.000-1.000.000 records

Perfomanceproblems with tabels containing 600.000-1.000.000 records

Hi everybody

I have this huge Paradoxtable 600.000-1.000.000 records. And the problem is:

When i want to add an record i have to see if the record exist (locate). And if the record exist, the record should be updated with the new data, or if the record dosn't exist i have to adpend the
record. The hole thing works fine, BUT:
I have many records to add, and with the method i described above i only get 1-2 records added per second. And when i have ca 500.000 records to be added or updated, it takes a f....... long time: ca.
70-140 hours :(

But if i start with a emty table and I assume there are no record to add the same as an other record (dublicated records?!), then i can leave out the Locate. And then it goes realy fast. But this does
ofcourse not work when i want to updazte the table.

I thought of using BDEException (Key violation). But is ther a better/"cleaner" way?

Anyone got an idea: Howto update or add records fast.

 

Re:Perfomanceproblems with tabels containing 600.000-1.000.000 records


i have solved the problem!

-> the primary key (composit) had been deleted. No wonder it tok so long time to locate a record :)

Re:Perfomanceproblems with tabels containing 600.000-1.000.000 records


Have you tried using a SQL SELECT WHERE statement?  Are you using indexed
fields?

Christoph Lindemann <____...@post7.tele.dk> wrote in article
<3544eac3.1577...@news.k-net.dk>...

Quote
> Hi everybody

> I have this huge Paradoxtable 600.000-1.000.000 records. And the problem
is:

> When i want to add an record i have to see if the record exist (locate).

And if the record exist, the record should be updated with the new data, or
if the record dosn't exist i have to adpend the
Quote
> record. The hole thing works fine, BUT:
> I have many records to add, and with the method i described above i only

get 1-2 records added per second. And when i have ca 500.000 records to be
added or updated, it takes a f....... long time: ca.
Quote
> 70-140 hours :(

> But if i start with a emty table and I assume there are no record to add

the same as an other record (dublicated records?!), then i can leave out
the Locate. And then it goes realy fast. But this does
Quote
> ofcourse not work when i want to updazte the table.

> I thought of using BDEException (Key violation). But is ther a

better/"cleaner" way?
Quote

> Anyone got an idea: Howto update or add records fast.

Re:Perfomanceproblems with tabels containing 600.000-1.000.000 records


Are you using a TQuery or a TTable?  With Paradox, it's better if you
use a TTable - you then have access to the "FindKey" method which uses
the index to find a record and is very fast.  IMHO, this would be the
best way to go in this situation.

-Dell

On Mon, 27 Apr 1998 20:48:55 GMT, ____...@post7.tele.dk (Christoph

Quote
Lindemann) wrote:
>Hi everybody

>I have this huge Paradoxtable 600.000-1.000.000 records. And the problem is:

>When i want to add an record i have to see if the record exist (locate). And if the record exist, the record should be updated with the new data, or if the record dosn't exist i have to adpend the
>record. The hole thing works fine, BUT:
>I have many records to add, and with the method i described above i only get 1-2 records added per second. And when i have ca 500.000 records to be added or updated, it takes a f....... long time: ca.
>70-140 hours :(

>But if i start with a emty table and I assume there are no record to add the same as an other record (dublicated records?!), then i can leave out the Locate. And then it goes realy fast. But this does
>ofcourse not work when i want to updazte the table.

>I thought of using BDEException (Key violation). But is ther a better/"cleaner" way?

>Anyone got an idea: Howto update or add records fast.

Other Threads