Loading Seq File to Database Performance

palmeal · Post by **palmeal** » Mon Mar 28, 2005 4:08 am

I have a sequential file which consists of 595 rows and am loading it into a table on Sybase.

SEQ_FILE --> SYBASE_OC

The file consists of 3 columns - numeric(9), varchar(128), char(12) and matches the definition of the table on Sybase that it is being loaded into.
The operation clears the table on Sybase first (max 595 rows) before inserting the data.

The Performance Stats show that only 8 records per second are inserted into the Sybase table - that's almost 75 seconds for this load. The table in question has no indexes and I have tried both page and row level locking. I also have exclusive use of this server so there is no contention.

If I bulk copy (bcp) this file into the table in question it loads in under 1 second.

I also took all of the entries in the file and converted it to 595 individual insert table values string - this took 0.6 seconds to run.

How can I speed up this operation as I don't believe that this would be expected behaviour ?

ray.wurlod · Post by **ray.wurlod** » Mon Mar 28, 2005 4:40 am

Use DataStage to create the data file then use BCP.

Bulk loaders will beat INSERT statements 10 times out of 10.

roy · Post by **roy** » Mon Mar 28, 2005 5:34 am

Hi,
Beside agreeing with Ray on Bulk loads,
Have you encreased the packets size? (512 multiples)

IHTH,

palmeal · Post by **palmeal** » Mon Mar 28, 2005 6:31 am

Cheers guys, I replaced the SYBASE_OC with SYBASE_BCP_Load and it did improve performance considerably. The only packet size that I was allowed to use was 512.
There doesn't appear to be a function on the SYBASE_BCP_Load to clear down the table first so I guess I'll have to add in another stage to do this first.
What would be the best way to clear this table within the same Server Job. I have a SYBASE_OC stage earlier in the process where I could piggy-back a delete string to either the SQL Before/After tabs. I see this approach as messy however and wonder how else I can achieve this.

roy · Post by **roy** » Mon Mar 28, 2005 7:10 am

How about a truncate table? (will you not get permitions?)

as prefered method is to use the command line bulk loader with some DBA work you may get it done there or in a different job as you performed the delete