Programing Solutions on OOPS-Concept

Equivalent of LIMIT and OFFSET for SQL Server 2005/2008

Introduction

The paging and ranking functions introduced in 2005 are old news by now, but the typical ROW_NUMBER OVER() implementation only solves part of the problem.

Nearly every application that uses paging gives some indication of how many pages (or total records) are in the total result set. The challenge is to query the total number of rows, and return only the desired records with a minimum of overhead? The holy grail solution would allow you to return one page of the results and the total number of rows with no additional I/O overhead.

In this article, we're going to explore four approaches to this problem and discuss their relative strengths and weaknesses. For the purposes of comparison, we'll be using I/O as a relative benchmark.

The 'two-bites' approach

The most basic approach is the 'two-bites' approach. In this approach you, effectively, run your query twice; querying the total rows in one pass, and querying your result set in the second. The code is pretty straightforward:

DECLARE @startRow INT ; SET @startrow = 50
SELECT COUNT(*) AS TotRows
FROM [INFORMATION_SCHEMA].columns

;WITH cols
AS
(
    SELECT table_name, column_name,
        ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq
    FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDERBY seq

It gives the desired results, but this approach doubles the cost of the query because you query your underlying tables twice:

(1 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(50 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The temp table approach

The 'two-bites' approach is especially undesirable if your paged query is very expensive and complex. A common workaround is to write the superset into a temporary table, then query out the subset. This is also the most common way to implement paging pre-2005 (in this case, ROW_NUMBER is superfluous).

DECLARE @startRow INT ; SET @startrow = 50
CREATE TABLE #pgeResults(
    id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    table_name VARCHAR(255),
    column_name VARCHAR(255)
)
INSERT INTO #pgeResults(Table_name, column_name)
SELECT table_name, column_name
FROM [INFORMATION_SCHEMA].columns
ORDERBY [table_name], [column_name]

SELECT @@ROWCOUNT AS TotRows
SELECT Table_Name, Column_Name
FROM #pgeResults
WHERE id between @startrow and @startrow + 49
ORDER BY id

DROPTABLE #pgeResults

Looking at the query plan, you can see that your underlying tables are queried only once but the I/O stats show us that you take an even bigger hit populating the temporary table.

Table '#pgeResults_________________________________________________________________________________________________________000000001A9F'. Scan count 0, logical reads 5599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 14, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 39, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(2762 row(s) affected)

(1 row(s) affected)

(50 row(s) affected)
Table '#pgeResults_________________________________________________________________________________________________________000000001A9F'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

In this case, it would be better to query the tables twice. Maybe some new 2005 functionality can yield a better solution.

The COUNT(*) OVER() Approach

OVER() can also be used with Aggregate Window Functions. For our purposes this means we can do a COUNT(*) without the need for a GROUP BY clause, returning the total count in our result set. The code definitely looks much cleaner and, if your application permits it, you can simply return one dataset (eliminating the overhead of writing to a temp table).

DECLARE @startRow INT ; SET @startrow = 50

;WITH cols
AS
(
    SELECT table_name, column_name,
    ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq,
    COUNT(*) OVER() AS totrows
    FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDER BY seq

Unfortunately this approach has it's own hidden overhead:

Table 'Worktable'. Scan count 3, logical reads 5724, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Where did that come from? In this case, SQL Server implements the COUNT(*) OVER() by dumping all the data into a hidden spool table, which it then aggregates and joins back to your main output. It does this to avoid re scanning the underlying tables. Although this approach looks the cleanest, it introduces the most overhead.

I've spent most of today cleaning up and old data-paging proc that is both very inefficient and frequently called enough for me to notice it. I've explored probably a dozen other approaches to solving this problem before I came up with the solution below. For the sake of brevity—and because they rest are pretty obscure and equally inefficient—we'll now skip to the best solution.

The Holy Grail

In theory, ROW_NUMBER() gives you all the information you need because it assigns a sequential number to every single row in your result set. It all falls down, of course, when you only return a subset of your results that don't include the highest sequential number. The solution is to return a 2^nd column of sequential numbers, in the reverse order. The total number of the records will always be the sum of the two fields on any given row minus 1 (unless one of your sequences is zero-bound).

DECLARE @startRow INT ; SET @startrow = 50
;WITH cols
AS
(
    SELECT table_name, column_name,
        ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq,
        ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows
    FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows + seq -1 as TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDER BY seq

This approach gives us our page of data and the total number of rows with zero additional overhead! (well, maybe one or two ms of CPU time, but that's it) The I/O statistics are identical to querying just the subset of records.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Compare the stats above with the stats and query below (just returning one page of data).

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Conclusion

I have found this approach to be best suited for smaller resultsets from complex queries where I/O is the primary bottleneck. Jeff Moden, Peso and others here have pointed out that with larger resultsets, the I/O cost you save is more than outweighed by the CPU cost. You definitly want to compare different approches to find the best solution for your problem.

My real goal here was to try and figure out a way to avoid unnecessary I/O overhead. I am sure that this solution is not the last word on the subject and I greatly look forward to hearing your thoughts, experiences and ideas on this topic. Thank you all for reading and for your feedback.

source: sqlservercentral

Use of length, charAt() and IndexOf() string function function in javascript with example

length

The length method returns the length of a particular string as a number. For instance, if a string is 5 characters long, the length method would return the number 5. To use the method, you will want your string to be placed in a variable. For this example, we will simply assign a string to a variable:

var my_name="Rashmi";

var how_long= my_name.length;

Eample

<SCRIPT

language="JavaScript">

<!--

function getname()

{

var yourname=prompt('Enter your name, NOW!','');

if (yourname.length > 10)

{

alert('That name is just too long, give me a shorter one!');

getname();

}

else

alert('Hi '+yourname+'!');

}

//-->

</SCRIPT>

charAt

Using the charAt method, you can find out what character is filling a designated position within a string. Basically, it allows you to find out what character is first, second, third, and so on. The important thing to remember is that when dealing with the position of a character, the count starts at zero, not one. So the first character is actually at position zero.

var my_name="Rashmi";

var the_char= my_name.charAt(0);

alert('The 1st character is '+the_char+'.');

The output will be ‘R’

indexOf

Now we get to the really fun method of indexOf. This method lets you check for a character or even a small string within the current string, and returns the position at which your character or string begins. Again, remember that the position starts counting at 0, so if indexOf returns zero, it means your character or string begins at the 1st character. Also, indexOf has a useful return value of -1 if the character or string you searched for is not contained within the string. And finally, if the character or string you search for occurs more than once, indexOf returns only the first appearance of your character or string.

First, let's find the 'a'. No problem, again we use the dot operator. IndexOf also takes an argument like charAt does, but this time the argument is the character or string we are looking for:

var my_name="Rashmi";

var where_is_a= my_name.indexOf('a');

alert('The a is at position '+where_is_a+'.');

Notice the quote marks around the argument of a. This tells the browser to look for that string, which is the type of argument you must send for indexOf to function properly. This code will send back the position of the letter a. The alert will say "The a is at position 1." Look out letter a, you have been found!

What about if we search for something that is not there? Well, indexOf will return a -1. We can use this -1 to tell us that what we are looking for is not there. This can be helpful when you ask a viewer for information, but the viewer does not type the information with a required character or set of characters. For instance:

Check for URL or Validate URL in javascript

function get_url()

{

var your_url=prompt('Enter your web page URL!','');

var is_protocol_ok=your_url.indexOf('http://');

if (is_protocol_ok==-1)

{

alert('Error: Your url should begin with http://');

get_url();

}

else

alert('Thanks!');

}

This one checks to be sure the viewer entered the http:// part of the address. If not, it shows the error alert and gives the viewer a chance to enter the url again. Of course, you can cut down on the number of forgotten http:// beginnings by using http:// as the default value for the prompt instead of nothing:

var your_url=prompt('Enter your web page URL!','http://');

Of course, the viewer could delete it by accident (or even on purpose), so the check would still be worthwhile. You could even go a step further and be sure that there is at least one dot (.) in the address as well. This way, the viewer would need to have a string plus a dot and hopefully a com or net afterward ( like http://oops-solution.blogspot.com/). We can also check for the http:// as well, here is a sample:

function get_url()

{

var your_url=prompt('Enter your web page URL!','http://');

var is_protocol_ok=your_url.indexOf('http://');

var is_dot_ok=your_url.indexOf('.');

if ((is_protocol_ok==-1) || (is_dot_ok==-1))

{

alert('Error: Your url should begin with http:// and have at least one dot (.)!');

get_url();

}

else

alert('Thanks!');

}

Programing Solutions on OOPS-Concept

Search This Blog

Tuesday, November 8, 2011

Pagination or paging in SQLserver 2005/2008

String handling in Javascript

Labels

Popular Posts

Followers

Blog Archive