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 |
|
paul.koanui
Starting Member
2 Posts |
 Posted - 2007-10-23 : 06:56:26
|
| I have the need to have a client application connect to a remote database server for retrieval of large datasets (~13000 rows at time) and am trying to leverage existing technologies to build my solution. To test the difference in retrieval times I created 2 test apps - testA and testB. I created an HTTP endpoint in SQL Server (2005) that exposes one web method corresponding to a sproc that executes one select query and configured testA to execute query and bind the results to a data grid. I exposed my SQL Server so that I could connect directly to the SQL Server using a connection string and configured testB to execute the same SQL statement in an ad-hoc fashion, binding the results to a data grid.On average, the HTTP endpoint would take ~ 34 seconds to return the 13,000 row data set whereas the direct connection returned that same result set in 4 seconds or less.I have a bunch of questions:* Why would I be seeing such a dramatic difference in the retrieval times? * How is SQL Server communicating when there is a direct connection?* What are the immediate disadvantages to connecting directly to the remote SQL Server? * If I were to consider allowing my client application to communicate directly with the SQL Server, is there any way to secure the communication without VPN? * In addition, is it even possible to create a SQL login that has all of these requirements?** select and update only** cannot view schema (cannot connect to the SQL Server using SQL Server Management Studio)** cannot view any system tables** cannot retrieve any information about the schema of the database whatsoeverI guess I'm wondering if there is a way to overcome the obvious serialization overhead that comes with the HTTP endpoint to get the speed of the direct connection while being secure at the same time. Is that being greedy? :PAnyhow, what are your opinions? I'm very interested to hear experiences and/or advice.Thanks,Paul |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-23 : 07:06:06
|
| 1. because the http endpoint returns data in xml which is more data and is slower.2. via tcp using TDS (tabular data stream) protocol3. none (maybe you should explain this in more detail what you mean)4. of course. sql connection can be properly secured with the use of windows or sql authentication5. yes_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
|
|
|
Why is my SQL HTTP Endpoint so slow?