请大家提供一些写得好的sql PROCEDURE(就是执行效率比较高的)的样例。

[longxiaojie]

比如一些开源项目中的存储过程的部分，什么asp程序,asp.net程序很多开源项目的数据库部分。

本人写的很多代码感觉效率太低了，求救阿！

duration>3000,太慢了。

贴出几个吧：

ALTER PROCEDURE UsersTopNGet
(
@TopN int=5,
@AdditionPermission bigint = 0, --附加权限
@ClassCode nvarchar(64)='0000', --行业代码
@Language varchar(8)='zh-CN',

@RoleID smallint = 0,
@RoleType smallint = 0,
@AreaCode nvarchar(32) = '0000',
@SortPostsBy smallint = 0,
@SortOrder smallint = 1,
@TradeType smallint = 0
)
AS


DECLARE @sql varchar(4000)

SET @sql = 'SELECT TOP '+CAST(@TopN AS VARCHAR(3))+' U.*, AC.*, Country = dbo.Fn_GetAreaName(U.AreaCode,1,''' + @Language + '''),Province = dbo.Fn_GetAreaName(U.AreaCode,2, ''' + @Language + '''),
City = dbo.Fn_GetAreaName(U.AreaCode,3,''' + @Language + '''),Town = dbo.Fn_GetAreaName(U.AreaCode,4,''' + @Language + ''') FROM V_CompanyRoles U
LEFT OUTER JOIN AreaCode AC ON AC.AreaCode = U.AreaCode AND AC.Language = ''' + @Language + '''
WHERE U.Language = ''' + @Language + ''' AND CompanyIntroStatus=2'

IF(@RoleType=1)
SET @sql = @sql + ' AND ('+CAST(@RoleID AS VARCHAR(1))+'=0 OR U.RoleID <='+CAST(@RoleID AS VARCHAR(3))+')'
ELSE IF(@RoleType=2)
SET @sql = @sql + ' AND ('+CAST(@RoleID AS VARCHAR(1))+'=0 OR U.RoleID >='+CAST(@RoleID AS VARCHAR(3))+')'
ELSE
SET @sql = @sql + ' AND ('+CAST(@RoleID AS VARCHAR(1))+'=0 OR U.RoleID ='+CAST(@RoleID AS VARCHAR(3))+')'

IF ((@AdditionPermission IS NOT NULL) AND (@AdditionPermission <> 0))
SET @sql = @sql + ' AND U.UserAdditionPermission & CONVERT(bigint,'+CAST(@AdditionPermission AS VARCHAR(20))+') = CONVERT(bigint,'+CAST(@AdditionPermission AS VARCHAR(20))+')'

IF (@ClassCode<>'' AND @ClassCode<>'0000')
BEGIN
--开始判断行业，最多可以传3个行业
SET @sql = @sql + ' AND ('
DECLARE @tmpClassCode VARCHAR(32)
SET @tmpClassCode = ''
SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',0,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' U.CompanyId in (SELECT TCITC.CompanyId FROM TradeCompanyInTradeClass TCITC WHERE TCITC.ClassCode like '''+@tmpClassCode+'%'' OR TCITC.ClassCode=(SELECT TC.ParentClassCode FROM TradeClass TC WHERE TC.ClassCode='''+@tmpClassCode+''' AND TC.Language='''+@Language+'''))'

SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',1,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' OR U.CompanyId in (SELECT TCITC.CompanyId FROM TradeCompanyInTradeClass TCITC WHERE TCITC.ClassCode like '''+@tmpClassCode+'%'' OR TCITC.ClassCode=(SELECT TC.ParentClassCode FROM TradeClass TC WHERE TC.ClassCode='''+@tmpClassCode+''' AND TC.Language='''+@Language+'''))'

SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',2,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' OR U.CompanyId in (SELECT TCITC.CompanyId FROM TradeCompanyInTradeClass TCITC WHERE TCITC.ClassCode like '''+@tmpClassCode+'%'' OR TCITC.ClassCode=(SELECT TC.ParentClassCode FROM TradeClass TC WHERE TC.ClassCode='''+@tmpClassCode+''' AND TC.Language='''+@Language+'''))'

SET @sql = @sql + ')'
END

IF (@AreaCode<>'' AND @AreaCode<>'0000')
SET @sql = @sql + ' AND U.AreaCode LIKE '''+@AreaCode+'%'''

IF (@TradeType<>0)
SET @sql = @sql + ' AND U.TradeType='+CAST(@TradeType AS VARCHAR(2))

IF(@SortPostsBy=0)
SET @sql = @sql + ' ORDER BY U.CompanyId'
ELSE IF(@SortPostsBy=1)
SET @sql = @sql + ' ORDER BY U.DateTradeCreated'
ELSE IF(@SortPostsBy=5)
SET @sql = @sql + ' ORDER BY U.EndDate'
ELSE IF(@SortPostsBy=9)
SET @sql = @sql + ' ORDER BY U.DateTradeBegin'

