SQL Server Side Paging

Friday, February 29th, 2008

I was extremely excited about GridViews and DataGrids when I first started working with .NET. The thought of having controls with built in paging and sorting just blew me away. Needless to say, this excitement was short lived. By the end of my first project in .NET, I found that this built in sorting and paging had some major performance issues. It was too much data to send back and forth between the web server and the SQL server; not to mention that .NET just isn't as optimized to sort that much data as fast as SQL Server.

The alternative was to have SQL Server sort and page the data and only send back the data that was needed to the web server. After some research and some time to combine the ideas I found into my own solution I came up with:

 
CREATE PROCEDURE dbo.ReturnPage
(
  @SelectStmt varchar(1000), --Select Statement with Where Clause
  @OrderBy varchar(1000), --Order By Clause 'ASC' is required
  @StartRow int, --First Row of Page
  @EndRow int, --Last Row of Page
  @totRows int output --Output: Total Rows in Set
)
AS
 
DECLARE @ColList varchar(2000)
DECLARE @WhereCls varchar(2000)
DECLARE @i int
DECLARE @i2 int
DECLARE @tmp varchar(1000)
DECLARE @dec varchar(1000)
DECLARE @f varchar(100)
DECLARE @d varchar(100)
DECLARE @Symbol char(2)
DECLARE @SQL varchar(4000)
DECLARE @Sort varchar(1000)
DECLARE @tmpSQL NVARCHAR(4000)
 
--Assign the total number of rows available to @totRows for output
SET @SQL = N'SELECT @totRows = COUNT(*) FROM (' + @SelectStmt + ') tbl'
SET @tmpSQL = '' + @SQL
EXEC sp_executesql  @tmpSQL , N'@totRows int OUTPUT',
     @totRows = @totRows OUTPUT
 
SET @Sort = @OrderBy + ', '
SET @dec = ''
SET @WhereCls  = ''
SET @SQL = ''
 
SET @i = charindex(',' , @Sort)
 
--Pull apart the sort by clause for declarations
WHILE (@i != 0)
BEGIN
  SET @tmp = LEFT(@Sort,@i-1)
  SET @i2 = charindex(' ', @tmp)
  SET @f = ltrim(rtrim(LEFT(@tmp,@i2-1)))
  SET @d = ltrim(rtrim(substring(@tmp,@i2+1,100)))
  SET @Sort = rtrim(ltrim(substring(@Sort,@i+1,100)))
  SET @i = charindex(',', @Sort)
  SET @symbol = case when @d = 'ASC' then '>' else '< ' end +
          case when @i=0 then '=' else '' end
 
  SET @dec = @dec + 'declare @' + @f + ' sql_variant; '
  SET @ColList = isnull(REPLACE(REPLACE(@colList,'>','='),'< ','=') +
    ' and ','') + @f + @Symbol + ' @' + @f
  SET @WhereCls = @WhereCls + ' OR (' + @ColList + ') '
  SET @SQL = @SQL + ', @' + @f + '= ' + @f
 END
 
--Put together Transact SQL for final output
SET @SQL = @dec + ' ' +
  'SET ROWCOUNT ' + convert(varchar(10), @StartRow) + '; ' +
  'SELECT ' + substring(@SQL,3,7000) + ' from ('
  + @SelectStmt + ') a ORDER BY ' +  @OrderBy + '; ' +
  'SET ROWCOUNT ' +
  convert(varchar(10), 1 + @EndRow - @StartRow) + '; ' +
  'select * from (' + @SelectStmt + ') a WHERE ' +
  substring(@WhereCls,4,7000) + ' ORDER BY ' + @OrderBy +
  '; SET ROWCOUNT 0;'
 
EXEC(@SQL)
GO
 

What's it doing?

The procedure works by turning a simple query like:

 
SELECT col1, col2, col3, col4 FROM TABLE ORDER BY col1 ASC
 

and turning it into the following to add the paging.

 
DECLARE @col1 sql_variant
SET ROWCOUNT = @StartRow
SELECT @col1 = col1 FROM TABLE ORDER BY col1 ASC
SET ROWCOUNT = (@EndRow - @StartRow) + 1
SELECT col1, col2, col3, col4 FROM tbl WHERE col1 > @col1
  ORDER BY col1 ASC
 

Simple enough right?

Disclaimer

While this procedure does work with more complex queries containing SUB-SELECTS, JOINS and WHERE clauses, it does have its limitations. GROUP BY clauses do not work in this version. Maybe in the future.

