请问以下例子的存储过程的运行时间太长，该如何优化？

iamchuang 2009-04-14 12:45:52
1、实际软件设计应用中，使用下面的语句建立表XSChuHuoDan_Items
CREATE TABLE [dbo].[XSChuHuoDan_Items](
[SubClassID] [int] NOT NULL,
[DanHao] [nvarchar](16) NOT NULL,
[subID] [int] NOT NULL,
[iOrderId] [int] NULL,
[Selected] [bit] NULL,
[YangPinBH] [nvarchar](20) NULL,
[YPTiaoXingMa] [nvarchar](20) NULL,
[HDTiaoXingMa] [nvarchar](20) NULL,
[KeHuHH] [nvarchar](20) NULL,
[ChangJiaBH] [nvarchar](20) NULL,
[ChangJiaHH] [nvarchar](20) NULL,
[YPPinMingCN] [nvarchar](50) NULL,
[HDPinMingCN] [nvarchar](50) NULL,
[YPPinMingEN] [nvarchar](50) NULL,
[HDPinMingEN] [nvarchar](50) NULL,
[BaoZhuangCN] [nvarchar](50) NULL,
[BaoZhuangEN] [nvarchar](50) NULL,
[DanWeiCN] [nvarchar](10) NULL,
[DanWeiEN] [nvarchar](10) NULL,
[LeiBieCN] [nvarchar](50) NULL,
[LeiBieEN] [nvarchar](50) NULL,
[LeiBieID] [nvarchar](20) NULL,
[YPCunFangWZ] [nvarchar](50) NULL,
[HDCunFangWZ] [nvarchar](50) NULL,
[YPHunZhuangFS] [nvarchar](50) NULL,
[HDHunZhuangFS] [nvarchar](50) NULL,
[YPChang] [real] NULL,
[YPKuan] [real] NULL,
[YPGao] [real] NULL,
[GGChang] [real] NULL,
[GGKuan] [real] NULL,
[GGGao] [real] NULL,
[YPTiJi] [real] NULL,
[HDTiJi] [real] NULL,
[YPCaiJi] [real] NULL,
[HDCaiJi] [real] NULL,
[YPMaoZhong] [real] NULL,
[HDMaoZhong] [real] NULL,
[YPJingZhong] [real] NULL,
[HDJingZhong] [real] NULL,
[NeiHeShuWX] [int] NULL,
[NeiHeShuNX] [int] NULL,
[NeiHeZL] [int] NULL,
[DaShu] [real] NULL,
[LiuShuiHao] [int] NULL,
[MeiJianShu] [int] NULL,
[MeiXiangShu] [int] NULL,
[ZhiShu] [int] NULL,
[JianShu] [int] NULL,
[XiangShu] [int] NULL,
[ShuLiangWX] [int] NULL,
[ShuLiangNX] [int] NULL,
[ShuLiangPP] [int] NULL,
[YPPBChangJiaWX] [smallmoney] NULL,
[THPBChangJiaWX] [smallmoney] NULL,
[HDPBChangJiaWX] [smallmoney] NULL,
[YPZheShuWX] [real] NULL,
[THZheShuWX] [real] NULL,
[HDZheShuWX] [real] NULL,
[YPTSChangJiaWX] [smallmoney] NULL,
[THTSChangJiaWX] [smallmoney] NULL,
[HDTSChangJiaWX] [smallmoney] NULL,
[YPPBDanJiaWX] [smallmoney] NULL,
[THPBDanJiaWX] [smallmoney] NULL,
[LSPBDanJiaWX] [smallmoney] NULL,
[HDPBDanJiaWX] [smallmoney] NULL,
[YPTSDanJiaWX] [smallmoney] NULL,
[THTSDanJiaWX] [smallmoney] NULL,
[LSTSDanJiaWX] [smallmoney] NULL,
[HDTSDanJiaWX] [smallmoney] NULL,
[YPPBChangJiaNX] [smallmoney] NULL,
[THPBChangJiaNX] [smallmoney] NULL,
[HDPBChangJiaNX] [smallmoney] NULL,
[YPZheShuNX] [real] NULL,
[THZheShuNX] [real] NULL,
[HDZheShuNX] [real] NULL,
[YPTSChangJiaNX] [smallmoney] NULL,
[THTSChangJiaNX] [smallmoney] NULL,
[HDTSChangJiaNX] [smallmoney] NULL,
[YPPBDanJiaNX] [smallmoney] NULL,
[THPBDanJiaNX] [smallmoney] NULL,
[LSPBDanJiaNX] [smallmoney] NULL,
[HDPBDanJiaNX] [smallmoney] NULL,
[YPTSDanJiaNX] [smallmoney] NULL,
[THTSDanJiaNX] [smallmoney] NULL,
[LSTSDanJiaNX] [smallmoney] NULL,
[HDTSDanJiaNX] [smallmoney] NULL,
[YPPPWuZhuangChangJia] [smallmoney] NULL,
[THPPWuZhuangChangJia] [smallmoney] NULL,
[HDPPWuZhuangChangJia] [smallmoney] NULL,
[YPZheShuPP] [real] NULL,
[THZheShuPP] [real] NULL,
[HDZheShuPP] [real] NULL,
[YPPPCaiZhiJia] [smallmoney] NULL,
[THPPCaiZhiJia] [smallmoney] NULL,
[HDPPCaiZhiJia] [smallmoney] NULL,
[YPPPBaoZhuangJia] [smallmoney] NULL,
[THPPBaoZhuangJia] [smallmoney] NULL,
[HDPPBaoZhuangJia] [smallmoney] NULL,
[YPPPZiZhuJia] [smallmoney] NULL,
[THPPZiZhuJia] [smallmoney] NULL,
[TYPPZiZhuJia] [smallmoney] NULL,
[LSPPZiZhuJia] [smallmoney] NULL,
[HDPPZiZhuJia] [smallmoney] NULL,
[YPJinEWX] [money] NULL,
[ZSYPJinEWX] [money] NULL,
[THJinEWX] [money] NULL,
[ZSTHJinEWX] [money] NULL,
[HDJinEWX] [money] NULL,
[ZSHDJinEWX] [money] NULL,
[YPJinENX] [money] NULL,
[ZSYPJinENX] [money] NULL,
[THJinENX] [money] NULL,
[ZSTHJinENX] [money] NULL,
[HDJinENX] [money] NULL,
[ZSHDJinENX] [money] NULL,
[YPJinEPP] [money] NULL,
[ZSYPJinEPP] [money] NULL,
[THJinEPP] [money] NULL,
[ZSTHJinEPP] [money] NULL,
[HDJinEPP] [money] NULL,
[ZSHDJinEPP] [money] NULL,
[YPBeiZhuCN] [nvarchar](500) NULL,
[YPBeiZhuEN] [nvarchar](500) NULL,
[HDBeiZhu] [nvarchar](500) NULL,
[HDBeiZhuEN] [nvarchar](500) NULL,
[HDBeiZhuPP] [nvarchar](500) NULL,
[PeiJian] [nvarchar](50) NULL,
[PeiJianJE] [money] NULL,
[PeiJianMaoZhong] [real] NULL,
[PeiJianJingZhong] [real] NULL,
[PeiJianTiJi] [real] NULL,
[PeiJianCaiJi] [real] NULL,
[PJDD] [nvarchar](200) NULL,
[PJDDJG] [smallmoney] NULL,
[PJDDSL] [int] NULL,
[PJDDJE] [money] NULL,
[PJDC] [nvarchar](50) NULL,
[PJDCJG] [smallmoney] NULL,
[PJDCSL] [int] NULL,
[PJDCJE] [money] NULL,
[PJRW] [nvarchar](50) NULL,
[PJRWJG] [smallmoney] NULL,
[PJRWSL] [int] NULL,
[PJRWJE] [money] NULL,
[PJDW] [nvarchar](50) NULL,
[PJDWJG] [smallmoney] NULL,
[PJDWSL] [int] NULL,
[PJDWJE] [money] NULL,
[PJWP] [nvarchar](50) NULL,
[PJWPJG] [smallmoney] NULL,
[PJWPSL] [int] NULL,
[PJWPJE] [money] NULL,
[PJQT] [nvarchar](50) NULL,
[PJQTJG] [smallmoney] NULL,
[PJQTSL] [int] NULL,
[PJQTJE] [money] NULL,
[PJQT0] [nvarchar](50) NULL,
[PJQT0JG] [smallmoney] NULL,
[PJQT0SL] [int] NULL,
[PJQT0JE] [money] NULL,
[PJQT1] [nvarchar](50) NULL,
[PJQT1JG] [smallmoney] NULL,
[PJQT1SL] [int] NULL,
[PJQT1JE] [money] NULL,
[PJQT2] [nvarchar](50) NULL,
[PJQT2JG] [smallmoney] NULL,
[PJQT2SL] [int] NULL,
[PJQT2JE] [money] NULL,
[PJQT3] [nvarchar](50) NULL,
[PJQT3JG] [smallmoney] NULL,
[PJQT3SL] [int] NULL,
[PJQT3JE] [money] NULL,
[PJQT4] [nvarchar](50) NULL,
[PJQT4JG] [smallmoney] NULL,
[PJQT4SL] [int] NULL,
[PJQT4JE] [money] NULL,
[PJQT5] [nvarchar](50) NULL,
[PJQT5JG] [smallmoney] NULL,
[PJQT5SL] [int] NULL,
[PJQT5JE] [money] NULL,
[PJQT6] [nvarchar](50) NULL,
[PJQT6JG] [smallmoney] NULL,
[PJQT6SL] [int] NULL,
[PJQT6JE] [money] NULL,
[PJQT7] [nvarchar](50) NULL,
[PJQT7JG] [smallmoney] NULL,
[PJQT7SL] [int] NULL,
[PJQT7JE] [money] NULL,
[PJQT8] [nvarchar](50) NULL,
[PJQT8JG] [smallmoney] NULL,
[PJQT8SL] [int] NULL,
[PJQT8JE] [money] NULL,
[GongNeng] [nvarchar](200) NULL,
[RenZheng] [nvarchar](200) NULL,
[RenZhengPic] [nvarchar](200) NULL,
[VideoUrl] [nvarchar](200) NULL,
[GengXinZT] [bit] NULL,
CONSTRAINT [PK_XSChuHuoDan_Items] PRIMARY KEY CLUSTERED
(
[SubClassID] ASC,
[DanHao] ASC,
[subID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
...全文
150 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
linfei_z 2009-04-23
  • 打赏
  • 举报
 回复
看了下。一个字，晕！忒晕～
acmilan1984 2009-04-22
  • 打赏
  • 举报
 回复
呵呵。自己在外面整合SQL语句吧。这样写等于浪费服务器资源。。。
sxdtgsh 2009-04-22
  • 打赏
  • 举报
 回复
晕了。
肥龙上天 2009-04-21
  • 打赏
  • 举报
 回复
谢谢LZ，你真的让我张见识了，一个存储过程这么多参数，
无言了~~~
南哥1207 2009-04-21
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 QQ832198 的回复:]
七八年前 我有设计开发了一个收费系统 有的单表有上千万条记录 无论是速度还是效果用户都还反映不错.
同时 有另外一个智能化部开发了一套考勤系统 几万条记录 或者可能还会更多一点吧(年代久远 记不得了)
听说索引也建了 速度还是慢得不行 统计个考勤 需要几天几夜 .
非要请我去看看 我也纳闷 没有什么把握 至那里仔细一看 原来他们在主键上建的是聚集索引,其他再没任何索引。
把主键 改为 非聚集索引 适当增加 其他索引…
[/Quote]
6楼说得好，
2、3楼的很逗啊~
iamchuang2 2009-04-14
  • 打赏
  • 举报
 回复
............(内容省略)

SELECT @PJQT8JGColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'PJQT8JG') and (UserName = @UserName)

SELECT @PJQT8SLColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'PJQT8SL') and (UserName = @UserName)

