Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DaveD
Starting Member
8 Posts |
 Posted - 2003-04-18 : 15:36:19
|
| Hi all,Why would my SQL 2000 Server answer local queries promptly but remote queries at a snale's pace? I'm talking about a one-second query taking over a minute from any remote client, either inside or outside of the local area network. I have confirmed to the best of my abilities that hardware resources and bandwidth are plentiful. Here are some STATISTICS TIME results from Query Analyzer:QUERY RESPONSE TIME AT SQL SERVERSQL Server Execution Times: CPU time = 297 ms, elapsed time = 1306 ms.QUERY RESPONSE TIME AT REMOTE WORKSTATIONSQL Server Execution Times: CPU time = 313 ms, elapsed time = 106651 ms.What should I look at?Any help is GREATLY appreciated.Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-18 : 15:42:27
|
| Well it depends on how you are retrieving the data. Is it from a stored procedure (doubtful though considering your problem)? If from a stored procedure, the query times should be rather similar. If not from a stored procedure, then that is probably your problem. When you do not use a stored procedure, the code executes on the local machine. The speedy of the query when not from a stored procedure will depend on the client's hardware. The server is so fast because when you run the query on the server, it is using the server's hardware. When run from a client machine, it is using the client's hardware. This does not apply to stored procedures. Stored procedures execute on the server.Suggestion: Move your T-SQL code into stored procedures, else upgrade the clients.Could you post the query so that we can help optimize it without having to upgrade the clients?Tara |
 |
|
|
DaveD
Starting Member
8 Posts |
Posted - 2003-04-19 : 17:03:27
|
| I don't believe that stored procedures can account for a 10000% performance hit. Stored procedures are efficient only because the query itself is pre-compiled. That difference cannot account for the 1-sec. vs. 100-sec. discrepency I am experiencing. Do you mean to suggest that when stored procedures are NOT used, that the contents of the database are transferred to the client for processing? This would be news to me (though it would account for the outrageous delay). Then again, some other queries of seemingly equal or greater complexity against this same database do not exhibit the same delays. I cannot explain it.Here is my test query:SELECT *FROM Table1JOIN Table2 ON Table1.Field1 = Table2.Field1WHERE (Field7 = 'LAX')Thank you very much for your input. Any other ideas? Am I incorrect in my assumptions?Dave |
|
|
|
DaveD
Starting Member
8 Posts |
Posted - 2003-04-19 : 21:34:26
|
| Woops! I only just noticed the SIZE of the result set. It turns out that my test query was returning some 7 megabytes of data. Though I'm not the developer, I feel more than a little foolish.[Note to self: Don't judge the size of a table by the number of fields.]Again, thank you for your help.Dave |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-21 : 16:22:27
|
quote:
Note to self: Don't judge the size of a table by the number of fields.
Yeah, I would say that's good advice.Ever hear of sp_spaceused?Brett8-) |
|
|
|
|
|
|
|
|
Network Problem?