IF(@SortOrder=1)
SET @sql = @sql + ' DESC'

print(@sql)
EXEC(@sql)

=================================================================================

[longxiaojie]

ALTER PROCEDURE ProductSetTopNGet
(
@PostApproveStatus smallint = 99, --是否审核
@UserId int = 0, --用户ID
@TopN int = 5, --显示多少条记录
@OrderBy smallint = 0, --按什么排序
@SortOrder smallint = 0, --排序主字段
@Language varchar(8)='zh-CN',

@ClassCode varchar(64) = '0000',
@AreaCode varchar(64) = '0000',
@RoleId int = 0,
@RoleType int = 0
)
AS

DECLARE @sql varchar(2000)
DECLARE @sqlTmp varchar(200)

IF(@UserId=0)
BEGIN

CREATE TABLE #PostIndexForTopN
(
CompanyId int
)


IF(@OrderBy=0)
BEGIN
ALTER TABLE #PostIndexForTopN ADD PostId int
SET @sql = 'INSERT INTO #PostIndexForTopN (PostId,CompanyId) SELECT '

IF(@SortOrder=1)
SET @sql = @sql + 'MAX'
ELSE
SET @sql = @sql + 'MIN'

SET @sql = @sql + '(NL.PostId),NL.CompanyId'
SET @sqlTmp = 'NL.PostId = TN.PostId AND NL.CompanyId = TN.CompanyId '
END
ELSE IF(@OrderBy=1)
BEGIN
ALTER TABLE #PostIndexForTopN ADD PostDate datetime
SET @sql = 'INSERT INTO #PostIndexForTopN (PostDate,CompanyId) SELECT '

IF(@SortOrder=1)
SET @sql = @sql + 'MAX'
ELSE
SET @sql = @sql + 'MIN'

SET @sql = @sql + '(NL.PostDate),NL.CompanyId'
SET @sqlTmp = 'NL.PostDate = TN.PostDate AND NL.CompanyId = TN.CompanyId '
END
ELSE IF(@OrderBy=2 )
BEGIN
ALTER TABLE #PostIndexForTopN ADD RefreshDate datetime
SET @sql = 'INSERT INTO #PostIndexForTopN (RefreshDate,CompanyId) SELECT '

IF(@SortOrder=1)
SET @sql = @sql + 'MAX'
ELSE
SET @sql = @sql + 'MIN'

SET @sql = @sql + '(NL.RefreshDate),NL.CompanyId'
SET @sqlTmp = 'NL.RefreshDate = TN.RefreshDate AND NL.CompanyId = TN.CompanyId '
END
ELSE IF(@OrderBy=3)
BEGIN
ALTER TABLE #PostIndexForTopN ADD SortOrder int

SET @sql = 'INSERT INTO #PostIndexForTopN (SortOrder,CompanyId) SELECT '

IF(@SortOrder=1)
SET @sql = @sql + 'MAX'
ELSE
SET @sql = @sql + 'MIN'

SET @sql = @sql + '(NL.SortOrder),NL.CompanyId'
SET @sqlTmp = 'NL.SortOrder = TN.SortOrder AND NL.CompanyId = TN.CompanyId '
END
ELSE IF(@OrderBy=4)
BEGIN
ALTER TABLE #PostIndexForTopN ADD TotalViews int

SET @sql = 'INSERT INTO #PostIndexForTopN (TotalViews,CompanyId) SELECT '

IF(@SortOrder=1)
SET @sql = @sql + 'MAX'
ELSE
SET @sql = @sql + 'MIN'

SET @sql = @sql + '(NL.TotalViews),NL.CompanyId'
SET @sqlTmp = 'NL.TotalViews = TN.TotalViews AND NL.CompanyId = TN.CompanyId '
END
ELSE
BEGIN
ALTER TABLE #PostIndexForTopN ADD RefreshDate datetime

SET @sql = 'INSERT INTO #PostIndexForTopN (RefreshDate,CompanyId) SELECT '

IF(@SortOrder=1)
SET @sql = @sql + 'MAX'
ELSE
SET @sql = @sql + 'MIN'

SET @sql = @sql + '(NL.RefreshDate),NL.CompanyId'
SET @sqlTmp = 'NL.RefreshDate = TN.RefreshDate AND NL.CompanyId = TN.CompanyId '
END

SET @sql = @sql + ' FROM ProductList NL,V_CompanyRoles U,AreaCode AC WHERE NL.Language = ''' + @Language + ''' AND NL.UserId = U.UserId AND U.AreaCode = AC.AreaCode AND U.Language='''+@Language+''' AND AC.Language='''+@Language+''''

IF(@PostApproveStatus<>99)
SET @sql = @sql + ' AND NL.ApproveStatus='+CAST(@PostApproveStatus AS VARCHAR(2))

IF(@RoleType=1)
SET @sql = @sql + ' AND ('+CAST(@RoleID AS VARCHAR(1))+'=0 OR U.RoleID <='+CAST(@RoleID AS VARCHAR(3))+')'
ELSE IF(@RoleType=2)
SET @sql = @sql + ' AND ('+CAST(@RoleID AS VARCHAR(1))+'=0 OR U.RoleID >='+CAST(@RoleID AS VARCHAR(3))+')'
ELSE
SET @sql = @sql + ' AND ('+CAST(@RoleID AS VARCHAR(1))+'=0 OR U.RoleID ='+CAST(@RoleID AS VARCHAR(3))+')'

IF (@ClassCode<>'' AND @ClassCode<>'0000')
BEGIN
--开始判断行业，最多可以传3个行业
SET @sql = @sql + ' AND ('
DECLARE @tmpClassCode VARCHAR(32)
SET @tmpClassCode = ''
SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',0,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' NL.ClassCode like '''+@tmpClassCode+'%'''

SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',1,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' OR NL.ClassCode like '''+@tmpClassCode+'%'''

SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',2,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' OR NL.ClassCode like '''+@tmpClassCode+'%'''

SET @sql = @sql + ')'
END

IF (@AreaCode<>'' AND @AreaCode<>'0000')
SET @sql = @sql + ' AND U.AreaCode LIKE '''+@AreaCode+'%'''

SET @sql = @sql + ' GROUP BY NL.CompanyId'

-- 读取结果到临时表
EXEC(@sql)

-- 读取结果
SET @sql = 'SELECT TOP '+CAST(@TopN AS VARCHAR(3))+' NL.*, AC.*, Country = dbo.Fn_GetAreaName(U.AreaCode,1,'''+@Language+'''),Province = dbo.Fn_GetAreaName(U.AreaCode,2,'''+@Language+'''),City = dbo.Fn_GetAreaName(U.AreaCode,3,'''+@Language+'''),Town = dbo.Fn_GetAreaName(U.AreaCode,4,'''+@Language+''') FROM ProductList NL,V_CompanyRoles U,AreaCode AC, #PostIndexForTopN TN
WHERE '+@sqlTmp+' AND NL.Language = ''' + @Language + ''' AND NL.UserId = U.UserId AND U.AreaCode = AC.AreaCode AND U.Language='''+@Language+''' AND AC.Language='''+@Language+''''

IF(@OrderBy=0)
SET @sql = @sql + ' ORDER BY TN.PostId'
ELSE IF(@OrderBy=1)
SET @sql = @sql + ' ORDER BY TN.PostDate'
ELSE IF(@OrderBy=2)
SET @sql = @sql + ' ORDER BY TN.RefreshDate'
ELSE IF(@OrderBy=3)
SET @sql = @sql + ' ORDER BY TN.SortOrder'
ELSE IF(@OrderBy=4)
SET @sql = @sql + ' ORDER BY TN.TotalViews'
ELSE
SET @sql = @sql + ' ORDER BY TN.RefreshDate'

IF(@SortOrder=1)
SET @sql = @sql + ' DESC'

-- 读取结果
Print(@sql)
EXEC(@sql)


DROP TABLE #PostIndexForTopN
END
ELSE
BEGIN

--读取我的TopN
SET @sql = 'SELECT TOP '+STR(@TopN)+' NL.*, AC.*, Country = dbo.Fn_GetAreaName(U.AreaCode,1,'''+@Language+'''),Province = dbo.Fn_GetAreaName(U.AreaCode,2,'''+@Language+'''),City = dbo.Fn_GetAreaName(U.AreaCode,3,'''+@Language+'''),Town = dbo.Fn_GetAreaName(U.AreaCode,4,'''+@Language+''') FROM ProductList NL,V_CompanyRoles U,AreaCode AC WHERE
NL.Language = ''' + @Language + ''' AND NL.UserId = U.UserId AND U.AreaCode = AC.AreaCode AND U.Language='''+@Language+''' AND AC.Language='''+@Language+''''

SET @sql = @sql + ' AND NL.UserId='+STR(@UserId)

IF(@PostApproveStatus<>99)
SET @sql = @sql + ' AND NL.ApproveStatus='+STR(@PostApproveStatus)

IF (@ClassCode<>'' AND @ClassCode<>'0000')
BEGIN
--开始判断行业，最多可以传3个行业
SET @sql = @sql + ' AND ('
DECLARE @tmpClassCode1 VARCHAR(32)
SET @tmpClassCode1 = ''
SET @tmpClassCode1 = dbo.Fn_SplitSubString(@ClassCode,',',0,default)
IF @tmpClassCode1 <>''
SET @sql = @sql + ' NL.ClassCode like '''+@tmpClassCode1+'%'''

SET @tmpClassCode1 = dbo.Fn_SplitSubString(@ClassCode,',',1,default)
IF @tmpClassCode1 <>''
SET @sql = @sql + ' OR NL.ClassCode like '''+@tmpClassCode1+'%'''