SELECT @PJQT8JEColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'PJQT8JE') and (UserName = @UserName)

SELECT @GongNengColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'GongNeng') and (UserName = @UserName)

SELECT @RenZhengColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'RenZheng') and (UserName = @UserName)

SELECT @RenZhengPicColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'RenZhengPic') and (UserName = @UserName)

SELECT @VideoUrlColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'VideoUrl') and (UserName = @UserName)

SELECT @GengXinZTColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'GengXinZT') and (UserName = @UserName)
............(内容省略)

SELECT @PJQT8JGColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'PJQT8JG') and (UserName = @UserName)

SELECT @PJQT8SLColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'PJQT8SL') and (UserName = @UserName)

SELECT @PJQT8JEColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'PJQT8JE') and (UserName = @UserName)

SELECT @GongNengColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'GongNeng') and (UserName = @UserName)

SELECT @RenZhengColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'RenZheng') and (UserName = @UserName)

SELECT @RenZhengPicColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'RenZhengPic') and (UserName = @UserName)

SELECT @VideoUrlColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'VideoUrl') and (UserName = @UserName)

SELECT @GengXinZTColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'GengXinZT') and (UserName = @UserName)

请问以上例子的存储过程的运行时间太长，该如何优化？
iamchuang 2009-04-14
  • 打赏
  • 举报
 回复
