Schubert's Blog

A biker and technology enthusiast's
view of the world

TSQL optimzation tricks

A question at the back of most System Architects and Enterprise Application Developer's mind is; will my software or website cope with the number of requests as it's user base grows?

One way I've optimized Mapze.com is by use NOLOCK in my select queries. Using NOLOCK politely asks MSSQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. However, using NOLOCK also means the code is reading data that might be partially written or in the process of being updated. On Mapze.com since the data is not critical, the user will see event data that is out of sync with the information in the database. If this were a banking or financial system, I would recommend using page or row level locks, depend on the number of rows being read and written.

Another optimization trick I've used is to page the data on the database server and only return page size of rows back, which can be 10, 20 or 50 rows. This not only reduces network bandwith between the web and database server, it also reduces stress and memory usage on the web server. This way the web server does not need to process large quanities of data for each database request and land up using only 10 or 20 rows of the 100 or so that get returned. Although it does introduce some stress on the database server and this subject is not a well covered subject on the internet, so tread carefully.

This is how most of Mapze.com Beta 2's search queries look like:

ALTER PROCEDURE [dbo].[ListEventsByKeywords] 
	-- Add the parameters for the stored procedure here
	@Keywords nvarchar(100) = '',
	@Year int,
	@Month int,
	@Day int,
	@PageNo int,
	@PageSize int
AS
BEGIN
	SELECT [t1].*
	FROM (
		SELECT ROW_NUMBER() OVER (ORDER BY el.[Priority] 
desc, el.[Id]) AS [ROW_NUMBER], el.*
  FROM Events el WITH(NOLOCK) Where el.IsDeleted = 0 AND el.IsSuspended = 0 AND el.Status = 1 AND DATEPART(Year,el.StartTime) = @Year AND DATEPART(Month,el.StartTime) = @Month AND DATEPART(Day,el.StartTime) = @Day ) AS [t1] WHERE [t1].[ROW_NUMBER] BETWEEN (@PageNo * @PageSize) + 1
AND (@PageNo * @PageSize) + @PageSize ORDER BY [t1].[ROW_NUMBER] END END

I would like to hear the opinion of database programmers about the above tsql and how they go about returning only a subset of required rows, instead of returning 100+ result rows and leaving the web developer with the task of only showing 10 or 20 at a time to the user.

By Schubert on 05 December 2010 14:43

Categories: Database | Tags: ,

Permalink | Comments (0)

Submit to DotNetKicks...

Mapze.com Beta 1

I wanted a challenge, a website to work on from ideas and concepts, all the way to deploying the final product on an internet accessible server and getting public feedback.

The solution: Mapze.com

Mapze.com is an event sharing website, to keep it short and simple. Although, its much more than that. The first Beta focused on bikers like myself. Besides the usual register, update my details and update my password page, the user should be able to plan a route online using Google Maps and upload a route in format like GPX, ITN, CSV, etc. They should also be able to download the routes in various GPS friendly formats (at least the well know devices) and share their planned rideout dates, details and route experiences in one central location. All very interesting, as I've never developed any of the above from scratch or seen anything like it on the web.

Technologies of choice: having worked on ASP.Net for over 3 year now, I wanted the developement process to be a challenge too. The ASP.Net MVC web framework being new and promising, served the purpose well. It allowed for cleaner SEO friendly urls and gave me full control over the HTML, prefect for learning some CSS at the same time. Having more exposure to the C# language over VB.Net, I decided to use C# 3.0 and taking advantage of the new LINQ To SQL ORM tool for the database access layer. Since Mapze.com needed to save GPS locations and needed to do searches and calculation based on global co-ordinate, like find rideouts planned within 5 miles of London, I decided to take advantage of the new spartial data types and function in MSSQL 2008.

The above technologies formed the base of Mapze Beta 1. The other technologies involved were Google Maps, so users could plan and amend their routes online. JQuery and Javascript to handle AJAX calls to google geo services and presenting them on the map. XML Parsing for reading GPX (XML) files. NUnit and Rhino mocks to follow the TDD style of progamming. MSSQL Full text indexing to search through event keywords. .Net Multilingual support, to make the website accessible in various language. Elmah and Log4Net to gracefully handle any error and warning on the website. DiscountASP.Net for hosting the website. And finally RapidSSL for the SSL certificate, for when users login and update their password.

Like most project I've worked on for clients and personal ones, I came away with some interesting lesson and a revised list of Do's and Don't's for future projects. These are a few lesson learnt from the first Beta:

  • Setting up SSL on IIS was a lot easier than I had initally though
  • CSS is a powerful display formatting language
  • Route formats are inconsistant, GPX being the most flexible and feature rich format
  • the .net build in Zipping classes are good, but ... library is better

Taking on the full software development life-cycle for even a simple concept website like Mapze.com, definitely involved a lot of creativity, architectural and scability considerations ... and a lot of hard work.

Screen shots of the final product:

Mapze.com Beta 2 is live! Click here for more ...