Bulk load performance for Sybase IQ loads

dataservices performance
1

Hi ,

I have some ETL jobs and I want to change the target database to Sybase IQ and have to execute the jobs. I don’t want to change anything in the database. I have to change target load level performance for jobs .

I have checked the performance by using bulk loads for Sybase IQ target . I have tested with 437,228 records .

Loading times are given below for same set of set of data on same standalone system ( Job server and source & target database on same system)

For normal load : 31 minutes 26 secs ( 4:09:14-----4:40:40)

Bulk load without binary option : 32 minutes 59 secs (4:46:48-----5:19:47)

Bulk load with binary option : 34 minutes 57 secs (5:25:59-----6:00:56)

But above figures showing bulk load taking more time compare to normal load. I have read in the technical manual binary option gives more performance but that only taking more time .

Please guide me how can i achieve bulk load performance . I have to change any settings to see the performance using bulk load.

Thanks & Regards,

Ramana.

Ramana :india: (BOB member since 2009-04-30)

2

what is the complete version of DS ?

manoj_d (BOB member since 2009-01-02)

3

Hi,

I am using the DS 4.2 . It is taking more time for file creation in the bulk load. It is taking very few minutes to load into the table. I have tried with the named pipe line by giving buffer size 1GB for 40 MB data, It is also taking about 30 mins. Is there any additional settings required for bulk load. I am not finding any performance when I am using bulk load.

Thanks & Regards,
Ramana.
Log Files Screenshots.zip (123.0 KB)

Ramana :india: (BOB member since 2009-04-30)

4

Well, I believe that there 2 options here:

  • Degree of parallelism @BODS side. BODS is well tuned to deal with Sybase IQ, so therefore i do not think that you can optimize something very extraordinary on BODS side. You can get few sec faster results , but not huge impact
  • Sybase IQ. As you well might know there are several IQ parameters -iqmc -iqtc and if you’re on previous release of IQ then there is a Data load parameters as well. I would suggest you to export your data to .dat files and then load the data from script and also work with your Sybase DBA in order to tune IQ db. These parameters may differ - I use different IQ parameters for ad-hoc and different for data loads, by doing this data loads finishes even 2.5-3 times faster that standard ad-hoc memory parameters.

Antremons (BOB member since 2006-03-21)

5

Hi Antremons,

Thanks for your suggestion . There was a problem with source excel sheet reading . It is not a problem of IQ or DS performance. I changed to table /flat file and it is taking 400,000( 25 MB) records taking 1.5 minutes from local laptop. For POC I used pipe method instead of client file loading method. It is working fine.

Thanks & Regards,
Ramana.

Ramana :india: (BOB member since 2009-04-30)