SELECT @YPTiaoXingMaColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'YPTiaoXingMa') and (UserName = @UserName)

SELECT @ChangJiaBHColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'ChangJiaBH') and (UserName = @UserName)

SELECT @HDTiaoXingMaColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'HDTiaoXingMa') and (UserName = @UserName)

SELECT @KeHuHHColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'KeHuHH') and (UserName = @UserName)

SELECT @ChangJiaHHColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'ChangJiaHH') and (UserName = @UserName)

SELECT @YPPinMingCNColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'YPPinMingCN') and (UserName = @UserName)

SELECT @HDPinMingCNColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'HDPinMingCN') and (UserName = @UserName)

SELECT @YPPinMingENColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'YPPinMingEN') and (UserName = @UserName)

SELECT @HDPinMingENColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'HDPinMingEN') and (UserName = @UserName)

SELECT @BaoZhuangCNColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'BaoZhuangCN') and (UserName = @UserName)

SELECT @BaoZhuangENColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'BaoZhuangEN') and (UserName = @UserName)

SELECT @DanWeiCNColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'DanWeiCN') and (UserName = @UserName)

SELECT @DanWeiENColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'DanWeiEN') and (UserName = @UserName)

SELECT @LeiBieCNColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'LeiBieCN') and (UserName = @UserName)

