多表联合查询,求提高存储过程效率,有远程连接供测试优化 本帖最后由 weicongjin 于 2013-05-26 11:16:21 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 USE [Jiagetu]GOCREATE NONCLUSTERED INDEX Noncluster_Products_Include_IDON [dbo].[Products] ([ShopId],[State])INCLUDE ([Id])GOALTER TABLE dbo.Prices DROP CONSTRAINT PK_PricesGODROP INDEX IX_Prices ON dbo.Prices goCREATE CLUSTERED INDEX Clustered_Prices ON dbo.Prices (pid,[time] DESC ) GOALTER TABLE dbo.Prices ADD CONSTRAINT PK_Prices PRIMARY KEY NONCLUSTERED ( Id ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO用了上面的语句之后:PageCount Counts 耗时----------- ----------- -----------253 5307 3080速度降了一倍,如果你在代码里面加上where条件能降得更多。不过我没看代码,就帮你调了一下索引 这么多的表,如何把记录组合起来 把远程服务器中一个表的数据备份到本地服务器 如何创建表 复合索引使用的原则是什么? 数据库脱机提示错误 为什么不能从视图删除数据? sql 2000不能启动 SQL2005备份到问题。 MS-SQL 增加2列分组问题. SQL问题 求SQL语句 SQL 存储过程 多个数据库问题
USE [Jiagetu]
GO
CREATE NONCLUSTERED INDEX Noncluster_Products_Include_ID
ON [dbo].[Products] ([ShopId],[State])
INCLUDE ([Id])
GOALTER TABLE dbo.Prices
DROP CONSTRAINT PK_Prices
GODROP INDEX IX_Prices ON dbo.Prices
goCREATE CLUSTERED INDEX Clustered_Prices ON dbo.Prices (pid,[time] DESC )
GOALTER TABLE dbo.Prices ADD CONSTRAINT
PK_Prices PRIMARY KEY NONCLUSTERED
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO用了上面的语句之后:
PageCount Counts 耗时
----------- ----------- -----------
253 5307 3080
速度降了一倍,如果你在代码里面加上where条件能降得更多。不过我没看代码,就帮你调了一下索引