Swim Event Times

First some background.  This all came about since we have a new developer using all the latest Javascript front-end coding tools/practices and he is developing using Apache locally since he is not a Microsoft guy.  So until we could get him into Visual Studio he had gotten along using Apache to develop and even demo the application.

Low and behold, his development workstation running Apache is more than double the speed of IIS on a Windows 2008R2 server. He even has more network hops to get the data he requires than IIS since all the resources for IIS are located on the same subnet.

I couldn't figure out how and why Apache was working so much faster.

Here's the how the application works.  The Javascript front-end makes seven asynchronous calls to the web service to bring back all the data for the home page.  Now that we're moving to a stable IIS configured development platform I ported his code to C# and expected that the IIS calls would run the seven asynchronous calls just like Apache.  Well it doesn't

I had run the gamut on this – from IIS worker threads to all sorts of machine configuration and web configuration changes.  None of that works.

Finally the Network Guy to put a Wire Shark on and it revealed that Apache was opening up seven ports to complete the calls to the web service and IIS was only opening one port to communicate with the web service. Until the network guy revealed that this was happening I was completely stumped.

Basically IIS ended up blocking the results from returning since they are all on the same port.  IIS thinks that you’re intending to mess with the Session variable so IIS prevents a race condition.

All you have to do is put a page declarative in to make the Session State Read Only.  We do this on a subsequent (data getter) page after the initial Login page so only the Login uses/stores the session variable. 

All other pages just interrogate the variable and this permits IIS to open as many ports as necessary to satisfy the asynchronous calls.  You’ll probably never ever hit this but it’s good to know that IIS still can do its job.

Tags: IIS, Javascript, Session, Asynchronous

As the saying goes: It's always better to be late than never and that goes double for this technique for executing stored procedures. 

What I'm referring to is a post from Remus Rusan, who explains in detail how to be able to run stored procedures in an asynchronous fashion:

In the set of two articles, he explains how to take a stored procedure, even with parameters, and run it without holding a client connection.

This is extremely useful if you need the ability for users to run long running procedures. 

You may be asking yourself why not just create a SQL Agent job and run that from the a client which has the same effect since SQL Agent jobs run asynchronously.  Well that's true but this technique is more flexible since you can pass parameters from the client whereas in the SQL Agent jobs there is no facility for passing parameters directly to the job.

He includes a detailed script for installing his helper stored procedures and the procedure for handling the Service Broker.  Yes I said Service Broker.

Don't get overly concerned since to use the SQL Service Broker is quite easy in setup and operational usage.

I won't go into his solution since you've probably already read that but what I offer here is just some small modifications which make it a little bit nicer to use.

To get started all you need is to have the Service Broker enabled by the DBA:

ALTER DATABASE xxxxxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE xxxxxx SET ENABLE_BROKER;
ALTER DATABASE xxxxxx SET MULTI_USER;

Once this is done you're good to install all the Stored Procedures from Remus.

Check the DBA:

SELECT is_broker from sys.databases where name = 'xxxxxx';

One of the modification I made to the set of procedures was to be able to see the Start_Time of the running Stored Procedure.

In Remus's routines, all execution is within a Transaction which proves invaluable since you'll need the rollback capability in the case of failure.

But the Start_Time I don't think qualifies as a need for the rollback of the transaction plus I want to display the Start_Time to the users who requested the run of the procedure.

So all I did was include an execution of a CLR procedure to update the Start_Time.  Since CLR procedures execute outside the transaction it updates the Start_time immediately.

I made the change here in the AsyncExecActivated procedure:

begin try;
    receive top (1).......

if (@messageTypeName = N'DEFAULT'....

--CLR procedure
EXECUTE ExecuteSQLNoQuery

I also included a way to cancel a waiting procedure which is next in line to be handled by Service Broker execution.

It's crude but it allows you to stack up requests and act on the behalf of the user to cancel inadvertent requests.

Just update the error_message with anything (before it runs) for that task and it will effectively be cancelled.

select @error_message = error_message from dbo.tblTaskResults
      where [token] = @token;

--see if the request was canceled
if (@messageTypeName = N'Default' and @error_message is null)

I've put together an ASP.NET Page which allows users to initiate and cancel queued requests:

I've included my script for those who would like to build onto my recipe for Asynchronous Procedure Execution:

http://sdrv.ms/12r1d7C

I drive my whole web page and the Tasks available for execution based solely upon a SQL configuration table.

This technique frees the developer to add new tasks at anytime and no longer do you need to add more SQL Agent jobs.

Developers need time to drive into subject matter.  Heads down developing is a trap.

Tags: Service Broker, Stored Procedure, Asynchronous, SQL Server