Best practice and/or ideas on how to do backend reporting

[szlamany]

I've got a web app - all ajax - maintenance with slickgrids and now I'm adding reporting.

The reports are sprocs that I "find" the parameters of - using an ajax call - and load these into a slickgrid. User fills in the column in that slickgrid basically putting in the selections for each parameter.

I've used this concept in VB6 for a decade - the image attached below shows the old VB6 app - and what I've got so far for the jQuery slickgrid below that...

At any rate - when the user clicks the PRINTER icon in the jQuery page it will ajax submit a request to a web service that will run the old VB6 app with a .BAT file (I already support this in the old VB6 app so my clients can automate nightly reports and what not).

The .BAT file on the server will produce output to a PDF print driver that we have installed...

So - my questions...

I guess I will respond to the initial AJAX request to start the report - which is starting the .bat file running - with some kind of SUCCESS message?

I guess I need to "identify" the "report submit session" somehow from the server - maybe a GUID?

How do I tell the web client when the report is done? Some reports run fast (moments) - but some can take 5 or 10 minutes.

Do I keep submitting AJAX requests for "status" updates? Use a javascript timer to do this?

I'm looking for any ideas or methods you all have used in this area.

Thanks a lot - in advance!!

-----

[szlamany]

Is an AJAX response to a request the only way to notify a web page of a status change on the server?

Can the server tell a web user something in some other fashion??

This is all related to this issue posted in the .Net area of the forum

http://www.vbforums.com/showthread.php?t=671573

[penagate]

Two methods:
1. WebSockets — requires HTML5 support
2. A pseudo-persistent connection ('Comet' pattern). You open a request to a script on the server which polls for status updates. During polling, no data is sent back; when data is available, the server sends the response. The client can then perform the request again to wait for any further data.

[szlamany]

What would be the downside of making a POST for a web request that might take 4 or 5 minutes to come back?

They could close the browser - I don't really care - but what happens to the pending request?? The response just gets sent into the ether??

Does having a lot of "long waiting" web requests take a toll on the IIS process on the server?

I'm not ever sure how to "pause" the web service to have it wait for a status change.

Is it better to respond quickly with a "report still running" type of status - and then have the web page make another POST - on like a javascript timer?

What would be the least burdensome overall??

[penagate]

Open connections do load the server a little. Depending on the server, they create either processes or threads. In IIS, I believe there is a pool of worker processes, and within them a pool of worker threads. It's very difficult to say how many of these would cause an issue, since it depends on so many things, such as hardware and other processes running in the OS, but you shouldn't see any adverse effects until you have several hundred concurrent connections at least.

4–5 minutes is far too long; you'll probably get about 25 seconds before you need to flush and reopen the connection (assuming you're going for the Comet model). The average browser will give up after about 30 seconds, and IIS (in a production configuration) caps scripts to about 90 seconds, I think, although that's configurable. In either case you can trap the timeout condition on the client and reopen the request.

The web server will handle any dropped requests; they happen all the time.

On the server side, when you get the request, perform some sort of loop which polls for a status change, and exits (sending the response) when one happens. Make sure you allow for a pause on each iteration so you're not going full throttle all the time. The duration of the pause determines how close to real-time the user perceives the interaction to be.

Yes, you can loop and poll on the client side, but that creates network traffic, which is far more expensive than CPU time on the server. Or, to put it another way, there is a comparatively very small amount of bandwidth available, and a very large amount of free CPU time.

[szlamany]

Thanks for the info - could you expand on the COMET techniques - lots of info on the web but it's all over the place...

I've got a table that stores when each REPORT is started and when it completes - I'll be able to use that at each customer site to fine tune the experience as we learn from usage.

If most reports complete under a minute then a simple AJAX post - with a little spinner animation - waiting for the status to go to COMPLETE - should be ok - right?

I'll have to see how many "long" running reports we have. Some reports embed JPG logo's onto the page - and PDF drivers can take a bit of time to absorb that kind of output.

