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