Fetch Buffer Size and Insert Batch Size, Performance

by James McIntyr » Sun, 26 Jan 2003 01:12:16

While my DTS package already performs very well, I have
never really evaluated the Fetch Buffer and Insert Batch
settings. I'm looking for a metric based perhaps on
records transfered, type of raids, etc... Anyone know of a
good resource for this? I'm hoping to avoid testing all
the possibilities.

Thanks in Advance.

James

Top

1. SP3 Data Pump Fetch Buffer Size

Please excuse me if somebody already posted this topic, but a deja...
errrrr...google did not turn up anything nor did a MS search.

We just moved from SQL Server 7.0 SP1 in development to SP3.
We have several datapumps where the Fetch Buffer size has been
adjusted to say 100. The source is a query to a SQL Server database.
The connection is with OLE DB, and we are running MDAC 2.1 SP2.

The query does reference tables in the same database as the
target table. The target table is not a referenced table.

Anyway, the datapump basically does not pump, where it did
in the past. When looking at sp_who2, the query does appear to be running, but is
in a hung state. Much like if you are fetching data from a recordset, and stopped
after grabbing the first page and left it there.

I have seen this same problem with SP1, but not that often.
I thought this was a problem with previous versions of MDAC, but can't remember.

When the fetch buffer size is set to 1, the query does work.
This left me thinking that unless, the number of rows is greater than the fetch
buffer size it may hang. Unfortunately, I had 357 rows in my test case. So right
now I have now idea why the thing hangs.

If anybody has a good explanation for this it would be most appreciated.

Tim

2. Informix Authorised Dealer

3. Problem with Fetch buffer size

4. Problem finding first record using CDaoRecordset class

5. Fetch Buffer Size

6. Database Consultant - 5 years DBA, Oracle 6 - 7.2 - 7.3, PL/SQL, UNIX, backup/recovery - NE

7. DTS: Data pump Task: Fetch Buffer Size

8. Limiting # of Open Databases

9. Changing Fetch buffer size creats problem

10. Backup image size changes with different buffer sizes

11. Batch Size & Performance