I've seen report cards at my bigger high schools take 4 minutes to complete - that's a long time.

If I see the initial request reaches the 60 second moment - for instance - and decide to return a status of "STILL RUNNING" - could I then do AJAX POST's for status changes every 15 seconds, for instance. That would mean about 16 requests for a 4 minute report to determine that it finished.

Is 16 POST's over 4 minutes eating up too much bandwidth in your opinion?

It would only happen for a report that exceeded the initial 60 second POST...

[penagate]

could you expand on the COMET techniques

Not much more to it than what I described. A typical Comet flow goes like this:

— User requests page; server responds with page and JavaScript
— Client makes GET request to a poll script on the server
— Server queries for new data; if there's any, it responds immediately
— If no data, then enter a loop, pausing for some time (about 250ms is a good compromise between CPU load and snappy interaction) then querying for new data
— If data is available, send response to client; if page time has exceeded some maximum, send a response of "No data" which prompts the client to start the process again; else continue the loop.

This model works well where there is constant — but not regular — interaction between the client and server: for example, a chat application, or a real-time (or close to real-time) data viewer. Apart from WebSockets, which are a relatively modern idea, it's the only method of pushing data from the server to the client, as opposed to having the client pulling data from the server.

In your situation, it might be overkill, given that:

I've seen report cards at my bigger high schools take 4 minutes to complete - that's a long time.
[...]
Is 16 POST's over 4 minutes eating up too much bandwidth in your opinion?

No — that's a much longer timeframe than I was imagining when I made the comparison between bandwidth and CPU loading. So perhaps forget the Comet stuff and stick with a poll from the client on an interval.

[szlamany]

How is this for server side code - is the DateTime.Now.Ticks loop for pausing ok?? I saw lots of info on the web that indicated that Thread.Sleep and what not were not good for IIS apps...

[edit]I might change it up so the MAIN loop is in the OPEN CONNECTION to the DB - no reason to loop an open/close DB connection - that seems expensive and useless...[/edit]

