🚀 go-pugleaf

<Gerard Vanderveken Ghia_no_spam.pi.be> wrote in message 
news:43256774$1@newsgroups.borland.com...
>
> Hi,
> I have a table with 200 000 entries (key number and data). Each morning I 
> receive a file with records and I need to verify with the database table 
> if keys are added or deleted, and if the data was changed on the other 
> records.
> This process takes a very long time (2 hours). But I noticed that 2 times 
> 40 minutes are used in the fetching of the records.
> I timed a fetchall. Also IBconsole takes this long time (Table - data - 
> scroll bottom) to load the complete table.
> I tried to speed up by enlarging the bufferchunks to 50 000, but no avail.
> The CPU (800MHz) stays always under 1%. What is it waiting for?It makes no 
> difference on running the program and database on the same or different 
> PC.
> What can I do to speed up this and why isn't there more CPU time used?

If possible, try reading the dataset without caching the results 
(Unidirectional=TRUE).  A lot of CPU time is spent reallocating memory for 
buffers when caching lots of results.

Dan 

Does the file you receive contain all 200,000 records? If not, how many
records does a typicla file contain.

It sounds like you are bringing all 200,000 records to the client. How
long that  will take depends on the speed of and load on your network
but it will take a long time.

Either of the following approaches should be faster.

1) Read the flat file one row at a time and SELECT the matching row
from the database using the primary key. Then process the record on the
client. To select the row from the database use an IBSQL component with
a parameterized SELECT.

2) Convert the flat file to fixed length format. Create an external
table in the database that matches the fixed lenght file. INSERT the
data from the external table into a table in the database (this would
be a good place to use a temporary table if you are using IB 7.5.1). Do
the processing on the server.

-- 
Bill Todd (TeamB)


"Gerard Vanderveken" Ghia_no_spam.pi.be wrote:

> 
> Hi,
> I have a table with 200 000 entries (key number and data). Each
> morning I receive a file with records and I need to verify with the
> database table if keys are added or deleted, and if the data was
> changed on the other records.  This process takes a very long time (2
> hours). But I noticed that 2 times 40 minutes are used in the
> fetching of the records.  I timed a fetchall. Also IBconsole takes
> this long time (Table - data - scroll bottom) to load the complete
> table.  I tried to speed up by enlarging the bufferchunks to 50 000,
> but no avail.  The CPU (800MHz) stays always under 1%. What is it
> waiting for?It makes no difference on running the program and
> database on the same or different PC.  What can I do to speed up this
> and why isn't there more CPU time used?

"Dan Palley" <dan@trams.com> wrote:

>If possible, try reading the dataset without caching the results 
>(Unidirectional=TRUE).  A lot of CPU time is spent reallocating memory for 
>buffers when caching lots of results.
>
>Dan 
>
I tried that , but didn't make a significant difference.

"Bill Todd" <no@no.com> wrote:
>Does the file you receive contain all 200,000 records? If not, how many
>records does a typicla file contain.

Yes, it does. Every day there are between 100-500 modifications (keys added, keys deleted, data changed). 

>It sounds like you are bringing all 200,000 records to the client. How
>long that  will take depends on the speed of and load on your network
>but it will take a long time.

Yes, I'm doing a First and then a while not eof do processing and next loop.
I'm wondering why it takes so long. CPU load stays under 1% average at all times (peaks are 3 or 4 %)
Obviously, the CPU is more waiting then running. Is there not a way to make it more CPU intensive? 
I should think 100 minutes at 1% could be done in 1 minute at 100%?

>Either of the following approaches should be faster.
>
>1) Read the flat file one row at a time and SELECT the matching row
>from the database using the primary key. Then process the record on the
>client. To select the row from the database use an IBSQL component with
>a parameterized SELECT.

So, you think running 200 000 separate selects should execute faster? 
This will not be a one row select, because I have also to verify if there are database records in the gap between two flat file record numbers. Eg. select * from table where number between last_flat_file_number + 1 and flat_file_number order by number 

>2) Convert the flat file to fixed length format. Create an external
>table in the database that matches the fixed lenght file. INSERT the
>data from the external table into a table in the database (this would
>be a good place to use a temporary table if you are using IB 7.5.1). 

I'm using 6.02 (the freeware version).
Can this file for filling the table come from a stream? I preprocess the records already in a stringlist.

>Do the processing on the server.

You mean with a stored procedure?

>-- 
>Bill Todd (TeamB)
>
Thanks for looking into my problem.
Gerard Vanderveken.

> Yes, I'm doing a First and then a while not eof do processing and
> next loop.  I'm wondering why it takes so long. CPU load stays under
> 1% average at all times (peaks are 3 or 4 %) Obviously, the CPU is
> more waiting then running. Is there not a way to make it more CPU
> intensive?  I should think 100 minutes at 1% could be done in 1
> minute at 100%?

I am confused. What does the CPU usage have to do with this? The
process of moving records from the server to the client is I/O bound.
The constraint is how fast data can be moved over the network cable.
The CPU is doing nothing because it is waiting for the I/O to take
place.

> 
> > Either of the following approaches should be faster.
> > 
> > 1) Read the flat file one row at a time and SELECT the matching row
> > from the database using the primary key. Then process the record on
> > the client. To select the row from the database use an IBSQL
> > component with a parameterized SELECT.
> 
> So, you think running 200 000 separate selects should execute faster?

If the flat file contained a subset of the records then yes, I do.
 
> > 2) Convert the flat file to fixed length format. Create an external
> > table in the database that matches the fixed lenght file. INSERT the
> > data from the external table into a table in the database (this
> > would be a good place to use a temporary table if you are using IB
> > 7.5.1).
> 
> I'm using 6.02 (the freeware version).
> Can this file for filling the table come from a stream? I preprocess

No. It must be a fixed length file.

> 
> You mean with a stored procedure?

Yes. The way to improve performance is to drastically reduce the amount
of data moved across the network.

Bill Todd