1

I have a reporting page which builds a report from a database table. The report is displayed in a tabular format. To handle user interactions I have built a JQuery table plugin which allows a user to customize their report with custom sorting, filtering and paging.

The JQuery plugin uses a set of ajax requests to populate the table with data. These ajax requests call asp.net (c#) webMethods which in-turn uses a set of classes to build SQL and execute the Query. The Queries built are run in the same thread as the WebMethod this causes a problem because the SQL can take in excess of 30 seconds to return. I have been reading about starting new threads and the asynchronous approach. My problem is I obviously don't want to hold a thread from the ASP.NET pool for the entire execution of the SQL as it is only waiting for I/O.

Is it possible to return the thread to the threadpool while waiting for the response from the SQL Server? Can this be achieved using the Begin and End ADO.net commands?

The reason I ask is at the moment this is not a problem but as the number of users using this system increases all the threadpool serving new users will be saturated running these long queries.

Westy10101
  • 861
  • 2
  • 12
  • 25
  • Unfortunately, if the WebMethod needs to respond with the data that is a result of the query you have no real option. Do you really expect 1,000+ users to run the same report simultaneously? If the answer is yes, and the parameters of the report don't change, why not just cache the result temporarily? Otherwise you may need to look at a design change -- submit a "request" to run the report, have a SQL Agent job running to process "requests" into a result table, and have the client query a method to check the status of the "request". – wgraham May 23 '13 at 13:12
  • I'm no expert so I'll just post a comment, here is the docs for getting the web method part asych- http://msdn.microsoft.com/en-us/library/aa480516.aspx I've read (suspected) that for async to work, everything in the stack needs to support async, or else you get to a level that runs synchronously and hogs a thread anyhow. So the asych web method should call an asych ado.net command. – MatthewMartin May 23 '13 at 13:13
  • @MatthewMartin you'd still see the ThreadPool being "eaten". – wgraham May 23 '13 at 13:14
  • Thanks. Its unlikely I will get 1000+ users running a report at once because the SQL server would likely be the bottleneck in that situation. The problem is the tables can contain in excess of 20 million rows of data so caching isnt feasible. I may have to look at having a temporary results table using SQL Agent job. – Westy10101 May 23 '13 at 13:30
  • @wgraham yes, some threadpool is going to be used, but my naive reading of articles on asych asp.net is that the goal is to free up the work process threads that accept new connections, which are limited. The rest of the threads on the machine are not limited. I don't see where the 1000 number is coming from, from this doc http://msdn.microsoft.com/en-us/library/ee377050.aspx it seems the limit for simultaneous req (w/o async) is far lower 12 per cpu – MatthewMartin May 23 '13 at 14:53
  • 1
    @MatthewMartin the number of threads available in the ThreadPool varies by processor and available virtual memory -- the 1,000 number didn't come from anywhere specific. Yes, it is true that IIS can further impose a limit on this, but regardless of using async or not, if the WebMethod does not return to the caller until the result is generated you will block the request (and therefore hold on to a request thread); async does not fix this. The only option I see in this case is to offload the generation of the report elsewhere and have the web browser poll for completion. – wgraham May 23 '13 at 16:39

1 Answers1

0

Can this be achieved using the Begin and End ADO.net commands?

Exactly!

sh1ng
  • 2,808
  • 4
  • 24
  • 38