SELECT @LeiBieENColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'LeiBieEN') and (UserName = @UserName)

SELECT @LeiBieIDColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'LeiBieID') and (UserName = @UserName)

SELECT @YPCunFangWZColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'YPCunFangWZ') and (UserName = @UserName)

SELECT @HDCunFangWZColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'HDCunFangWZ') and (UserName = @UserName)

SELECT @YPHunZhuangFSColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'YPHunZhuangFS') and (UserName = @UserName)

SELECT @HDHunZhuangFSColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'HDHunZhuangFS') and (UserName = @UserName)

SELECT @YPChangColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'YPChang') and (UserName = @UserName)

SELECT @YPKuanColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'YPKuan') and (UserName = @UserName)

SELECT @YPGaoColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'YPGao') and (UserName = @UserName)

SELECT @GGChangColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'GGChang') and (UserName = @UserName)

SELECT @GGKuanColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'GGKuan') and (UserName = @UserName)

SELECT @GGGaoColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'GGGao') and (UserName = @UserName)

SELECT @YPTiJiColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'YPTiJi') and (UserName = @UserName)

SELECT @HDTiJiColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'HDTiJi') and (UserName = @UserName)

SELECT @YPCaiJiColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'YPCaiJi') and (UserName = @UserName)

SELECT @HDCaiJiColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'HDCaiJi') and (UserName = @UserName)

