Programmer Helper

Friday, August 25, 2006

Efficient Method for Paging Through Large Result Sets in MS SQL Server 2000

Introduction
It is amazing the amount of cycles, hardware and brain ware, go into paging results efficiently. Recently Scott Mitchell authored an article titled Efficiently Paging Through Large Result Sets in SQL Server 2000 that looked at a stored procedure that returned a particular "page" of data from a table. After examining Scott's approach, I saw some potential improvements in his method. (If you haven't yet perused Scott's technique, take a moment to do so before continuing here, as this article builds upon Scott's example.)

Scott's approach made use of a table variable to generate a synthetic ID to act as a row counter. Every time a page is requested, all of the data in the table being paged must be read and inserted into the table variable in order to generate the synthetic ID, at which point a SELECTROWCOUNT to greatly reduce the number of records that must be read and inserted into the table variable. statement returns just those records whose IDs fall within the desired range. While Scott's method is faster than blindly returning all of the records, his approach can be greatly improved by using

In this article we'll look at two ways to improve Scott's method. The first approach uses a table variable (just like Scott's), but utilizes the SET ROWCOUNT command to reduce the number of records read and inserted into the table variable. The second technique more cleverly uses SET ROWCOUNT to provide an even more efficient approach than the first. Read on to learn more!

Using ROWCOUNT to Optimize Paging
The first step we can take to optimize paging is to use SET ROWCOUNT prior to filling our table variable. SET options alter the current sessions handling of specific behavior; SET ROWCOUNT tells SQL Server to stop processing query results after it has processed the specified number of rows. For more background on SET ROWCOUNT, refer to Retrieving the First N Records from a SQL Query.

This particular stored procedure example was created by 4Guys reader Dave Griffiths.

CREATE PROCEDURE usp_PagedResults_New_Rowcount
(
  @startRowIndex int,
  @maximumRows int
)
AS

DECLARE @TempItems TABLE
(
  ID int IDENTITY,
  EmployeeID int
)

DECLARE @maxRow int

-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

SET @maxRow = (@startRowIndex + @maximumRows) - 1

SET ROWCOUNT @maxRow

INSERT INTO @TempItems (EmployeeID)
SELECT EmployeeID
FROM Employees
ORDER BY EmployeeID

SET ROWCOUNT @maximumRows

SELECT e.*, d.[Name] as DepartmentName
FROM @TempItems t
  INNER JOIN Employees e ON
      e.EmployeeID = t.EmployeeID
  INNER JOIN Departments d ON
      d.DepartmentID = e.DepartmentID
WHERE ID >= @startRowIndex

SET ROWCOUNT 0

GO

As you can see, this stored procedure uses SET ROWCOUNT prior to filling the table variable with what is already known will be the last row needed to satisfy the current page. SQL Server stops filling the table once it processes this number of rows, minimizing the amount of data pulled into the table variable. This method rocks for the first several pages and only begins to hit SQL Server resources as we get incrementally deeper into our pages. Last but very important, SET ROWCOUNT 0. This turns off row limitation and puts the current session in the default behavior mode in case the caller is doing anything else interesting with the same connection that may require more rows returned.

Taking Advantage of the SQL Server Optimizer
An optimizer trick that can also be used in this scenario is when a single variable is set to a potential list of values, it will get assigned the value of the last item in the list. For example, the following SQL script simply creates and fills a table variable with 100 records (1 through 100), then selects the value of the val column into a local variable from the entire table using two different sorts:

DECLARE @tmp TABLE(
val int
)

DECLARE @i int, @cnt int, @res int
SELECT @i = 0, @cnt = 100
WHILE @i < @cnt
BEGIN 
SELECT @i = @i + 1 
INSERT INTO @tmp VALUES(@i)  
end 
SELECT @res = val FROM @tmp ORDER BY val ASC
SELECT @res [Value], 'ASC Sort' [Sort] 
SELECT @res = val FROM @tmp ORDER BY val DESC
SELECT @res [Value], 'DESC Sort' [Sort]

The results from these selects follow:

Value Sort
----- ----
100 ASC Sort

Value Sort
----- ----
1 DESC Sort

While one might think that SQL Server will need to read every record from the @tmp table and assign the val column to @res for each such record, looking at the query plan for the statement it becomes obvious that the SQL Server optimizer knows that it will only ever need a single row to complete the query and is able to just read in that particular record. Examining this operation in SQL Profiler you'll find that the optimizer is able to get to the end result in only six reads (as opposed to the 100+ reads that would be necessary if it was reading every single record in the table). In short, when presented with such a query SQL Server doesn't actually get all of the records from the table, one at a time, and assign them to the local variable. Rather, it searches just for the last record in the query and assigns that result to the variable.

So, how does this little trick help in the problem of paging large result sets? If this knowledge is combined with SET ROWCOUNT, large result sets can be efficiently paged without the need for temporary tables or table variables! Here is another, more efficient version of Scott and David's stored procedures:

CREATE  PROCEDURE [dbo].[usp_PageResults_NAI]
(
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @startRow int

-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
  e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO

Using optimizer knowledge and SET ROWCOUNT, the first EmployeeID in the page that is requested is stored in a local variable for a starting point. Next, SET ROWCOUNT to the maximum number of records that is requested in @maximumRows. This allows paging the result set in a much more efficient manner. Using this method also takes advantage of pre-existing indexes on the table as it goes directly to the base table and not to a locally created table.

Using an even more highly unscientific comparison method than Scott's, let's see the results:

	Rows in Table	Page #	CPU	Reads	Duration
Scott's Approach (Table Variable w/o `SET ROWCOUNT`)	50,000	1	407	51,469	438
	50,000	10	375	51,469	422
	50,000	100	406	51,469	485
	50,000	1,000	359	51,469	422
	50,000	10,000	422	51,469	468
	100,000	50,000	734	102,829	797
	150,000	100,000	1,109	154,170	1,219
David's Approach Table Variable with `SET ROWCOUNT`)	50,000	1	0	80	0
	50,000	10	0	90	0
	50,000	100	0	182	15
	50,000	1,000	0	1,133	16
	50,000	10,000	47	10,422	110
	100,000	50,000	313	51,501	391
	150,000	100,000	688	102,867	750
Greg's Approach	50,000	1	0	24	0
	50,000	10	0	24	0
	50,000	100	0	26	0
	50,000	1,000	0	24	0
	50,000	10,000	16	125	15
	100,000	50,000	31	525	63
	150,000	100,000	31	1,023	78

Impressed? If your application is like 99.99% of application using paging and you know no one will ever make it to the 1000th page, you might not think using knowledge of the optimizer is not very important, but if you talk to just about anyone who has a highly loaded database, they will tell you space and blocking in tempdb is a problem. (Anytime you write to a temporary table or table variable, you're working with tempdb.) Anything that can be done to minimize the use of tempdb should be used – it will speed up your application. Also, the reduction in IO through a 30% reduction in reads over using SET ROWCOUNT alone for the first page alone is significant.

Conclusion
As you can see, paging can be greatly improved with the use of SET ROWCOUNT and perhaps a little knowledge of the optimizer. SET ROWCOUNT can be used with a table variable and allow different sorting parameters easily. Additionally, we could still allow ordering of the pages with different sorting options through the use of dynamic SQL (let us know if you'd like to see how to do these things in another article) and still use the optimizer to our advantage, but this can get very complex in the case of ties.

Happy Programming!

By Greg Hamilton

Attachments:

Download the stored procedures, tables, and data used for this article

Read the original article here..
http://www.4guysfromrolla.com/webtech/042606-1.shtml
Thank you 4GuysFromRolla... You guys rock...

Please visit my other blogs too: http://edwardanil.blogspot.com for information and http://netsell.blogspot.com for internet marketing. Thanks !!

Monday, July 31, 2006

ASP Tips to Improve Performance and Style

Performance is a feature. You need to design for performance up front, or you get to rewrite your application later on. That said, what are some good strategies for maximizing the performance of your Active Server Pages (ASP) application?

This article presents tips for optimizing ASP applications and Visual Basic® Scripting Edition (VBScript). Many traps and pitfalls are discussed. The suggestions listed in this article have been tested on http://www.microsoft.com and other sites, and work very well. This article assumes that you have a basic understanding of ASP development, including VBScript and/or JScript, ASP Applications, ASP Sessions, and the other ASP intrinsic objects (Request, Response, and Server).

Often, ASP performance depends on much more than the ASP code itself. Rather than cover all wisdom in one article, we list performance-related resources at the end. These links cover both ASP and non-ASP topics, including ActiveX® Data Objects (ADO), Component Object Model (COM), databases, and Internet Information Server (IIS) configuration. These are some of our favorite links-be sure to give them a look.

Len Cardinal
Senior Consultant, Microsoft Consulting Services
George V. Reilly
Microsoft IIS Performance Lead

Adapted from an article by Nancy Cluts
Developer Technology Engineer
Microsoft Corporation

Original Resource: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnasp/html/asptips.asp

ASP Tips: Tip 15: More on Fine Tuning

Use Server.Transfer Instead of Response.Redirect Whenever Possible

Response.Redirect tells the browser to request a different page. This function is often used to redirect the user to a log on or error page. Since a redirect forces a new page request, the result is that the browser has to make two round trips to the Web server, and the Web server has to handle an extra request. IIS 5.0 introduces a new function, Server.Transfer, which transfers execution to a different ASP page on the same server. This avoids the extra browser-to-Web-server round trip, resulting in better overall system performance, as well as better response time for the user. Check out New Directions in Redirection, which talks about Server.Transfer and Server.Execute.

Also see Leveraging ASP in IIS 5.0 for a full list of the new features in IIS 5.0 and ASP 3.0.

Use Trailing Slashes in Directory URLs

A related tip is to make sure to use a trailing slash (/) in URLs that point to directories. If you omit the trailing slash, the browser will make a request to the server, only to be told that it's asking for a directory. The browser will then make a second request with the slash appended to the URL, and only then will the server respond with the default document for that directory, or a directory listing if there is no default document and directory browsing has been enabled. Appending the slash cuts out the first, futile round trip. For user-friendliness, you may want to omit the trailing slash in display names.

For example, write:

<a href="http://msdn.microsoft.com/workshop/"
title="MSDN Web Workshop">http://msdn.microsoft.com/workshop</a>

This also applies to URLs pointing to the home page on a Web site: Use the following:
<a href="http://msdn.microsoft.com/">, not <a href="http://msdn.microsoft.com">.

Avoid Using Server Variables

Accessing server variables causes your Web site to make a special request to the server and collect all server variables, not just the one that you requested. This is akin to needing to retrieve a specific item in a folder that you have in that musty attic of yours. When you want that one item, you have to go to the attic to get the folder first, before you can access the item. This is the same thing that happens when you request a server variable—the performance hit occurs the first time you request a server variable. Subsequent requests for other server variables do not cause performance hits.

Never access the Request object unqualified (for example, Request("Data")). For items not in Request.Cookies, Request.Form, Request.QueryString, or Request.ClientCertificate, there is an implicit call to Request.ServerVariables. The Request.ServerVariables collection is much slower than the other collections.

Upgrade to the Latest and Greatest

System components are constantly updated and we recommend that you upgrade to the latest and greatest. Best of all would be to upgrade to Windows 2000 (and hence, IIS 5.0, ADO 2.5, MSXML 2.5, Internet Explorer 5.0, VBScript 5.1, and JScript 5.1). IIS 5.0 and ADO 2.5 implement spectacular performance gains on multiprocessor machines. Under Windows 2000, ASP scales nicely to four processors or more, whereas under IIS 4.0, ASP didn't scale well beyond two processors. The more script code and ADO usage in your application, the more performance benefits you'll see after upgrading to Windows 2000.

If you can't upgrade to Windows 2000 just yet, you can upgrade to the latest releases of SQL Server, ADO, VBScript and JScript, MSXML, Internet Explorer, and Windows NT 4 Service Packs. All of them offer improved performance as well as increased reliability.

Tune Your Web Server

There are several IIS tuning parameters that can improve site performance. For example, with IIS 4.0, we've often found that increasing the ASP ProcessorThreadMax parameter (see IIS documentation) can have significant benefits, especially on sites that tend to wait on back-end resources such as databases or other middle-ware products such as screen-scrapers. In IIS 5.0, you may find that turning on ASP Thread Gating is more effective than trying to find an optimal setting for AspProcessorThreadMax, as it is now known.

For good references, see Tuning IIS below.

The optimal configuration settings are going to be determined by (among other factors) your application code, the hardware it runs on, and the client workload. The only way to discover the optimal settings is to run performance tests, which brings us to the next tip.

Do Performance Testing

As we said before, performance is a feature. If you are trying to improve performance on a site, set a performance goal, then make incremental improvements until you reach your goal. Don't save all performance testing for the end of the project. Often, at the end of a project, it's too late to make necessary architectural changes, and you disappoint your customer. Make performance testing a part of your daily testing. Performance testing can be done against individual components, such as ASP pages or COM objects, or on the site as a whole.

Many people test the performance of their Web sites by using a single browser to request pages. This will give you a good feel for the responsiveness of the site, but it will tell you nothing about how well the site performs under load.

Generally, to accurately measure performance, you need a dedicated testing environment. This environment should include hardware that somewhat resembles production hardware in terms of processor speed, number of processors, memory, disk, network configuration, and so on. Next, you need to define your client workload: how many simultaneous users, the frequency of requests they will be making, the types of pages they'll be hitting, and so forth. If you don't have access to realistic usage data from your site, you'll need to guesstimate. Finally, you need a tool that can simulate your anticipated client workloads. Armed with these tools, you can start to answer questions such as "How many servers will I need if I have N simultaneous users?" You can also sniff out bottlenecks and target these for optimization.

Some good Web stress-testing tools are listed below. We highly recommend the Microsoft Web Application Stress (WAS) Toolkit. WAS allows you to record test scripts and then simulate hundreds or thousands of users hitting your Web servers. WAS reports numerous statistics, including requests per second, response time distributions, and error counts. WAS has both a rich-client and a Web-based interface; the Web interface allows you to run tests remotely.

ASP Tips: Tip 14: Avoid String Concatenation in Loops

Many people build a string in a loop like this:

s = "<table>" & vbCrLf
For Each fld in rs.Fields
s = s & " <th>" & fld.Name & "</th> "
Next

While Not rs.EOF
s = s & vbCrLf & " <tr>"
For Each fld in rs.Fields
s = s & " <td>" & fld.Value & "</td> "
Next
s = s & " </tr>"
rs.MoveNext
Wend

s = s & vbCrLf & "</table>" & vbCrLf
Response.Write s

There are several problems with this approach. The first is that repeatedly concatenating a string takes quadratic time; less formally, the time that it takes to run this loop is proportional to the square of the number of records times the number of fields. A simpler example should make this clearer.

s = ""
For i = Asc("A") to Asc("Z")
s = s & Chr(i)
Next

On the first iteration, you get a one-character string, "A". On the second iteration, VBScript has to reallocate the string and copy two characters ("AB") into s. On the third iteration, it has to reallocate s again and copy three characters into s. On the Nth (26th) iteration, it has to reallocate and copy N characters into s. That's a total of 1+2+3+...+N which is N*(N+1)/2 copies.

In the recordset example above, if there were 100 records and 5 fields, the inner loop would be executed 100*5 = 500 times and the time taken to do all the copying and reallocation would be proportional to 500*500 = 250,000. That's a lot of copying for a modest-sized recordset.

In this example, the code could be improved by replacing the string concatenation with Response.Write() or inline script (<% = fld.Value %>). If response buffering is turned on (as it should be), this will be fast, as Response.Write just appends the data to the end of the response buffer. No reallocation is involved and it's very efficient.

In the particular case of transforming an ADO recordset into an HTML table, consider using GetRows or GetString.

If you concatenate strings in JScript, it is highly recommended that you use the += operator; that is, use s += "some string", not s = s + "some string".

ASP Tips: Tip 13: Use Your Browser

Take Advantage of Your Browser's Validation Abilities

Modern browsers have advanced support for features such as XML, DHTML, Java applets, and the Remote Data Service. Take advantage of these features whenever you can. All of these technologies can save round trips to the Web server by performing client-side validation as well as data caching. If you are running a smart browser, the browser is capable of doing some validation for you (for example, checking that a credit card has a valid checksum before executing POST). Again, take advantage of this whenever you can. By cutting down on client-server round trips, you'll reduce the stress on the Web server and cut down network traffic (though the initial page sent to the browser is likely to be larger), as well as any back-end resources that the server accesses. Furthermore, the user will not have to fetch new pages as often, improving the experience. This does not relieve you of the need to do server-side validation—you should always do server-side validation as well. This protects against bad data coming from the client for some reason, such as hacking, or browsers that don't run your client-side validation routines.

Much has been made of creating "browser-independent" HTML. This concern often discourages the developer from taking advantage of popular browser features that could benefit performance. For truly high-performance sites that must be concerned about browser "reach," a good strategy is to optimize pages for the popular browsers. Browser features can be easily detected in ASP using the Browser Capabilities Component. Tools such as Microsoft FrontPage can help you design code that works with the browsers and HTML versions you wish to target.

Enable Browser and Proxy Caching

By default, ASP disables caching in browsers and proxies. This makes sense since by nature an ASP page is dynamic with potentially time-sensitive information. If you have a page that doesn't require a refresh on every view, you should enable browser and proxy caching. This allows browsers and proxies to use a "cached" copy of a page for a certain length of time, which you can control. Caching can greatly alleviate load on your server and improve the user experience.

What kind of dynamic pages might be candidates for caching? Some examples are:

A weather page, where the weather is only updated every 5 minutes.
A home page listing news items or press releases, which are updated twice a day.
A mutual fund performance listing, where underlying statistics are only updated every few hours.

Note that with browser or proxy caching, you'll get less hits recorded on your Web server. If you are trying to accurately measure all page views, or post advertising, you may not be happy with browser and proxy caching.

Browser caching is controlled by the HTTP "Expires" header, which is sent by a Web server to a browser. ASP provides two simple mechanisms to send this header. To set the page to expire at a certain number of minutes in the future, set the Response.Expires property. The following example tells the browser that the content expires in 10 minutes:

<% Response.Expires = 10 %>

Setting Response.Expires to a negative number or 0 disables caching. Be sure to use a large negative number, such as -1000 (a little more than a day), to work around mismatches between the clocks on the server and the browsers. A second property, Response.ExpiresAbsolute, allows you set the specific time at which the content will expire:

<% Response.ExpiresAbsolute = #May 31,2001 13:30:15# %>

Rather than using the Response object to set expiration, you can write a <meta> tag into the HTML, usually within the <HEAD> section of the HTML file. Some browsers will respect this directive, although proxies will not.

<meta equiv="Expires" value="May 31,2001 13:30:15">

Finally, you can indicate whether the content is valid for an HTTP proxy to cache, using the Response.CacheControl property. Setting this property to "Public" enables proxies to cache the content.

<% Response.CacheControl = "Public" %>

By default, this property is set to "Private." Note that you should not enable proxy caching for pages that show data specific to a user, as the proxy may serve pages to users that belong to other users.