Monday, 23 November 2009

Count Active Sessions in ASP.Net

In order to monitor load on a site I've built and support, I wanted to add a simple counter to show how many active sessions were currently being handled by the web application.

The first technique was to use a counter variable held in the Application context. This was pretty straightforward - in global.asax increment the counter in the Session_Start event and decrement in the Session_End:


  1   Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)


  2 


  3       'Increment session count


  4       Application.Lock()


  5       Application("SessionCount") += 1


  6       Application.UnLock()


  7 


  8   End Sub


  9 


   10   Sub Session_End(ByVal sender As Object, ByVal e As EventArgs)


   11 


   12       'Decrement session count


   13       Application.Lock()


   14       Application("SessionCount") = Application("SessionCount") - 1


   15       Application.UnLock()


   16 


   17   End Sub




Unfortunately this didn't turn out to be accurate in my case as I was using out of process session state, using State Server. This is a method of using an external service for the management of sessions, which is often a good idea as it means that user's session information will survive application restarts caused by uploading new .dlls or modifying the web.config file.

So instead made use of a performance counter, reading it's value in code for display in a web page:


  1   Private Sub DisplaySessionCount()


  2       Dim pc As PerformanceCounter


  3       Dim dblSessions As Double = 0


  4       If PerformanceCounterCategory.CounterExists("State Server Sessions Active", "ASP.NET") Then


  5           pc = New PerformanceCounter("ASP.NET", "State Server Sessions Active")


  6           dblSessions = pc.NextValue()


  7       End If


  8       lblSessionCount.Text = dblSessions.ToString()


  9   End Sub




There's a gotcha in that as it stands this will lead to a security exception. To avoid you need to add the website account (e.g. IUSR_MACHINENAME if IIS anonymous access and identity impersonate are being used), to the Performance Monitor and Perfomance Log groups under Computer Management > Local Users and Groups.

Tuesday, 17 November 2009

Paging with SQL Server and MySQL

Paging sets of database records in a web application efficiently has long been a relatively tricky undertaking. Whilst web form controls such as the GridView offer control based paging very easily, they will still hit the database for all the records before processing the paging within the web application, and hence unnecessary amounts of data are passed between database and web server.

Database based techniques - only retrieving the particular rows you require in each request - are therefore generally to be preferred. But some of those provide their own headaches - for example as well as retrieving the particular set of rows for a page, you will often want to know what the full count of records to be able to display a message along the lines of: 200 records found. Page 1 of 20.

SQL Server Paging

Prior to the 2005 version, I would do this using SQL Server with two queries - one to get the page of data and another, using the same WHERE clause, to get the count. With SQL Server 2005 though, you can get both with a single query, using the following syntax:

CREATE PROCEDURE getProducts
@page int,
@recordsPerPage int
AS
WITH Results AS (
SELECT ROW_NUMBER() OVER ( ORDER BY Name ) RowNumber,
ID, Name
FROM Products
)
SELECT ID,Name,
(SELECT Count(*) FROM Results) TotalRows
FROM Results
WHERE @page = 0 OR @recordsPerPage = 0 OR RowNumber BETWEEN @recordsPerPage * (@page - 1) AND @recordsPerPage * @page
ORDER BY Name

With this stored procedure, passing 0 for the page number or the records per page simply returns all of them, but with non-zero values passed only the required page of data is returned. In addition, every row will have an additional column TotalRows that returns the full count of the query.

MySQL Server Paging

On a recent project we had need to port an SQL server database to MySQL, at which point we discovered this is one of the features that isn't so easy to translate. MySQL supports the LIMIT clause in SELECT statements. It also supports a clause called SQL_CALC_FOUND_ROW which returns the total number of records returned irrespective of the LIMIT clause. These two together can be used as the basis of a similar paging technique to that described above.

The crux of the problem though is that MySQL doesn't support the use of parameters in the LIMIT clause in functions (stored procedures).

Instead you have to make use of prepared statements and execute two of them within the function to have the same result as the SQL server version. An example follows:

CREATE PROCEDURE `getProducts`(page INT,@recordsPerPage INT)
BEGIN

SET @sql = CONCAT(
"SELECT SQL_CALC_FOUND_ROWS ID,Name
FROM products
LIMIT ", page,",",@recordsPerPage,"; "
);

set @sql2 = "SELECT found_rows();";

prepare stmt from @sql;
execute stmt;

prepare stmt from @sql2;
execute stmt;

drop prepare stmt;

END;

In this case, rather than having one result set with an additional column holding the total number of rows, there will be a second result set containing just a single field where the total record count can be extracted.

In .Net the IDataReader method NextResult can be used to access this additional recordset.