Need a faster paging in a wesite search result page

have over million rows in the our table and we are looking forward to increase the speed of our query .Any ideas?

set ANSI_NULLS OFFset QUOTED_IDENTIFIER OFFGOALTER PROCEDURE [dbo].[mainSearch] @.startRowIndexint,@.maximumRowsint,@.rowCountint out,@.countedRowint,@.QUERY nvarchar(400)ASSELECT _ID,_NAME,_TYPE,_CREATEDATE,ESTATETYPE,ESTATEDISPLAYPRICE,ESTATEDISPLAYPRICECURRENCY,ESTATEDISTRICT,ESTATECITY,ESTATEROOMCOUNT,NUMBEROFPICTURES FROM (SELECT ROW_NUMBER() OVER (ORDER BY _CREATEDATE DESC) AS ROWRANK,*FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@.QUERY)as KEY_TBLON ADTBL._ID = KEY_TBL.[KEY]Where (_DELETIONSTATUS=0))AS RANKEDADDSWHERE ROWRANK > @.startRowIndex AND ROWRANK <= @.startRowIndex + @.maximumRows -1if(@.countedRow < 1)SET @.rowCount =(SELECT COUNT(_ID) FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@.QUERY)as KEY_TBLON ADTBL._ID = KEY_TBL.[KEY]Where _DELETIONSTATUS=0)else SET @.rowCount = @.countedRowRETURN

Do you have any idea about the Full Text Indexing?

|||

I don't know if you designed the table but have you also checked to ensure that all the proper indexes have been added ? You also might want to look at the query execution plan to see which part is consuming most of the execution time such as a hash or merge join etc.