free qr code generator in vb.net Row Numbers, Unique Sort Column in .NET

Creator QR-Code in .NET Row Numbers, Unique Sort Column

This technique to calculate row numbers, though fairly simple, is extremely slow. To understand why, examine the execution plan shown in Figure 4-6 created for the query.

[View full size image]

There is an index on the sort column (empid) that happens to be the Sales table's clustered index. The table is first fully scanned (Clustered Index Scan operator) to return all rows. For each row returned from the initial full scan, the Nested Loops operator invokes the activity that generates the row number by counting rows. Each row number calculation involves a seek operation within the clustered index, followed by a partial scan operation (from the head of the leaf level's linked list to the last point where inner_empid is smaller than or equal to outer_empid). Note that there are two different operators that use the clustered indexfirst, a full scan to return all rows; second, a seek followed by a partial scan for each outer row to achieve the count. Remember that the primary factor affecting the performance of queries that do data manipulation

will usually be I/O. A rough estimate of the number or rows accessed here will show how inefficient this execution plan is. To calculate rownum for the first row of the table, SQL Server needs to scan 1 row in the index. For the second row, it needs to scan 2 rows. For the third row, it needs to scan 3 rows, and so on, and for the nth row of the table, it needs to scan n rows. For a table with n rows, having an index based on the sort column in place, the total number of rows scanned is 1 + 2 + 3 + ... + n. You may not grasp immediately the large number of rows that are going to be scanned. To give you a sense, for a table with 100,000 rows, you're looking at 5,000,050,000 rows that are going to be scanned in total. As an aside, there's a story told about the mathematician Gauss. When he was a child, he and his classmates got an assignment from their teacher to find the sum of all the integers from 1 through 100. Gauss gave the answer almost instantly. When the teacher asked him how he came up with the answer so fast, he said that he added the first and the last values (1+100 = 101), and then multiplied that total by half the number of integers (50), which is the number of pairs. Sure enough, the result of first_val + last_val is equal to the second_val + next_to_last val, and so on. In short, the formula for the sum of the first n positive integers is (n + n2) / 2. That's the number of rows that need to be scanned in total to calculate row numbers using this technique when there is an index based on the sort column. You're looking at an n2 graph of I/O cost and run time based on the number of rows in the table. You can play with the numbers in the formula and see that the cost gets humongous pretty quickly. When there's no index on the table, matters are even worse. To calculate each row number, the entire table needs to be scanned. The total number of rows scanned by the query is then n2. For example, given a table with 100,000 rows, the query will end up scanning 10,000,000,000 rows in total.

When the sort column is not unique, you can make it unique by introducing a tiebreaker, to allow a solution that keeps a reasonable level of simplicity. Let sortcol be the sort column, and let tiebreaker be the tiebreaker column. To count rows with the same or smaller values of the sort list (sortcol+tiebreaker), use the following expression in the subquery: inner_sortcol < outer_sortcol OR inner_sortcol = outer_sortcol AND inner_tiebreaker <= outer_tiebreaker

Note that operator precedence dictates that AND would be evaluated prior to OR. For clarity, manageability, and readability you might want to use parentheses. The following query (which generates the output shown in Table 4-23) produces row numbers based on qty and empid, in that order: SELECT empid, qty, (SELECT COUNT(*) FROM dbo.Sales AS S2 WHERE S2.qty < S1.qty OR (S2.qty = S1.qty AND S2.empid <= S1.empid)) AS rownum FROM dbo.Sales AS S1 ORDER BY qty, empid;