SELECT @YPMaoZhongColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'YPMaoZhong') and (UserName = @UserName)

SELECT @HDMaoZhongColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'HDMaoZhong') and (UserName = @UserName)

SELECT @YPJingZhongColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'YPJingZhong') and (UserName = @UserName)

SELECT @HDJingZhongColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'HDJingZhong') and (UserName = @UserName)

SELECT @NeiHeShuWXColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'NeiHeShuWX') and (UserName = @UserName)

SELECT @NeiHeShuNXColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'NeiHeShuNX') and (UserName = @UserName)

SELECT @NeiHeZLColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'NeiHeZL') and (UserName = @UserName)

SELECT @DaShuColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'DaShu') and (UserName = @UserName)

SELECT @LiuShuiHaoColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'LiuShuiHao') and (UserName = @UserName)

SELECT @MeiJianShuColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'MeiJianShu') and (UserName = @UserName)

SELECT @MeiXiangShuColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'MeiXiangShu') and (UserName = @UserName)

SELECT @ZhiShuColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'ZhiShu') and (UserName = @UserName)

SELECT @JianShuColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'JianShu') and (UserName = @UserName)

SELECT @XiangShuColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'XiangShu') and (UserName = @UserName)

SELECT @ShuLiangWXColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'ShuLiangWX') and (UserName = @UserName)

SELECT @ShuLiangNXColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'ShuLiangNX') and (UserName = @UserName)

SELECT @ShuLiangPPColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'ShuLiangPP') and (UserName = @UserName)

SELECT @YPPBChangJiaWXColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'YPPBChangJiaWX') and (UserName = @UserName)

iamchuang 2009-04-14
  • 打赏
  • 举报
 回复
3、使用下面语句建立存储过程

