free qr code generator in vb.net Benchmark Results in .NET

Drawer QR Code in .NET Benchmark Results

IDENTITY Cursor 80 160 660 893 870 990 900 1123 1040 1060 550 1343 1613 2110 2873 3043 3913 4276 4766 5280

The query uses a pivoting technique that I'll describe in 6, so don't try to squeeze your brains if you're not familiar with it. For our discussion, the important thing is the benchmark's results. You can immediately see that the preSQL Server 2005 set-based technique is dramatically slower than all the rest, and I explained why earlier. You will also notice that the ROW_NUMBER function is dramatically faster than all the rest. I wanted to present a graph with all results, but the run times of the preSQL Server 2005 set-based techniques were so great that the lines for the other solutions were simply flat. So I decided to present two separate graphs. Figure 4-7 shows the graph of run times for the IDENTITY-based, cursor-based, and ROW_NUMBER function-based techniques. Figure 4-8 shows the graph for the set-based preSQL Server 2005 technique.

You can see in Figure 4-7 that all three techniques have a fairly linear performance graph, while Figure 4-8 shows a beautifully curved n2 graph. The obvious conclusion is that in SQL Server 2005 you should always use the new ranking functions. In SQL Server 2000, if it's important to you to use a standard set-based technique, only use that technique when the partition size is fairly small (measured in dozens). Otherwise, use the IDENTITYbased technique, first creating the table, and then loading the data.

As I mentioned earlier, row numbers have many practical applications that I'll demonstrate throughout the book. Here I'd like to show one example where I use row numbers to achieve pagingaccessing rows of a result set in chunks. Paging is a common need in applications, allowing the user to navigate through chunks or portions of a result set. Paging with row numbers is also a handy technique. This example will also allow me to demonstrate additional optimization techniques that the optimizer applies when using the ROW_NUMBER function. Of course, in SQL Server 2000 you can use the less efficient techniques to calculate row numbers to achieve paging.

Ad hoc paging is a request for a single page, where the input is the page number and page size (the number of rows in a page). When the user needs a particular single page and won't request

additional pages, you implement a different solution than the one you would for multiple page requests. First you have to realize that there's no way to access page n without physically accessing pages 1 through n1. Bearing this in mind, the following code returns a page of rows from the Sales table ordered by qty and empid, given the page size and page number as inputs: DECLARE @pagesize AS INT, @pagenum AS INT; SET @pagesize = 5; SET @pagenum = 2; WITH SalesCTE AS ( SELECT ROW_NUMBER() OVER(ORDER BY qty, empid) AS rownum, empid, mgrid, qty FROM dbo.Sales ) SELECT rownum, empid, mgrid, qty FROM SalesCTE WHERE rownum > @pagesize * (@pagenum-1) AND rownum <= @pagesize * @pagenum ORDER BY rownum;

This code generates the output shown in Table 4-30.

The CTE called SalesCTE assigns row numbers to the sales rows based on the order of qty and empid. The outer query filters only the target page's rows using a formula based on the input page size and page number. You might be concerned that the query appears to calculate row numbers for all rows and then filter only the requested page's rows. This might seem to require a full table scan. With very large tables this, of course, would be a serious performance issue. However, before getting concerned, examine the execution plan for this query, which is shown in Figure 4-9.