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 ?
Loading Seq File to Database Performance
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi,
Beside agreeing with Ray on Bulk loads,
Have you encreased the packets size? (512 multiples)
IHTH,
Beside agreeing with Ray on Bulk loads,
Have you encreased the packets size? (512 multiples)
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
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.
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.
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
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
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)