Showing posts with label paging. Show all posts
Showing posts with label paging. Show all posts

Wednesday, March 28, 2012

Need Help - Custome Paging Using ROW_NUMBER()

Hi,

I am attempting to implement a custome paging solution for my web Application, I have a table that has 30,000 records and I need to bw able to page through these using a Gridview. Here is my curent code but it generates an error when I try to compile the Stored Procedure, I get the following errors:

<Error messages>

These are on the first SELECT Line..

Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.CODE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.LAST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.FIRST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.MIDDLE_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAMETYPE.TYPE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.FUNERAL.NUMBER" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "mort.NAME.CODE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.LAST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.FIRST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.MIDDLE_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAMETYPE.TYPE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "FUNERAL.NUMBER" could not be bound.

</Error Messages>

<Sotred Procedure>

CREATE PROCEDURE proc_NAMEGetPaged
@.startRowIndex int,
@.maximumRows int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT NAME.CODE, NAME.LAST_NAME, NAME.FIRST_NAME + ' ' + NAME.MIDDLE_NAME AS Name, NAMETYPE.TYPE, FUNERAL.NUMBER
FROM
(SELECT CODE, LAST_NAME, FIRST_NAME + ' ' + MIDDLE_NAME AS Name, NAMETYPE.TYPE, FUNERAL.NUMBER,
ROW_NUMBER() OVER(ORDER BY LAST_NAME) as RowNum
FROM Name n) as NameInfo
WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) -1
END
GO

</Stored Procedure>

Any assistance in resolving this would be greatly appreciated..

Regards..

Peter.

Hi,

Have resolved my problem see code below..

<Stored Procedure>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Peter Annandale
-- Create date: 21/10/2006
-- Description: Return Subset from Names table
-- =============================================
CREATE PROCEDURE proc_NAMEGetPaged
-- Add the parameters for the stored procedure here
@.startRowIndex int,
@.maximumRows int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT CODE, LAST_NAME, Name, TYPE, NUMBER
FROM
(SELECT n.CODE, n.LAST_NAME, n.FIRST_NAME + ' ' + n.MIDDLE_NAME AS Name, nt.TYPE, f.NUMBER,
ROW_NUMBER() OVER(ORDER BY n.LAST_NAME) as RowNum
FROM dbo.NAME n
LEFT OUTER JOIN NAMETYPE nt ON n.NAME_TYPE = nt.NAME_TYPE
LEFT OUTER JOIN FUNERAL f ON n.CODE = f.DECEASED) as NameInfo
WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) -1
END
GO


< /Stored Procedure>

Regards..

Peter.

Saturday, February 25, 2012

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.