| Subject | RE: [firebird-support] Slow performance with Index |
|---|---|
| Author | Edwin A. Epstein, III |
| Post date | 2005-02-04T02:03:20Z |
It is necessary to process these in batch mode. I have several SP's that
will set the process flag based upon multiple different criteria in the
database. These SP's run in a single transaction and can flag as many as
250K to 300K on their own. So semi-continuous scanning of the flag field is
sort of already being done. Its just that the amount of flags that are set
at any one time is already fairy huge. We take the batch of records and
send them out via FTP and then check the FTP every 5-10 minutes for a new
result file. This file is then manually processed into the database (going
to be automatic shortly). This processing happens fairly fast, about 700+
updates per second to the database. So it is usually always under an hour
for the processing results to be updated into the database.
I have had problems with a 2nd table. It gets "thrashed" so often that it
begans to severely impact its performance rather quickly. So many
inserts,deletes, triggers, etc. It causes a lot of garbage collection to be
running and has impacted the backup performance. It was, however, the
fastest at being exported.
-----Original Message-----
From: David Johnson [mailto:d_johnson@...]
Sent: Thursday, February 03, 2005 5:56 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Slow performance with Index
Suggestion 1. Is it necessary to process these in batch mode? If your
vendor can process these in real time you can distribute the bottleneck
over time, and it will be less apparent.
In real time, as records are written to the table (or possibly a
separate process pending table), they are processed and cleared (or
removed). To process 2 million records per day, you need to sustain a
rate of only 23 to 24 records per second. This is readily attainable on
wintel servers, and even desktops. Your application handles about 1/5th
this much traffic, so this performance may represent a good spike
solution.
In the case that you write to a process pending table, no flag is
required. The existence of a record in the process pending table is
evidence that processing is required.
Suggestion 2. Have a trigger write updated records to an external table
for later processing. This can work with either batch model or real
time model.
In batch model, the external file is simply fired off to the vendor for
processing and cleared. For performance reasons, you may wish to zip
the file before sending it off.
In this scenario, no flag is required. The existence of a row in the
external table is evidence of a need for processing.
Suggestion 3. Have a second process that runs in its own thread
continually scan the table "as-is" for items to process, append them to
a flat file, and update the flag. The existing index would buy you a
fair bit in this scenario, since you would only need to pull out 23 to
24 records per pass, assuming 1 second intervals between passes.
will set the process flag based upon multiple different criteria in the
database. These SP's run in a single transaction and can flag as many as
250K to 300K on their own. So semi-continuous scanning of the flag field is
sort of already being done. Its just that the amount of flags that are set
at any one time is already fairy huge. We take the batch of records and
send them out via FTP and then check the FTP every 5-10 minutes for a new
result file. This file is then manually processed into the database (going
to be automatic shortly). This processing happens fairly fast, about 700+
updates per second to the database. So it is usually always under an hour
for the processing results to be updated into the database.
I have had problems with a 2nd table. It gets "thrashed" so often that it
begans to severely impact its performance rather quickly. So many
inserts,deletes, triggers, etc. It causes a lot of garbage collection to be
running and has impacted the backup performance. It was, however, the
fastest at being exported.
-----Original Message-----
From: David Johnson [mailto:d_johnson@...]
Sent: Thursday, February 03, 2005 5:56 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Slow performance with Index
Suggestion 1. Is it necessary to process these in batch mode? If your
vendor can process these in real time you can distribute the bottleneck
over time, and it will be less apparent.
In real time, as records are written to the table (or possibly a
separate process pending table), they are processed and cleared (or
removed). To process 2 million records per day, you need to sustain a
rate of only 23 to 24 records per second. This is readily attainable on
wintel servers, and even desktops. Your application handles about 1/5th
this much traffic, so this performance may represent a good spike
solution.
In the case that you write to a process pending table, no flag is
required. The existence of a record in the process pending table is
evidence that processing is required.
Suggestion 2. Have a trigger write updated records to an external table
for later processing. This can work with either batch model or real
time model.
In batch model, the external file is simply fired off to the vendor for
processing and cleared. For performance reasons, you may wish to zip
the file before sending it off.
In this scenario, no flag is required. The existence of a row in the
external table is evidence of a need for processing.
Suggestion 3. Have a second process that runs in its own thread
continually scan the table "as-is" for items to process, append them to
a flat file, and update the flag. The existing index would buy you a
fair bit in this scenario, since you would only need to pull out 23 to
24 records per pass, assuming 1 second intervals between passes.
On Thu, 2005-02-03 at 11:35, Edwin A. Epstein, III wrote:
>
>
> >>First, no professional programmer would ever send 7 million records
> >>across a network to a client application, so I'm going to assume that
> >>whatever you're doing is happening on the server.
>
> Welllll. Not 7 million records across the network but between 100K and 2
> million records across the network. I don't really see that I have a
> choice. I have to export those records one way or another for processing.
I
> am using a 100 mbit network and a machine right next to the server to
obtain
> these records. I am up for suggestions on the fastest way.
>
> There are huge number of duplicates. I will not ever have to query the
> database for records with a flag value = 0, only records with a flag value
=
> 1.
>
> According to your definition I have an extremely volatile database. An
> average of 1 million records per week being inserted into the datbase. In
> addition to the insert, updates are happening on the records in about the
> same amount. The DNC_QUED field I am using to indicate that the record
> needs further processing outside of the server. This does not happen only
> one time. This may happen multiple times on the same record in a monthly
> period.
>
Yahoo! Groups Links