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.
>