Greetings Borg! I have an offer of more optimum method of sample of data instead of what is used now. It is so-called " the Method paginal SQL samples " I it did not test on WWF 8 beta 2, but I can tell, that it is less exacting to resources and returns only that quantity of data which is necessary in each concrete sample.
The offered method does not apply for a rank of the best. It, maybe, and not better, but precisely is not worse than others.
1. Search of the decision was based on following wishes: the Reference to new page of sample should not lead re-query all sample.
2. The inquiry of page should return usual habitual recordset. Inizialisation should not be excessively long that the inquiry to the table in 1000000 lines has not tired with expectation.
3. It should be at any moment authorized to choose any page from sample, including earlier chosen.
4. The page should contain any set in lines.
5. The page should begin with any set line under number.
6. The decision satisfying to all these wishes to a srtass, has been found.
1. Here it: we Have inquiry which we wish to choose page by page
select * from BigTable
we it we do not start, and it we pass to a step 2.
2. We initialize thus: declare @handle int,
@rows int exec sp_cursoropen @handle OUT, ' select * from BigTable ', 1, 1, @rows OUT select @handle, @rows
thus we receive empty recordset, containing metadata-descriptions of columns who can be used for reception of the name of fields (and types). After NextRecordset also we receive handle the turned out cursor and in lines in all sample. handle it to be necessary for us for substitution in following calls, and it should be kept on the client in a global variable, and in lines can be used for definition pages.
3. We receive the necessary page from sample:
exec sp_cursorfetch @handle, 16, @rowid, @rowcount
Where in @handle it is substituted the kept value handle, in @rowid number of a line with which the page interesting us begins is substituted, and in @rowcount is substituted in lines on the given page.
The step 3 is repeated so much how many it is necessary time.
4. We release resources after pages already any more will not be necessary
exec sp_cursorclose @handle
In our case in Query Analizer it is carried out following SQL a code
-----------------------------------------------------------------------------
Declare @handle int,@rows int
EXEC sp_cursoropen @handle OUT, 'SELECT TOP 1000000 tblTopic.Topic_ID, tblTopic.Poll_ID, tblTopic.Moved_ID,
tblTopic.Subject, tblTopic.Icon, tblTopic.Start_Thread_ID, tblTopic.Last_Thread_ID,
tblTopic.No_of_replies, tblTopic.No_of_views, tblTopic.Locked, tblTopic.Priority,
tblTopic.Hide, tblThread.Message_date, tblThread.Message, tblThread.Author_ID,
tblAuthor.Username, LastThread.Message_date, LastThread.Author_ID, LastAuthor.Username,
tblTopic.Event_date
FROM tblTopic WITH (NOLOCK) , tblThread WITH (NOLOCK) , tblThread AS LastThread WITH (NOLOCK) , tblAuthor WITH (NOLOCK) , tblAuthor AS LastAuthor WITH (NOLOCK)
WHERE ( tblThread.Author_ID = tblAuthor.Author_ID AND LastThread.Author_ID = LastAuthor.Author_ID AND tblTopic.Start_Thread_ID = tblThread.Thread_ID AND tblTopic.Last_Thread_ID = LastThread.Thread_ID AND (tblTopic.Priority = 3 OR tblTopic.Moved_ID = 2 OR tblTopic.Forum_ID = 2) ) AND (tblTopic.Hide = 0 )
ORDER BY tblTopic.Priority DESC, LastThread.Message_date DESC;',1, 1, @rows OUT
SELECT @handle, @rows
Declare @rowid int,@rowcount int
SET @rowid = 2000
SET @rowcount = 20
exec sp_cursorfetch @handle,16,@rowid,@rowcount
------------------------------------------------------------------------------------------
The necessary set of records stands out practically instantly and well works with a lot of records in the table. 
Edited by Ritchie - 09 March 2006 at 7:06pm
Topic Options
Post Options
Thanks(0)
