March 2, 2006 at 5:40 am
I have 5 jobs set up using SQL Server Agent all of which run a standard bulk copy from a user table to a file. They all copy out different numbers of rows (67000 is the highest and 10 is the lowest), yet each of them takes 1 minute 20 seconds to run. I can't get them to run any faster. Does anyone know why this would be or if there is a way to speed things up?
Thanks,
Rob
March 2, 2006 at 7:14 am
Possibly something to do with the time taken to open/close a connection to the database?
March 2, 2006 at 8:17 am
I think that must be it. It takes exactly that long to run from command line aswell. I think I'm stuck with the problem.
Thanks for answering.
March 3, 2006 at 12:44 pm
Where are the input files ? On the server or a mapped drive ? If they are on a mapped drive then network latency may be hindering you a bit. But the times you have are really not that bad considering ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 3, 2006 at 12:59 pm
Look in BOL for the -h hint. This lets you specify how many records are uploaded at once.
I've just had to run a 220,000,000 record upload and I would be waiting for ever without specifying a batch size of 50,000. As it was it took around 30 minutes.
March 3, 2006 at 1:16 pm
If I'm reading the question correctly your complaint is that it's taking BCP 1 minute and 20 seconds to run regardless of the number of records?
A couple of things I can think of that might cause that type of behavior:
(1) You're using a select statement as your BCP source, all against the same user table and every query is causing a table scan of the source table - how long does it take to run the same select statement(s) via query analyzer?
(2) a name resolution problem - it's taking your computer a while to find the source server by name. Try pinging the source server from a command line, how long does it take for ping to find the server?
(3) is it possible that your source is really a view that is going out to a linked server?
Joe
March 6, 2006 at 3:21 am
Joe...you read me correctly.
The select statements take (at most) of a couple of seconds through query analyser. The jobs are being run on the local server so I doubt the problem is anything to do with points 2) or 3)
It does just seem that 1 minute 20 seconds is the best I can do for these jobs. It takes as long to run the bcp from command line, so it doesn't look like anything is wrong from a SQL perspective. It would be nice if the jobs ran faster but maybe that's unrealisitc. Is this definitely a slow response time?
Rob
March 6, 2006 at 3:32 am
I can answer that last question myself. It's definitely slow. Equivalent jobs on different servers take 1 second to run, however the bulk copy is also very fast from command line on those servers.
March 6, 2006 at 5:37 am
How do I close this as an issue? I think this problem is caused by the local installation of SQL Server. I'd noticed a while back that Query Analyser and Enterprise Manager were taking a long time to open, but I hadn't made a connection with this problem. I just timed how long it takes them to open. Lo and behold, it's 1 minutes 20 seconds. I still don't know what's causing it, but I don't want people taking their time to post answers to some obscure build problem I've got with this server.
Thanks for the help.
Rob
March 6, 2006 at 8:59 am
Try this experiment.
Switch the servername for the actual IP address of the server in the bcp commandline.
If time improves, you're probably having a nameresolution issue.
/Kenneth
March 6, 2006 at 9:10 am
Kenneth,
I'm running this locally, so I'm not using a computer name or an IP address.
Rob
March 6, 2006 at 9:23 pm
Rob -
Your BCP command should look something like bcp sourcedatabase..sourcetable (or query) out targetfilename -S hostname...
Try using various options for the hostname. Assuming you're running on the same machine as SQL try localhost... if running against another machine use the servers IP address instead of the hostname (e.g. x.x.x.x instead of server.domainname.local).
Joe
March 7, 2006 at 8:26 am
Could you post your bcp commandline?
If you're running locally, try to leave out the -S server parameter. (if you've not done so already)
In the 'old days' this was a trick to circumvent the redirector (server service) to speed things up.
/Kenneth
March 7, 2006 at 8:51 am
Everybody,
Thanks for the suggestions, but as I mentioned, you aren't going to fix this (not unless you pay a visit to my workplace). It isn't the bulk copy syntax or a name resolution problem. I have a problem with the build on this server and I hadn't noticed it when I posted the problem. Does anybody know how I let people know that there's no need to post any more replies?
Rob
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply