Click here to Skip to main content
15,892,804 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
SQL
USE [HJGINFODB]
GO
/****** 
object:Table [dbo].[TB_FB_EX]  
scritp:09/19/2010 21:16:39 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TB_FB_EX](
    [ID] [bigint] NULL,
    [CITY_ID] [int] NOT NULL,
    [QU_ID] [int] NOT NULL,
    [TYPE_ID] [int] NOT NULL,
    [TYPE_PID] [int] NOT NULL,
    [STITLE] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [JG] [int] NOT NULL,
    [INSTIME] [datetime] NULL CONSTRAINT [DF_TB_FB_EX_INSTIME]  DEFAULT (getdate())
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF





USE [HJGINFODB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[f_div_TB_FB_EX]
@CITY_ID int,---------city of ID,default 0
@QU_ID int,-----------area ID,default 0
@TYPE_PID int,--------main type ID,default 0
@TYPE_ID int,---------small type ID,default 0

@PageIndex Int,-------Page Index 
@PageSize Int,--------Page size
@ReturnRecordCount int out---row count
as
/*

declare @ReturnRecordCount int
exec [f_div_TB_FB_EX] 264,0,1,0,1,10,@ReturnRecordCount

declare @ReturnRecordCount int
exec [f_div_TB_FB_EX] 264,571003,2,0,0,10,@ReturnRecordCount

declare @ReturnRecordCount int
exec [f_div_TB_FB_EX] 264,0,0,30,0,10,@ReturnRecordCount

declare @ReturnRecordCount int
exec [f_div_TB_FB_EX] 264,571008,0,30,0,10,@ReturnRecordCount

*/
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

SET @PageLowerBound = @PageSize * @PageIndex+1
SET @PageUpperBound = @PageLowerBound + @PageSize-1

--http://localhost/hangzhou/gongzuo/  一级
if @CITY_ID<>0 and @TYPE_PID<>0 and @QU_ID=0
    begin
    select @ReturnRecordCount = count(*)  from TB_FB_EX  with (nolock)
    where CITY_ID = @CITY_ID  and TYPE_PID=@TYPE_PID

    SELECT *
     FROM     (select ID,CITY_ID,QU_ID,
    TYPE_ID,TYPE_PID,STITLE,JG,INSTIME ,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
    FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and TYPE_PID=@TYPE_PID) AS NormRen
    where  SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
    end
--http://localhost/hangzhou/jianzhiduangong/  二级
else if @CITY_ID<>0 and @TYPE_ID<>0  and @QU_ID=0
    begin
    select @ReturnRecordCount = count(*)  from TB_FB_EX  with (nolock)
    where CITY_ID = @CITY_ID  and TYPE_ID=@TYPE_ID

    SELECT *
     FROM     (select ID,CITY_ID,QU_ID,
    TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
    FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and TYPE_ID=@TYPE_ID) AS NormRen
    where  SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
    end
else if @CITY_ID<>0 and @TYPE_PID<>0  and @QU_ID<>0
--http://localhost/hangzhou/gongzuo/571004/
begin
    select @ReturnRecordCount = count(*)  from TB_FB_EX  with (nolock)
    where CITY_ID = @CITY_ID  and QU_ID=@QU_ID and TYPE_PID=@TYPE_PID

    SELECT *
     FROM     (select ID,CITY_ID,QU_ID,
    TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
    FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and QU_ID=@QU_ID and TYPE_PID=@TYPE_PID ) AS NormRen
    where  SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end

else if @CITY_ID<>0 and @TYPE_ID<>0  and @QU_ID<>0
--http://localhost/hangzhou/dianyuan/571006/
begin
    select @ReturnRecordCount = count(*)  from TB_FB_EX with (nolock)
    where CITY_ID = @CITY_ID  and TYPE_ID=@TYPE_ID and QU_ID=@QU_ID

    SELECT *
     FROM     (select ID,CITY_ID,QU_ID,
    TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
    FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and TYPE_ID=@TYPE_ID and QU_ID=@QU_ID) AS NormRen
    where  SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end
else if @CITY_ID<>0 and @TYPE_ID=0  and @QU_ID=0 and @TYPE_PID=0
begin
    select @ReturnRecordCount = count(*)  from TB_FB_EX with (nolock)
    where CITY_ID = @CITY_ID

    SELECT *
     FROM     (select ID,CITY_ID,QU_ID,
    TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
    FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID) AS NormRen
    where  SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end
Posted
Updated 19-Sep-10 23:24pm
v2
Comments
Sandeep Mewara 20-Sep-10 5:22am    
Update from OP: there are 1000 0000 or more records in the table

1 solution

I would index several columns, you want to index a column when it is used in a link or criteria in the where clause.

That being said you do not want too many indexes. At a glance I imagine that at least City_ID and Type_PID need to be indexed. Note I did not decipher your program just a quick overview.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900