SET @tmpClassCode1 = dbo.Fn_SplitSubString(@ClassCode,',',2,default)
IF @tmpClassCode1 <>''
SET @sql = @sql + ' OR NL.ClassCode like '''+@tmpClassCode1+'%'''

SET @sql = @sql + ')'
END

IF (@AreaCode<>'' AND @AreaCode<>'0000')
SET @sql = @sql + ' AND U.AreaCode LIKE '''+@AreaCode+'%'''

IF(@OrderBy=0)
SET @sql = @sql + ' ORDER BY NL.PostId'
ELSE IF(@OrderBy=1)
SET @sql = @sql + ' ORDER BY NL.PostDate'
ELSE IF(@OrderBy=2)
SET @sql = @sql + ' ORDER BY NL.RefreshDate'
ELSE IF(@OrderBy=3)
SET @sql = @sql + ' ORDER BY NL.SortOrder'
ELSE IF(@OrderBy=4)
SET @sql = @sql + ' ORDER BY NL.TotalViews'
ELSE
SET @sql = @sql + ' ORDER BY NL.RefreshDate'

IF(@SortOrder=1)
SET @sql = @sql + ' DESC'

-- 读取结果
Print(@sql)
EXEC(@sql)
END

[longxiaojie]

ALTER PROCEDURE TradeClassListGet
(
/*测试：
exec dbo.TradeClassListGet @ClassCode = '0001', @ClassDeep = 2, @InfoType = 21, @TopN = 3, @IsClose = 0
TradeClassListGet '0000',1,100,0 ,0 TradeClassListGet '0001',1,2,0

exec dbo.TradeClassListGet @ClassCode = '0001', @ClassDeep = 0, @InfoType = 1, @TopN = 0, @IsClose = 0

SELECT TC.*,SubInfoCount = (SELECT COUNT(BL.PostId) FROM ProductList BL WHERE BL.InfoType=1 AND BL.ApproveStatus=1
AND BL.ClassCode like TC.ClassCode + '%') FROM TradeClass TC WHERE TC.ClassId>0
AND TC.IsClose=0 AND TC.ParentClassCode=0001 ORDER BY TC.ClassCode



*/
@ClassCode VarChar(20)='0000',--所属行业编码
@ClassDeep Tinyint = 1, --编码的层次(哪一层类)
@InfoType Int = 999, --信息类型(决定从哪个表中取数据)
@TopN int = 0, --取多少条
@IsClose bit = 0, --该记录是否有效.
@TradeInfoType Int = 0, --商业信息类型
@Language VarChar(8)='zh-CN'
)
AS
DECLARE @sql varchar(1000)
DECLARE @sqlInfoCount varchar(1000)

SET @sqlInfoCount = 'SubInfoCount = 0'

IF @InfoType =999 -- 无需获取信息数目
SET @sqlInfoCount = 'SubInfoCount = 0'

IF @InfoType = 1 -- 产品信息
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.PostId) FROM ProductList BL WHERE BL.Language = ''' + @Language + ''' AND BL.InfoType=1 AND BL.ApproveStatus=1 AND BL.ClassCode like TC.ClassCode + ''%'')'
IF @InfoType = 2 -- 采购信息
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.PostId) FROM BuyList BL WHERE BL.Language = ''' + @Language + ''' AND BL.ApproveStatus=1 AND BL.ClassCode like TC.ClassCode + ''%'')'
IF @InfoType = 3 -- 商品库信息
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.PostId) FROM ProductList BL WHERE BL.Language = ''' + @Language + ''' AND BL.InfoType=3 AND BL.ApproveStatus=1 AND BL.ClassCode like TC.ClassCode + ''%'')'
IF @InfoType = 4 -- 商业信息
BEGIN
IF(@TradeInfoType=0)
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.PostId) FROM InfoList BL WHERE BL.Language = ''' + @Language + ''' AND BL.ApproveStatus=1 AND BL.ClassCode like TC.ClassCode + ''%'')'
ELSE
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.PostId) FROM InfoList BL WHERE BL.TradeInfoType='+STR(@TradeInfoType)+' AND BL.Language = ''' + @Language + ''' AND BL.ApproveStatus=1 AND BL.ClassCode like TC.ClassCode + ''%'')'
END
IF @InfoType = 9 -- 材料店铺分类
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(MSL.StoreId) FROM MaterialStoreInTradeClass MSL WHERE MSL.ClassCode like TC.ClassCode + ''%'')'
IF @InfoType = 11 -- 鞋款分类
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(PL.PostId) FROM ProductList PL WHERE PL.Language = ''' + @Language + ''' AND PL.InfoType=11 AND PL.ApproveStatus=1 AND PL.ClassCode like TC.ClassCode + ''%'')'