I wouldn't suggest calling this procedure directly as that would mean you're building your query in-code, and probably not doing necessary escaping to prevent security issues. Creating a stored procedure that builds the original query from passed in WHERE and ORDER BY Parameters and then calls the ReturnPage procedure will be much more efficient and secure.

I hope to post a complete sample that will illustrate the changes to the .NET code and the wrapping procedure soon.

Posted in Development, Programming, SQL Server | Add a Comment »

Data Access and Business Logic

Thursday, August 16th, 2007

Since I've started web development, I've come across many ways to handle data access and business logic. Some of these have been good, many were bad, and some had potential but not for that particular project. I've decided I would bring up some of the better designs I've come across and discuss the benefits and problems I've experienced with each, and hopefully come up with a decent idea of when each should be used.

Ad Hoc

The basic premise of this is simple; put data access code and business logic anywhere that needs it. If you have a web page that allows the user to add, edit, and delete users, then put the code to access and handle adding, editing, and deleting users directly in the [server side] code of that page. The biggest benefit of this setup is flexibility. Since the code of every page has its own set of data handling procedures, you don't have to worry about how the changes to one part is going to affect the entire project. This also allows you to just get the data you need at the time, which can increase performance. However, with this, you'll often end up with the same code scattered across most of your files. This makes even minor changes a nightmare.

Benefits
flexibility, performance

Problems
code bloat, increased maintenance time/costs

When to Use
This should really only be used in a couple of situations, small read-only sites and working models. Small read-only sites are ones that don't really have any business logic to them. They basically spit out the same data no matter who the user is and what they do. Working models are the projects where you need to get a solution out there, but you don't yet know the problem set well enough to create a well designed system. So, to move forward, you create a working model of the final project until you have identified enough of the problem set to replace it with a viable solution. Choose wisely though, plans often change and you may end up having to maintain that working model longer than you planned.

Datasets And Business Objects

While these methods can become two very different things, I've grouped them together because they usually tend to end in the same result. The basic premise behind these is having one set of objects that handle accessing the data and another for managing that data after its been retrieved. This method can make front end coding very quick and easy as you can now call simple well defined functions like:

$customer = Customer::GetCustomer($id);
$orders = $customer->getOrders();

While this often works out very well for most of the front end development, making changes to handle any unforeseen business requirements (yes, these do change, often) can be like adding a bathroom to a finished house. Major changes like this will often need to start in the database and weave their way through the data and business layers. A lot of time for what the boss probably thinks is a minor change.
I've also run into an issue with performance using this method. Unless I've spent way too much time breaking down the business classes into the smallest data chunks I could think of, the program is almost always having to retrieve way more data than it actually needs. For instance, if there is a page that only needs to print out all the customer names who purchased Uncle John's Bathroom Reader, then either the "Person Name" would have to be handled as a separate business object from the rest of the customer class or the software is having to wastefully retrieve each customer's address, phone number, etc.

Benefits
easier front end development, lower coupling between database logic and application logic, easier minor business logic changes

Problems
wasted data transfer, major changes can be expensive, performance

When to Use
Most projects will work best with this design. Its compartmentalized logic means that most changes will be relatively simple. The compartmentalized logic also means the developers only need to know about one section of the project at a time, which works well for multi-developer projects where people may come and go. If performance is a major concern, consider spending a great deal of time in pre-development design.

Logic First

This is the latest one I've discovered, and so far, its gone well. The basic idea is to move all the business logic to the database itself. Not only will this lower the number of trips to the database to retrieve intermediate logic data, but it also makes sure that any other applications that use the same data have the same rules applied. Through the use of stored procedures, transactions, and views a single query can now verify the customer's credentials, input the order, and output the receipt info, all while being able to undo the entire thing in case of an error. With the business logic now contained within the database, data access can be setup in data managing classes, ad hoc, or both, leaving a lot of flexibility. This method is still relatively new to me, so I haven't found many problems yet other than the fact that from experience, most developers don't know enough about T-SQL to write all the needed business logic.

Benefits
flexible data transfer, easier front end development, localized business logic, performance

Problems
readability by most developers, debugging sql is much more difficult than other code, simple logic is more complex

When to Use
Since writing T-SQL can be much more difficult and wordy to write, I would leave this to medium to larger projects. With a lot web projects moving towards AJAX integration, I prefer this to the conventional dataset design, as this allows less calls back and forth between the code and database, giving a much more responsive user experience.

Posted in Programming | 4 Comments »