Stored Procedure vs. ISQL

[DecodeThis]

Can someone please shed some light on this matter for me? Let me first say that I am a network engineer not a DBA. When I run a SP on one MS SQL server that accesses another SQL server the entire process takes about 14 seconds. If I run the same procedure in a query it takes 1 second. The developer is trying to blame the network. I believe it is the SP. Any suggestions would be great.

 

[nigelrivett]

How much data is being transferred?
If it's a lot it can easily be the network.

If the query is actally joining two tables across servers then it's not surprising that it can take a long time and should probably be redesigned.
To do the join it has to move the data to one of the servers - if they are not collaion compatible then it will probably transfer all the data in the relevent columns to the local server to fulfill the join.

Have a look at the query plan to see wht's happenning.

You can use the profiler to see what is happenning on the remote server.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.