IF @InfoType = 20 --品牌信息
--SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.PostId) FROM BrandList BL WHERE BL.ApproveStatus=1 AND LEFT(BL.ClassCode,'+CAST(@ClassDeep*4 AS VARCHAR(2))+')=TC.ClassCode)'
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.PostId) FROM BrandList BL WHERE BL.Language = ''' + @Language + ''' AND BL.ApproveStatus=1 AND BL.PostId IN (SELECT BITC.PostId FROM BrandInTradeClass BITC WHERE BITC.ClassCode like TC.ClassCode + ''%''))'

IF @InfoType = 21 -- 店铺信息
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.StoreId) FROM GoodsStoreList BL WHERE BL.Language = ''' + @Language + ''' AND BL.ApproveStatus=1 AND
BL.StoreId IN (SELECT G.StoreId FROM GoodsStoreInTradeClass G WHERE G.ClassCode like TC.ClassCode + ''%''))'

IF @InfoType = 100 -- 所有公司信息
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.CompanyId) FROM VWCompanyFullTradeProfile BL WHERE BL.Language = ''' + @Language + ''' AND BL.CompanyIntroStatus=2 and BL.CompanyId IN (SELECT TCITC.CompanyId FROM TradeCompanyInTradeClass TCITC WHERE TCITC.ClassCode like TC.ClassCode + ''%'' OR TCITC.ClassCode=TC.ParentClassCode))'

IF @InfoType = 101 -- 供应商公司信息
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.CompanyId) FROM VWCompanyFullTradeProfile BL WHERE BL.Language = ''' + @Language + ''' AND BL.CompanyIntroStatus=2 AND BL.TradeType =2 and BL.CompanyId IN (SELECT TCITC.CompanyId FROM TradeCompanyInTradeClass TCITC WHERE TCITC.ClassCode like TC.ClassCode + ''%'' OR TCITC.ClassCode=TC.ParentClassCode))'

IF @InfoType = 102 -- 采购商公司信息

SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.CompanyId) FROM VWCompanyFullTradeProfile BL WHERE BL.Language = ''' + @Language + ''' AND BL.CompanyIntroStatus=2 AND BL.TradeType =1 and BL.CompanyId IN (SELECT TCITC.CompanyId FROM TradeCompanyInTradeClass TCITC WHERE TCITC.ClassCode like TC.ClassCode + ''%'' OR TCITC.ClassCode=TC.ParentClassCode))'

IF @InfoType = 103 -- 所有鞋款公司
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.CompanyId) FROM VWCompanyFullTradeProfile BL WHERE BL.Language = ''' + @Language + ''' AND (BL.UserAdditionPermission & 0x400000000000000) = 0x400000000000000 AND BL.CompanyId IN (SELECT TCITC.CompanyId FROM TradeCompanyInTradeClass TCITC WHERE TCITC.ClassCode like TC.ClassCode + ''%'' OR TCITC.ClassCode=TC.ParentClassCode))'

IF @InfoType = 104 -- 协会组织信息
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.CompanyId) FROM VWCompanyFullTradeProfile BL WHERE BL.Language = ''' + @Language + ''' AND BL.CompanyIntroStatus=2 AND BL.TradeType =5 and BL.CompanyId IN (SELECT TCITC.CompanyId FROM TradeCompanyInTradeClass TCITC WHERE TCITC.ClassCode like TC.ClassCode + ''%'' OR TCITC.ClassCode=TC.ParentClassCode))'

IF @InfoType = 105 -- 专业市场信息
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.CompanyId) FROM VWCompanyFullTradeProfile BL WHERE BL.Language = ''' + @Language + ''' AND BL.CompanyIntroStatus=2 AND BL.TradeType =6 and BL.CompanyId IN (SELECT TCITC.CompanyId FROM TradeCompanyInTradeClass TCITC WHERE TCITC.ClassCode like TC.ClassCode + ''%'' OR TCITC.ClassCode=TC.ParentClassCode))'

IF @InfoType = 106 -- 贸易商信息
SET @sqlInfoCount = 'SubInfoCount = (SELECT COUNT(BL.CompanyId) FROM VWCompanyFullTradeProfile BL WHERE BL.Language = ''' + @Language + ''' AND BL.CompanyIntroStatus=2 AND BL.TradeType =4 and BL.CompanyId IN (SELECT TCITC.CompanyId FROM TradeCompanyInTradeClass TCITC WHERE TCITC.ClassCode like TC.ClassCode + ''%'' OR TCITC.ClassCode=TC.ParentClassCode))'

IF @TopN = 0
SET @sql = 'SELECT TC.*,'+@sqlInfoCount+' FROM TradeClass TC WHERE TC.Language = ''' + @Language + ''''
ELSE
SET @sql = 'SELECT TOP '+STR(@TopN)+' TC.*, '+@sqlInfoCount+' FROM TradeClass TC WHERE TC.Language = ''' + @Language + ''''

IF @IsClose = 1
SET @sql = @sql + ' AND TC.IsClose=1'
ELSE
SET @sql = @sql + ' AND TC.IsClose=0'

IF @ClassDeep=0
BEGIN
IF @ClassCode='' OR @ClassCode='0000'
SET @sql = @sql + ' AND TC.ClassDeep=1 AND TC.Language = ''' + @Language + ''''
ELSE
SET @sql = @sql + ' AND TC.ParentClassCode=''' + @ClassCode + ''' AND TC.Language = ''' + @Language + ''''
END
ELSE
BEGIN
IF @ClassCode='' OR @ClassCode='0000'
SET @sql = @sql + ' AND TC.ClassDeep=' + cast(@ClassDeep as varchar(1)) + ' AND TC.Language = ''' + @Language + ''''
ELSE
SET @sql = @sql + ' AND TC.ClassDeep=' + cast(@ClassDeep as varchar(1)) + ' AND TC.Language = ''' + @Language + ''' AND TC.ClassCode like '''+@ClassCode+'%'''
END

