| Subject | RE: [firebird-support] To SP or not |
|---|---|
| Author | Dan Wilson |
| Post date | 2004-05-31T15:35:17Z |
On 5/31/2004 at 4:44 PM Alfred Thomas wrote:
Client-based method:
1. Read a record from a source file (including the primary key to be used in the FB table).
2. Test for presence of the record in the DB (select primary_key from mytable where primary_key=value).
3. If not present, issue Insert statement.
4. If present, issue update statement.
5. After processing 20,000 records, issue commit.
SP-based method:
client actions:
1. Open an external table file (create, truncate, write-only).
2. Read 20,000 records from a source file, converting them into external table format and writing each to external table file.
3. Close external table file.
4. Call SP.
SP actions:
For select lots_of_fields from myExternalTable do
Test for presence of the record in the DB (select primary_key from mytable where primary_key=value)
If not present, issue Insert statement.
If present, issue update statement.
5. Issue commit.
I know (from setting breakpoints) that steps 1-3 of the SP-based method take under 2 seconds. The SP execution followed by the commit then take right around 60 seconds. For 20,000 records, the client method takes about 30 seconds including the commit time. It seemed to me during my testing that the execution path through the SP that used the "update" statement ran much faster than the "insert" path, but I didn't explore that much.
Dan.
> >>My experience with this was the exact opposite. Contrary to all myNo. I simplified the test case down as much as I could, and as near as I could tell the delay was in execution of an "Insert" query inside the SP. The simplified logic boiled down to the following:
> expectations,
> >>the SP I tried ran twice as slow as the same logic executed from the
> client process
> >>using IBPP. Logic that required 30 seconds when run through the client
> required
> >>just over 60 seconds when run via SP. I may well have done something
> wrong, but
> >>have no ready explanation for what that might have been.
>
> >>Dan.
>
> Hmm that is strange, I was under the impression that an increase in speed
> was a given,
> when using SP's. This certainly was the case wherever I used it (FireBird
> and Oracle).
> Ever found the reason for the slow execution?
>
Client-based method:
1. Read a record from a source file (including the primary key to be used in the FB table).
2. Test for presence of the record in the DB (select primary_key from mytable where primary_key=value).
3. If not present, issue Insert statement.
4. If present, issue update statement.
5. After processing 20,000 records, issue commit.
SP-based method:
client actions:
1. Open an external table file (create, truncate, write-only).
2. Read 20,000 records from a source file, converting them into external table format and writing each to external table file.
3. Close external table file.
4. Call SP.
SP actions:
For select lots_of_fields from myExternalTable do
Test for presence of the record in the DB (select primary_key from mytable where primary_key=value)
If not present, issue Insert statement.
If present, issue update statement.
5. Issue commit.
I know (from setting breakpoints) that steps 1-3 of the SP-based method take under 2 seconds. The SP execution followed by the commit then take right around 60 seconds. For 20,000 records, the client method takes about 30 seconds including the commit time. It seemed to me during my testing that the execution path through the SP that used the "update" statement ran much faster than the "insert" path, but I didn't explore that much.
Dan.