00:24:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[XSChuHuoDan_Items_GetColID]
@UserName nvarchar(50) ='',
@YangPinBHColID int = 0 out,
@YPTiaoXingMaColID int = 0 out,
@HDTiaoXingMaColID int = 0 out,
@KeHuHHColID int = 0 out,
@ChangJiaBHColID int = 0 out,
@ChangJiaHHColID int = 0 out,
@YPPinMingCNColID int = 0 out,
@HDPinMingCNColID int = 0 out,
@YPPinMingENColID int = 0 out,
@HDPinMingENColID int = 0 out,
@BaoZhuangCNColID int = 0 out,
@BaoZhuangENColID int = 0 out,
@DanWeiCNColID int = 0 out,
@DanWeiENColID int = 0 out,
@LeiBieCNColID int = 0 out,
@LeiBieENColID int = 0 out,
@LeiBieIDColID int = 0 out,
@YPCunFangWZColID int = 0 out,
@HDCunFangWZColID int = 0 out,
@YPHunZhuangFSColID int = 0 out,
@HDHunZhuangFSColID int = 0 out,
@YPChangColID int = 0 out,
@YPKuanColID int = 0 out,
@YPGaoColID int = 0 out,
@GGChangColID int = 0 out,
@GGKuanColID int = 0 out,
@GGGaoColID int = 0 out,
@YPTiJiColID int = 0 out,
@HDTiJiColID int = 0 out,
@YPCaiJiColID int = 0 out,
@HDCaiJiColID int = 0 out,
@YPMaoZhongColID int = 0 out,
@HDMaoZhongColID int = 0 out,
@YPJingZhongColID int = 0 out,
@HDJingZhongColID int = 0 out,
@NeiHeShuWXColID int = 0 out,
@NeiHeShuNXColID int = 0 out,
@NeiHeZLColID int = 0 out,
@DaShuColID int = 0 out,
@LiuShuiHaoColID int = 0 out,
@MeiJianShuColID int = 0 out,
@MeiXiangShuColID int = 0 out,
@ZhiShuColID int = 0 out,
@JianShuColID int = 0 out,
@XiangShuColID int = 0 out,
@ShuLiangWXColID int = 0 out,
@ShuLiangNXColID int = 0 out,
@ShuLiangPPColID int = 0 out,
@YPPBChangJiaWXColID int = 0 out,
@THPBChangJiaWXColID int = 0 out,
@HDPBChangJiaWXColID int = 0 out,
@YPZheShuWXColID int = 0 out,
@THZheShuWXColID int = 0 out,
@HDZheShuWXColID int = 0 out,
@YPTSChangJiaWXColID int = 0 out,
@THTSChangJiaWXColID int = 0 out,
@HDTSChangJiaWXColID int = 0 out,
@YPPBDanJiaWXColID int = 0 out,
@THPBDanJiaWXColID int = 0 out,
@LSPBDanJiaWXColID int = 0 out,
@HDPBDanJiaWXColID int = 0 out,
@YPTSDanJiaWXColID int = 0 out,
@THTSDanJiaWXColID int = 0 out,
@LSTSDanJiaWXColID int = 0 out,
@HDTSDanJiaWXColID int = 0 out,
@YPPBChangJiaNXColID int = 0 out,
@THPBChangJiaNXColID int = 0 out,
@HDPBChangJiaNXColID int = 0 out,
@YPZheShuNXColID int = 0 out,
@THZheShuNXColID int = 0 out,
@HDZheShuNXColID int = 0 out,
@YPTSChangJiaNXColID int = 0 out,
@THTSChangJiaNXColID int = 0 out,
@HDTSChangJiaNXColID int = 0 out,
@YPPBDanJiaNXColID int = 0 out,
@THPBDanJiaNXColID int = 0 out,
@LSPBDanJiaNXColID int = 0 out,
@HDPBDanJiaNXColID int = 0 out,
@YPTSDanJiaNXColID int = 0 out,
@THTSDanJiaNXColID int = 0 out,
@LSTSDanJiaNXColID int = 0 out,
@HDTSDanJiaNXColID int = 0 out,
@YPPPWuZhuangChangJiaColID int = 0 out,
@THPPWuZhuangChangJiaColID int = 0 out,
@HDPPWuZhuangChangJiaColID int = 0 out,
@YPZheShuPPColID int = 0 out,
@THZheShuPPColID int = 0 out,
@HDZheShuPPColID int = 0 out,
@YPPPCaiZhiJiaColID int = 0 out,
@THPPCaiZhiJiaColID int = 0 out,
@HDPPCaiZhiJiaColID int = 0 out,
@YPPPBaoZhuangJiaColID int = 0 out,
@THPPBaoZhuangJiaColID int = 0 out,
@HDPPBaoZhuangJiaColID int = 0 out,
@YPPPZiZhuJiaColID int = 0 out,
@THPPZiZhuJiaColID int = 0 out,
@TYPPZiZhuJiaColID int = 0 out,
@LSPPZiZhuJiaColID int = 0 out,
@THPPZiZhuJiaID int = 0 out,
@HDPPZiZhuJiaColID int = 0 out,
@YPJinEWXColID int = 0 out,
@ZSYPJinEWXColID int = 0 out,
@THJinEWXColID int = 0 out,
@ZSTHJinEWXColID int = 0 out,
@HDJinEWXColID int = 0 out,
@ZSHDJinEWXColID int = 0 out,
@YPJinENXColID int = 0 out,
@ZSYPJinENXColID int = 0 out,
@THJinENXColID int = 0 out,
@ZSTHJinENXColID int = 0 out,
@HDJinENXColID int = 0 out,
@ZSHDJinENXColID int = 0 out,
@YPJinEPPColID int = 0 out,
@ZSYPJinEPPColID int = 0 out,
@THJinEPPColID int = 0 out,
@ZSTHJinEPPColID int = 0 out,
@HDJinEPPColID int = 0 out,
@ZSHDJinEPPColID int = 0 out,
@YPBeiZhuCNColID int = 0 out,
@YPBeiZhuENColID int = 0 out,
@HDBeiZhuColID int = 0 out,
@HDBeiZhuENColID int = 0 out,
@HDBeiZhuPPColID int = 0 out,
@PeiJianColID int = 0 out,
@PeiJianJEColID int = 0 out,
@PeiJianMaoZhongColID int = 0 out,
@PeiJianJingZhongColID int = 0 out,
@PeiJianTiJiColID int = 0 out,
@PeiJianCaiJiColID int = 0 out,
@PJDDColID int = 0 out,
@PJDDJGColID int = 0 out,
@PJDDSLColID int=0 out,
@PJDDJEColID int = 0 out,
@PJDCColID int = 0 out,
@PJDCJGColID int = 0 out,
@PJDCSLColID int=0 out,
@PJDCJEColID int = 0 out,
@PJRWColID int = 0 out,
@PJRWJGColID int = 0 out,
@PJRWSLColID int=0 out,
@PJRWJEColID int = 0 out,
@PJDWColID int = 0 out,
@PJDWJGColID int = 0 out,
@PJDWSLColID int=0 out,
@PJDWJEColID int = 0 out,
@PJWPColID int = 0 out,
@PJWPJGColID int = 0 out,
@PJWPSLColID int=0 out,
@PJWPJEColID int = 0 out,
@PJQTColID int = 0 out,
@PJQTJGColID int = 0 out,
@PJQTSLColID int=0 out,
@PJQTJEColID int = 0 out,
@PJQT0ColID int = 0 out,
@PJQT0JGColID int = 0 out,
@PJQT0SLColID int=0 out,
@PJQT0JEColID int = 0 out,
@PJQT1ColID int = 0 out,
@PJQT1JGColID int = 0 out,
@PJQT1SLColID int=0 out,
@PJQT1JEColID int = 0 out,
@PJQT2ColID int = 0 out,
@PJQT2JGColID int = 0 out,
@PJQT2SLColID int=0 out,
@PJQT2JEColID int = 0 out,
@PJQT3ColID int = 0 out,
@PJQT3JGColID int = 0 out,
@PJQT3SLColID int=0 out,
@PJQT3JEColID int = 0 out,
@PJQT4ColID int = 0 out,
@PJQT4JGColID int = 0 out,
@PJQT4SLColID int=0 out,
@PJQT4JEColID int = 0 out,
@PJQT5ColID int = 0 out,
@PJQT5JGColID int = 0 out,
@PJQT5SLColID int=0 out,
@PJQT5JEColID int = 0 out,
@PJQT6ColID int = 0 out,
@PJQT6JGColID int = 0 out,
@PJQT6SLColID int=0 out,
@PJQT6JEColID int = 0 out,
@PJQT7ColID int = 0 out,
@PJQT7JGColID int = 0 out,
@PJQT7SLColID int=0 out,
@PJQT7JEColID int = 0 out,
@PJQT8ColID int = 0 out,
@PJQT8JGColID int = 0 out,
@PJQT8SLColID int=0 out,
@PJQT8JEColID int = 0 out,
@GongNengColID int = 0 out,
@RenZhengColID int = 0 out,
@RenZhengPicColID int = 0 out,
@VideoUrlColID int = 0 out,
@GengXinZTColID int = 0 out
AS