SET @sql = @sql + ' ORDER BY TC.ClassCode'

EXEC(@sql)

[longxiaojie]

ALTER PROCEDURE TradeInfoSet_TopN_Get
(
@PostApproveStatus smallint = 99, --是否审核
@UserId int = 0, --用户ID
@TopN int = 5, --显示多少条记录
@OrderBy smallint = 0, --按什么排序
@SortOrder smallint = 0, --排序主字段
@Language varchar(8)='zh-CN',
@TradeInfoType smallint = 0,
@TradeTypeDirection smallint = 0,
@TradeType smallint = 0,

@ClassCode varchar(64) = '0000',
@AreaCode varchar(64) = '0000',
@RoleId int = 0,
@RoleType int = 0
)
AS

DECLARE @sql varchar(1000)


SET @sql = 'SELECT TOP '+CAST(@TopN AS VARCHAR(3))+' NL.*,AC.*,Country = dbo.Fn_GetAreaName(U.AreaCode,1,'''+@Language+'''),Province = dbo.Fn_GetAreaName(U.AreaCode,2,'''+@Language+'''),City = dbo.Fn_GetAreaName(U.AreaCode,3,'''+@Language+'''),Town = dbo.Fn_GetAreaName(U.AreaCode,4,'''+@Language+''') FROM InfoList NL,V_CompanyRoles U,AreaCode AC
WHERE NL.Language = ''' + @Language + ''' AND NL.UserId = U.UserId AND U.AreaCode = AC.AreaCode AND U.Language='''+@Language+''' AND AC.Language='''+@Language+''''

IF(@UserId<>0)
SET @sql = @sql + ' AND NL.UserId='+CAST(@UserId AS VARCHAR(12))

IF(@PostApproveStatus<>99)
SET @sql = @sql + ' AND NL.ApproveStatus='+CAST(@PostApproveStatus AS VARCHAR(2))

IF(@TradeInfoType<>0)
SET @sql = @sql + ' AND NL.TradeInfoType='+CAST(@TradeInfoType AS VARCHAR(2))

IF(@TradeTypeDirection<>0)
SET @sql = @sql + ' AND NL.TradeTypeDirection='+CAST(@TradeTypeDirection AS VARCHAR(2))

IF(@TradeType<>0)
SET @sql = @sql + ' AND U.TradeType='+STR(@TradeType)

IF(@RoleType=1)
SET @sql = @sql + ' AND ('+STR(@RoleID)+'=0 OR U.RoleID <='+CAST(@RoleID AS VARCHAR(3))+')'
ELSE IF(@RoleType=2)
SET @sql = @sql + ' AND ('+STR(@RoleID)+'=0 OR U.RoleID >='+CAST(@RoleID AS VARCHAR(3))+')'
ELSE
SET @sql = @sql + ' AND ('+STR(@RoleID)+'=0 OR U.RoleID ='+CAST(@RoleID AS VARCHAR(3))+')'

IF (@ClassCode<>'' AND @ClassCode<>'0000')
BEGIN
--开始判断行业，最多可以传3个行业
SET @sql = @sql + ' AND ('
DECLARE @tmpClassCode VARCHAR(32)
SET @tmpClassCode = ''
SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',0,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' NL.ClassCode like '''+@tmpClassCode+'%'''

SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',1,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' OR NL.ClassCode like '''+@tmpClassCode+'%'''

SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',2,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' OR NL.ClassCode like '''+@tmpClassCode+'%'''

SET @sql = @sql + ')'
END

IF (@AreaCode<>'' AND @AreaCode<>'0000')
SET @sql = @sql + ' AND U.AreaCode LIKE '''+@AreaCode+'%'''

IF(@OrderBy=0)
SET @sql = @sql + ' ORDER BY NL.PostId'
ELSE IF(@OrderBy=1)
SET @sql = @sql + ' ORDER BY NL.PostDate'
ELSE IF(@OrderBy=2)
SET @sql = @sql + ' ORDER BY NL.RefreshDate'
ELSE IF(@OrderBy=3)
SET @sql = @sql + ' ORDER BY NL.SortOrder'
ELSE IF(@OrderBy=4)
SET @sql = @sql + ' ORDER BY NL.TotalViews'
ELSE
SET @sql = @sql + ' ORDER BY NL.RefreshDate'

