Search Speed Tuning[ASAP Responses Requested]

[Magiaus]

Indexs are not paert of the equation at this point.....

If I have a query:

@Param... 1-20,
@Page int,
@NumberOfResults int,
@MaxSearchResults int
AS

SET ROWCOUNT @MaxSearchResults

DECLARE @Results TABLE
(
    CountID int IDENTITY NOT NULL,
    tblAID int,
    tblBID int
)
INSERT INTO @Results (tblAID, tblBID)
SELECT tblA.ID, tblB.ID FROM tblA INNER JOIN tbl ON tblA.BID = tblB.ID WHERE @Param...1-20
ORDER BY tblA.SomeColumn, tblB.SomeColumn

SELECT tblA.*, tbl.* FROM @Results AS tblRet 
WHERE --page index is 1 based
tblRet.CountID > @NumberOfResults * (@PageNumber - 1) 
AND 
tblRet.CountID <= @NumberOfResults * (@PageNumber)	

SET ROWCOUNT 0

Will the fact that the number of search results that are being returned speed up the query given that because of the order by clause all possible results are pulled and sorted initialy before the ROWCOUNT limit is applied?

Also won't setting this limit cause some results to never be avaliable?

[Magiaus]

I am thinking it is a waste of effort to try to speed up the searches by doing this.

1) All posssible results are going to be pulled and touched.
2) Some results may never be avaliable.
3)The paging is already limiting the amount of network traffic caused by the query and reducing the Asp.Net page weight.

Does this sound correct?

Info on using SELECT TOP: http://msdn.microsoft.com/library/de...qd_04_13ec.asp

Info on using SET ROWCOUNT n: http://msdn.microsoft.com/library/de...t-set_0bjo.asp

[Magiaus]

um.... anybody...? comments..? ***...?

[dee-u]

I can only advice, provide the users with the data they only need...

[Magiaus]

Well, I basicly convinced them to let me setup a configurable/dynamic limit for each search, and to let me start off with 0(get all) and limit as need be. We also setup indexing and rewrote a few of the really slow queries. Right now the queries are completing in under a second with around 1000 - 3000 records to work with... So for now the limit seems pointless and if they ever want to use the thing they can....