SELECT @YangPinBHColID = ColID
FROM vXSChuHuoDan_Items_GetColID where (COLUMN_NAME = 'YangPinBH') and (UserName = @UserName)

iamchuang 2009-04-14
  • 打赏
  • 举报
 回复
2、使用下面语句建立视图

CREATE VIEW [dbo].[vXSChuHuoDan_Items_GetColID]
AS
SELECT TOP (2000) INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, dbo.sys_GridColSetup.ColID, dbo.sys_GridColSetup.UserName
FROM INFORMATION_SCHEMA.COLUMNS LEFT OUTER JOIN
dbo.sys_GridColSetup ON INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = dbo.sys_GridColSetup.FieldName
WHERE (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'XSChuHuoDan_Items') AND (dbo.sys_GridColSetup.TableName = N'vXSChuHuoDan_Items')
ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
wlljjw 2009-04-14
  • 打赏
  • 举报
 回复
同意5楼的观点，聚集索引适合于大量重复的.... PK是唯一的,聚集索引显然不合适
QQ832198 2009-04-14
  • 打赏
  • 举报
 回复
七八年前 我有设计开发了一个收费系统 有的单表有上千万条记录 无论是速度还是效果用户都还反映不错.
同时 有另外一个智能化部开发了一套考勤系统 几万条记录 或者可能还会更多一点吧(年代久远 记不得了)
听说索引也建了 速度还是慢得不行 统计个考勤 需要几天几夜 .
非要请我去看看 我也纳闷 没有什么把握 至那里仔细一看 原来他们在主键上建的是聚集索引,其他再没任何索引。
把主键 改为 非聚集索引 适当增加 其他索引 速度马上就恢复正常了.