IF(@SortOrder=1)
SET @sql = @sql + ' DESC'


--SELECT @sql
EXEC(@sql)

[子陌红尘]

效率不是由存储过程单方面决定的，跟数据库表/索引的设计等方方面面密切关联。

[longxiaojie]

ALTER PROCEDURE BuyPostSet_TopN_Get
(
@PostApproveStatus smallint = 99, --是否审核
@UserId int = 0, --用户ID
@TopN int = 5, --显示多少条记录
@OrderBy smallint = 0, --按什么排序
@SortOrder smallint = 0, --排序主字段
@Language varchar(8)='zh-CN',

@ClassCode varchar(64) = '0000',
@AreaCode varchar(64) = '0000',
@RoleId int = 0,
@RoleType int = 0
)
AS

DECLARE @sql varchar(1000)


SET @sql = 'SELECT TOP '+CAST(@TopN AS VARCHAR(3))+' NL.*,AC.*,Country = dbo.Fn_GetAreaName(U.AreaCode,1,'''+@Language+'''),Province = dbo.Fn_GetAreaName(U.AreaCode,2,'''+@Language+'''),City = dbo.Fn_GetAreaName(U.AreaCode,3,'''+@Language+'''),Town = dbo.Fn_GetAreaName(U.AreaCode,4,'''+@Language+''') FROM BuyList NL,V_CompanyRoles U,AreaCode AC
WHERE NL.Language = ''' + @Language + ''' AND NL.UserId = U.UserId AND U.AreaCode = AC.AreaCode AND U.Language='''+@Language+''' AND AC.Language='''+@Language+''''

IF(@UserId<>0)
SET @sql = @sql + ' AND NL.UserId='+CAST(@UserId AS VARCHAR(12))

IF(@PostApproveStatus<>99)
SET @sql = @sql + ' AND NL.ApproveStatus='+CAST(@PostApproveStatus AS VARCHAR(2))

IF(@RoleType=1)
SET @sql = @sql + ' AND ('+CAST(@RoleID AS VARCHAR(1))+'=0 OR U.RoleID <='+CAST(@RoleID AS VARCHAR(3))+')'
ELSE IF(@RoleType=2)
SET @sql = @sql + ' AND ('+CAST(@RoleID AS VARCHAR(1))+'=0 OR U.RoleID >='+CAST(@RoleID AS VARCHAR(3))+')'
ELSE
SET @sql = @sql + ' AND ('+CAST(@RoleID AS VARCHAR(1))+'=0 OR U.RoleID ='+CAST(@RoleID AS VARCHAR(3))+')'

IF (@ClassCode<>'' AND @ClassCode<>'0000')
BEGIN
--开始判断行业，最多可以传3个行业
SET @sql = @sql + ' AND ('
DECLARE @tmpClassCode VARCHAR(32)
SET @tmpClassCode = ''
SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',0,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' NL.ClassCode like '''+@tmpClassCode+'%'''

SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',1,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' OR NL.ClassCode like '''+@tmpClassCode+'%'''

SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',2,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' OR NL.ClassCode like '''+@tmpClassCode+'%'''

SET @sql = @sql + ')'
END

IF (@AreaCode<>'' AND @AreaCode<>'0000')
SET @sql = @sql + ' AND U.AreaCode LIKE '''+@AreaCode+'%'''

IF(@OrderBy=0)
SET @sql = @sql + ' ORDER BY NL.PostId'
ELSE IF(@OrderBy=1)
SET @sql = @sql + ' ORDER BY NL.PostDate'
ELSE IF(@OrderBy=2)
SET @sql = @sql + ' ORDER BY NL.RefreshDate'
ELSE IF(@OrderBy=3)
SET @sql = @sql + ' ORDER BY NL.SortOrder'
ELSE IF(@OrderBy=4)
SET @sql = @sql + ' ORDER BY NL.TotalViews'
ELSE
SET @sql = @sql + ' ORDER BY NL.RefreshDate'

IF(@SortOrder=1)
SET @sql = @sql + ' DESC'


--SELECT @sql
EXEC(@sql)

[longxiaojie]

ALTER PROCEDURE dbo.BrandsSetGet
(
/*
Brands TOPN
*/
@OwnerCompanyId int = 0,
@TopN int = 10,
@Language varchar(8)='zh-CN',

@RoleID smallint = 0,
@RoleType smallint = 0,
@ClassCode nvarchar(64) = '0000',
@AreaCode nvarchar(32) = '0000',
@SortPostsBy smallint = 0,
@SortOrder smallint = 1
)
AS

DECLARE @sql NVARCHAR(1000)

SET @sql=''

