SQL server load performance issue

[dsscholar]

HI ,
My job is trying to load around 20M records to SQL server. I am using ODBC connector stage to load the sql server table. We do not have SQL server ent stage. It is an insert operation only. Even though job's load performance is very low. It gave maximum 3000 records per sec for insert. Please let me know how can I improve the performance of the job for loading.

[ray.wurlod]

Oh come on! Look at all the layers between DataStage (on UNIX) and SQL Server (on Windows). You shouldn't be at all surprised. Manage expectations better.

[SURA]

Try to load the data from a Dataset to SQL Server in another job. Just a try.

DS User

[ray.wurlod]

Write to a text file or FTP, get BCP to load the data on the Windows machine.

[dsscholar]

Thanks Ray and Sura, I will try both and let you know what is the result. Anyways I do not have idea about BCP. I have to search for that utility/function.

[dsscholar]

i have tried loading with dataset but performance is still same (1900 rows/sec). We have discussed for BCP but there is storage memory issue so we can't choose that option. Is there any other way to boost the loading?

[ray.wurlod]

Get more storage. BCP (the bulk loader for SQL Server) does not require any permanent storage, but does require temporary storage for the data file.

[chulett]

Otherwise it's standard things to look at for any load - commit intervals, indexes, array size, etc etc. Nothing is going to beat a native bulk loader, however.