SYSK 36: The Cost of SQL EndPoints

Many of you already know that you can expose your data as a web service natively in SQL 2005.  No IIS.  There are plenty of great articles that talk about it; one of them is at http://www.developer.com/db/article.php/3390241.

There are a few articles and blogs that talk about when you should use SQL endpoints.  For example, check out When to Use HTTP/SOAP Endpoints section at the bottom of this article -- http://www.devx.com/dbzone/Article/28525/1954?pf=true.

But I found no information on the resource cost of SQL EndPoints.  So, I ran my own tests comprised of getting data from a simple small table with a hundred or so rows via SQL endpoints and by calling the stored proc directly, lazily using SQLDataAdapter.Fill method.  I found that the processor on the SQL box is highly utilized for a significantly longer period of time, when using endpoints.  Other server resources did not show a big difference.

#Threads      Web Service Performance          Stored Proc Performance
1                   24 ms/iteration                           3 ms/iter
5                   58 ms/iter                                   4 ms/iter
10                 109 ms/iter                                 5 ms/iter

Now, I know… it’s not quite an apple-to-apple comparison.  But the data led me to the following conclusion:  don’t use SQL endpoints unless you need web services without IIS; otherwise, just create a traditional web service using .NET, which calls the stored proc.  After all, scaling web/middle tier is easy, scaling SQL is not.