Andy Butland

This week I've been looking into some optimisations for a long-running process that chats to the database. Within the operation, the data isn't expected to change, so it would be reasonable that, where we make repeated calls, we cache the values and re-use them for the duration of the operation. I've been using SQL Profiler to identify them and wanted to blog the method for reference. First, start up SQL Profiler and create a new trace. Connect to the database and define the Events Selection. I kept most things related to data reads and writes: I then ran the application, set a break-point in the code at the point where afterward I was interested in the results, and attached the debugger. On hitting the breakpoint, I started the trace and continued the code execution. When the operation was complete I could stop the trace. To analyse the data, I saved it to a new table in a database I created to store the results in, via File > Save As... > Trace Table ...

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 Serve...