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.
MSSQL Performance Problem
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.