一、SQL上 建PK时默认是聚集索引 也不知道算不算是不是Bug 。
聚集索引适合于大量重复的.... PK是唯一的,聚集索引显然不合适

二、使用数据库有十几年了还真没设计过这样有差不多200个字段的表,
原来的表 最多的时候会有40多个字段 今天已经难找到超过 30个字段的表了.
字段太多的表 速度不会太快 其实扩展也很困难 写程序代码也难啊.

三、最怕用 拼音来做字段名. 如果可能的话 用英语吧.

四、在Create View时第一次见到 top 2000 那超过的呢 不要了啊

五、一个存储过程这么多的参数 开眼界了 如果这样 你不如得到一个select 结果好了.

数据库的设计非常重要 这样的设计 ... 我无语了.....
oracle 自动执行存储过程
当项目中在一个时间要运行一段存储过程，如在每天午夜执行存储过程，往表中插入数据。附带例子。
SQL（及存储过程）跑得太慢怎么办？
单纯的计算密集型任务涉及的数据量不大而只是计算量很大，计算量大并不是由于数据量大造成的，这样不用改变存储方式，只要实施了好的计算方法也能大幅提升性能，也就是说，可以继续在原有的存储方式（比如数据库）上使用SPL来优化性能。为了解决这个问题，SPL提供了自有的存储机制，直接采用文件系统，将数据从数据库导出到特定格式的文件中，不仅可以获得更高的IO存取效率以及文件系统灵活的管理能力，还可以充分利用自有格式的列存、有序、压缩、并行分段等数据存储优势，从而高效地发挥高性能算法效力。
基于海思3536平台为示例的嵌入式软件开发-基础篇
课程介绍:本课程从以下多个方面来给大家讲解1. 以海思3536为例，以图示的方式，从芯片丝印开始介绍此平台的能力、架构以及各个主要功能模块的使用方法、步骤;2. 嵌入式Linux系统环境、应用程序运行环境、开发环境、QT运行环境、QT开发环境的搭建过程介绍;3. 编译并运行SDK官方例程，并移植并运行QT官方的OpenGLES例子程序;4. 运行一个8路1080P60导播台程序以及一个多路4KP30解码加多画面合成4K输出的测试程序;5. 以上所讲内容所涉及的一些相关概念、知识点的讲解;6. 更多涉及本课程的资料，请邮箱索取;
Oracle PLSQL的存储过程性能优化方法
Oracle PLSQL的存储过程性能优化方法 一、影响性能常见原因 1.尽量使用存储过程，避免使用PL/SQL匿名块 存储过程创建后，oracle会对其进行语法句法分析，以编译的形式存储在数据库中，当客户端调用时，只需要发送一条调用指令，避免了匿名块在网上传送大量源代码，降低了网络通信的负担，同时因为仅在创建时编译一次，因此提升了程序运行的性能. 2.编写共享SQL语句 oracle在执行SQL语句时，在第一次解析后，会将SQL语句放在位于系统全局区SGA中的共享池中,这块内存区域可以被所有的数据库用户共
ORACLE存储过程
oracle存储过程 目录 一.什么是存储过程 二.为什么要写存储过程 三.存储过程基础 1.存储过程结构 2.存储过程语法 3.pl/sql处理存储过程 四.存储过程进阶 1.BUIK COLLECT 2.FORALL 3.pl/sql调试存储过程 4.案例实战 ...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章