MSSQL Performance Problem

palmeal · Post by **palmeal** » Mon Apr 25, 2005 3:14 am

I am trying to write a simple server job to take comma separated files and load them into a table in MSSQL Server.
Unlike ORACLE/SYBASE which have load/BCP stages there doesn't appear to be anything that I can use for MSSQL Server except the ODBC stage. By using this method it took 5 1/2 minutes to load 3012 rows which is obviously not acceptable. There will be many rows and many files to process and I need it to take seconds rather than minutes.

Is there any other way to achieve this ?

The table that the data is being stuffed into has (currently) no indexes/no constraints or triggers. The server has been set up in an optimal manner.
If I load the same data file using MSSQL's DTS function then it takes under 2 seconds.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 25, 2005 4:20 am

Once upon a time BCP also worked with SQL Server. Is there no longer a bulk loader? I'd be surprised if this were the case.

palmeal · Post by **palmeal** » Mon Apr 25, 2005 4:25 am

Thanks Ray, I guess I could break out to UNIX and use the bcp utility.
I'm trying to do everything within DataStage without breaking out to UNIX but I guess in this case I may have no option. Having said that I'd be surprised if Microsofts bcp will sit and run on UNIX.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 25, 2005 1:36 pm

No, but you could write and FTP a text file using DataStage, then remotely execute bcp on the Windows machine.

palmeal · Post by **palmeal** » Tue Apr 26, 2005 3:31 am

Thanks Ray, I had joked to a colleague that this is what I'd have to end up doing.
Anyway, I've got the ftp working (FTP Plug0In stage) to transfer files to the MSSQL Server box but wonder if I can remotely call the bcp on that server.

I have previously used "Call DSExecute("UNIX", Command, Output, SystemReturnCode)" to break out to the operating system but how do I do this to a remote server.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 26, 2005 4:55 am

There will be some kind of "remote shell" or "remote execution" mechanism that you can employ. Not currently working where I can check for details. A search of this forum might turn up something!