SET @sql = + 'SELECT TOP ' + CAST(@TopN AS VARCHAR(2)) +' BL.* , AC.*,
ProdcutCount = (SELECT COUNT(P1.PostId) FROM ProductList P1 WHERE P1.BrandPostId=BL.PostId AND P1.InfoType=1),
GoodsCount = (SELECT COUNT(P2.PostId) FROM ProductList P2 WHERE P2.BrandPostId=BL.PostId AND P2.InfoType=3),
Country = dbo.Fn_GetAreaName(U.AreaCode,1,'''+@Language+'''),
Province = dbo.Fn_GetAreaName(U.AreaCode,2,'''+@Language+'''),
City = dbo.Fn_GetAreaName(U.AreaCode,3,'''+@Language+'''),
Town = dbo.Fn_GetAreaName(U.AreaCode,4,'''+@Language+''')
FROM
BrandList BL (nolock),
V_CompanyRoles U ,
AreaCode AC
WHERE
BL.Language = ''' + @Language + ''' AND
U.Language = ''' + @Language + ''' AND
AC.Language = ''' + @Language + ''' AND
BL.ApproveStatus = 1 AND
BL.UserId = U.UserId AND
U.AreaCode = AC.AreaCode'

IF(@RoleID>0)
BEGIN
IF(@RoleType=1)
SET @sql = @sql + ' AND U.RoleID <='+CAST(@RoleID AS VARCHAR(3))
ELSE IF(@RoleType=2)
SET @sql = @sql + ' AND U.RoleID >='+CAST(@RoleID AS VARCHAR(3))
ELSE
SET @sql = @sql + ' AND U.RoleID ='+CAST(@RoleID AS VARCHAR(3))
END

IF(@OwnerCompanyId>0)
SET @sql = @sql + ' AND U.CompanyId IN (SELECT UTU.RegisterCompanyId FROM UserToUser UTU WHERE OwnerCompanyId='+CAST(@OwnerCompanyId AS VARCHAR(16))

IF (@ClassCode<>'' AND @ClassCode<>'0000')
BEGIN
--开始判断行业，最多可以传3个行业
SET @sql = @sql + ' AND ('
DECLARE @tmpClassCode VARCHAR(32)
SET @tmpClassCode = ''
SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',0,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' BL.PostId in (SELECT TCITC.PostId FROM BrandInTradeClass TCITC WHERE TCITC.ClassCode like '''+@tmpClassCode+'%'' OR TCITC.ClassCode=(SELECT TC.ParentClassCode FROM TradeClass TC WHERE TC.ClassCode='''+@tmpClassCode+''' AND TC.Language='''+@Language+'''))'

SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',1,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' OR BL.PostId in (SELECT TCITC.PostId FROM BrandInTradeClass TCITC WHERE TCITC.ClassCode like '''+@tmpClassCode+'%'' OR TCITC.ClassCode=(SELECT TC.ParentClassCode FROM TradeClass TC WHERE TC.ClassCode='''+@tmpClassCode+''' AND TC.Language='''+@Language+'''))'

SET @tmpClassCode = dbo.Fn_SplitSubString(@ClassCode,',',2,default)
IF @tmpClassCode <>''
SET @sql = @sql + ' OR BL.PostId in (SELECT TCITC.PostId FROM BrandInTradeClass TCITC WHERE TCITC.ClassCode like '''+@tmpClassCode+'%'' OR TCITC.ClassCode=(SELECT TC.ParentClassCode FROM TradeClass TC WHERE TC.ClassCode='''+@tmpClassCode+''' AND TC.Language='''+@Language+'''))'

SET @sql = @sql + ')'
END

IF (@AreaCode<>'' AND @AreaCode<>'0000')
SET @sql = @sql + ' AND U.AreaCode LIKE '''+@AreaCode+'%'''


IF(@SortPostsBy=1)
SET @sql = @sql + ' ORDER BY BL.PostDate'
ELSE IF(@SortPostsBy=2)
SET @sql = @sql + ' ORDER BY BL.RefreshDate'
ELSE IF(@SortPostsBy=4)
SET @sql = @sql + ' ORDER BY BL.TotalViews'


IF(@SortOrder=1)
SET @sql = @sql + ' DESC'


EXEC(@SQL)

[longxiaojie]

请大家帮我看看，这几个存储过程都是duration>1000,太慢了

[一品梅]

好累。
学习。

[longxiaojie]

嗯，我也理解，请大家给一些建议，我也可以贴出这几个表的结构都可以，比如索引方面的建议等等。

[nzperfect]

优化从单条sql开始

[longxiaojie]

不太明白，大家帮帮忙啊！

[longxiaojie]

每人愿意帮忙吗？

[dobear_0922]

[Quote=引用 4 楼 libin_ftsafe 的回复:]
效率不是由存储过程单方面决定的，跟数据库表/索引的设计等方方面面密切关联。
[/Quote]

[dobear_0922]

[Quote=引用 12 楼 longxiaojie 的回复:]
每人愿意帮忙吗？
[/Quote]

你的问题太泛了，别人怎么回答？

[ojuju10]

存储过程跟服务器硬件、软件都有关系

表结构和索引等等