If ctrloption = "reportstatus" Then
    Try
        Dim maxloop As Integer = 20
        Dim curloop As Integer = 0
        Do While strMessage <> "C" And curloop < maxloop
            Using dcn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("LocalSQLServerAWC").ToString)
                Using cmd As New SqlCommand
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.CommandText = "dbo.awc_ReportGetStatus"
                    cmd.Connection = dcn
                    cmd.CommandTimeout = 0
                    DetermineParameters(dcn, cmd)
                    cmd.Parameters("@RptId").Value = ctrlval1
                    dcn.Open()
                    strMessage = cmd.ExecuteScalar().ToString
                End Using
            End Using
            If strMessage <> "C" Then
                curloop += 1
                Dim curtime As Long = DateTime.Now.Ticks
                Do While DateTime.Now.Ticks < curtime + (10000 * 250) '250)

                Loop
            End If
        Loop
        .NewObject("reportstatus", "true")
        .Seperate()
        .NewObject("rptid", ctrlval1)
        .Seperate()
    Catch ex As Exception
        strSuccess = ""
        strMessage = ex.Message.Replace("""", "'").Replace("\", "\\")
    End Try
End If

And the jQuery does this - the initial post for status...

var objReturn = $.parseJSON(msg.d);
var rptid = objReturn.rptid || "";
var queued = (objReturn.queued || "N") == "Y" ? true : false;
if (rptid.length != 0) {
    if (queued) {
        $(sender).find('.acs-report-status').html('Report Status: Waiting in queue...<img src="Images/ajax-loader-small.gif" />');
    } else {
        $(sender).find('.acs-report-status').html('Report Status: Started and running...<img src="Images/ajax-loader-small.gif" />');
    }
    var t = setTimeout("ctrlWebService('reportstatus', '" + rptid + "','','" + $(sender).attr('id') + "')", 1000);
    //ctrlWebService('reportstatus', rptid,'',sender);
}

And in the callback after the initial POST for status

if (objReturn.message == "C") {
    $("#" + strWho).find(".acs-report-status").html("Report Status: Completed");
    $("#" + strWho).closest('.acs-ddreflector').removeClass("acs-report-running");
} else {
    var t = setTimeout("ctrlWebService('reportstatus', '" + objReturn.rptid + "','','" + strWho + "')", 1000);
}

This is in the call back from the web service call...

[penagate]

I don't think I'm quite comfortable with not having some kind of sleep call in there somewhere...

I read on StackOverflow that the CPU loading comes from spawning new worker threads, rather than the actual sleep call. Is this what you saw too? Presumably you could increase the thread pool size to deal with this if it became a problem. I know you're developing an intranet application so you must have a good idea of the maximum number of connections you're likely to have.

[penagate]

Nitpick: I prefer avoiding eval'd code wherever possible, so I'd change the setTimeout call to use a closure:

var t = setTimeout(function(r, i) {
  return function() {
    ctrlWebService('reportstatus', r, i);
  }
}(rptid, $(sender).attr('id')), 1000);

(hope I got the identifiers correct...)

[szlamany]

Would you just use Thread.Sleep?? I put that silly loop waiting for the .Ticks to go past 250 ms...

Thanks for the closure assist on that setTimeout - I love closure but was struggling to understand how to handle it in the setTimeout!

[penagate]

Would you just use Thread.Sleep??

Yeah, I think so. I think the loop on its own would spike CPU load.

[szlamany]

btw - this app is not just for LAN use - one of the pushes to migrate to web (from winform) was to allow easier access outside the office. Most of my customers have dozens of users - one school district already has around 100 teachers hitting this app. I do have school districts with 1000+ teachers - if we ever put this app at that customer site scalabilty will be a big issue...

At any rate - Thread.Sleep() is considered evil in this blog

http://msmvps.com/blogs/peterritchie...d-program.aspx

Seems to be valid points - I like these two...

# re: Thread.Sleep is a sign of a poorly designed program.

I primarily write websites using ASP.Net (C#). Recently I have come across the need to write a windows service that polls a SQL Server Table and does something based on the table values. If the process is running then I do not want to polls the SQL Server table until the process is complete. Base on examples of logic I have found pertaining to creating this service, I was lead here. My problem is that everything I have found so far suggests using Thread.Sleep to accomplish my goal. If this is a bad design what would be a good design for periodically polling a SQL Server table and pausing the polling to do something with the data when the appropriate values are found?

# re: Thread.Sleep is a sign of a poorly designed program.
Everything you've found so far suggests using Thread.Sleep? Scary.

If you want to periodically perform some logic I recommend using a System.Timers.Timer object. The System.Timers.Timer.Elapsed event handler would be the place to put the code to query the table. You can pause the timer while you're doing your processing with the System.Timers.Timer.Enabled property. Alternatively you could use System.Timers.Timer.Stop to pause the timer and System.Timers.Timer.Start to restart it (you're intentions are a little more clear using Stop/Start).

Should I put this looping code into a TIMER instead???

Are TIMER's ok in ASP.Net web services...

[penagate]

Yes, I would suggest a timer for a desktop app or service, as I think that quote concerns.

The reason I suggested a sleep call in this case is because the lifetime of a web request is a little different: the thread is spawned (or rather, activated) in response to an incoming HTTP request, and becomes dormant again once the response is served. In order to put off sending the response back to the client, we need to block the thread in between polling the database. A timer wouldn't be appropriate, because by nature they do not block execution and so the HTTP worker thread would expire.

I admit my knowledge of IIS/ASP.NET is lacking a little; my advice comes from an Apache/PHP perspective, but I believe the execution models to be very similar. Someone in the ASP.NET section (perhaps Gary or John) might be able to suggest a better solution than Thread.Sleep.