group by sum 优化

[kkbac]

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
set statistics io on
SET STATISTICS time ON

SELECT
top 10
[UserID]
, sum([Score]) score
FROM
[users]
group by
UserID
order by
Score desc

500w数据.
userid, 非聚焦索引
score 非聚焦索引

时间11秒.
测试电脑配置 amd640(3.0); 8G内存;

[soaringsouth]

索引试图不支持out join吧
inner join 毕竟还少，主从表的结构比较普遍

[nzperfect]

加上时间的话，效率就没有那么高了，需要加一个索引试试：

CREATE INDEX IX_createtime_Userid_score ON users (createtime,userid) INCLUDE(score)

[kkbac]

执行时间太长. 怎么优化?

[问苍天人生几何]

-->try
select t.[UserID],sum(t.[Score]) Score
from
(
	SELECT top 10 [UserID],[Score]
	FROM [users]   
	group by UserID
	order by Score desc
) t

[问苍天人生几何]

不好意思，上面那个写的有问题

[kkbac]

[Quote=引用 3 楼 的回复:]

SQL code

-->try
select t.[UserID],sum(t.[Score]) Score
from
(
SELECT top 10 [UserID],[Score]
FROM [users]
group by UserID
order by Score desc
) t
[/Quote]
运行错误.

[kkbac]

[Quote=引用 4 楼 的回复:]

不好意思，上面那个写的有问题
[/Quote]

[人生无悔]

这还可以再优化？

[问苍天人生几何]

-->try 
SELECT top 10 [UserID] into #tmp 
FROM [users] group by UserID

select [UserID], sum([Score]) Score from [users]
where [UserID] in (select t.[UserID] from #tmp t)
order by Score desc

--drop table #tmp

[kkbac]

[Quote=引用 7 楼 的回复:]

这还可以再优化？
[/Quote]

可以速度提高点么?

[kkbac]

[Quote=引用 8 楼 的回复:]

SQL code

-->try
SELECT top 10 [UserID] into #tmp
FROM [users] group by UserID

select [UserID], sum([Score]) Score from [users]
where [UserID] in (select t.[UserID] from #tmp t)
order by Score de……
[/Quote]

这个和我原意不符吧.

[nzperfect]

发一下生成表结构的脚本看看

[问苍天人生几何]

[Quote=引用 10 楼 的回复:]
引用 8 楼 的回复:

SQL code

-->try
SELECT top 10 [UserID] into #tmp
FROM [users] group by UserID

select [UserID], sum([Score]) Score from [users]
where [UserID] in (select t.[UserID] from #tmp t)……
[/Quote]
不变个法，那我也不知道怎么优化了。

[Mr_Nice]

SELECT TOP 10
        [UserID] ,
        SUM([Score]) score
FROM    [users]
GROUP BY UserID
ORDER BY Score DESC

光看语句，应该没啥问题，关键是索引的事儿了。 LZ ctrl + L 看看这个查询，走索引没有?
另外，看看到底是什么地方慢了。

[kkbac]

CREATE TABLE [dbo].[users](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[userid] [int] NOT NULL,
	[score] [int] NOT NULL,
	[createtime] [datetime] NOT NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 
(
	[id] 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

ALTER TABLE [dbo].[users] ADD  CONSTRAINT [DF_users_userid]  DEFAULT ((0)) FOR [userid]
GO

ALTER TABLE [dbo].[users] ADD  CONSTRAINT [DF_users_score]  DEFAULT ((0)) FOR [score]
GO

ALTER TABLE [dbo].[users] ADD  CONSTRAINT [DF_users_createtime]  DEFAULT (getdate()) FOR [createtime]
GO

[kkbac]

[Quote=引用 13 楼 的回复:]

SQL code
SELECT TOP 10
[UserID] ,
SUM([Score]) score
FROM [users]
GROUP BY UserID
ORDER BY Score DESC



光看语句，应该没啥问题，关键是索引的事儿了。 LZ ctrl + L 看看这个查询，走索引没有?
另外，看看到底是什么地方慢了。
[/Quote]

时间消耗84%, 聚焦索引扫描.

[筱筱澄]

userid, 非聚焦索引
score 非聚焦索引

把这个改成 userid和score 的联合索引试试。

[kkbac]

[Quote=引用 16 楼 的回复:]

userid, 非聚焦索引
score 非聚焦索引

把这个改成 userid和score 的联合索引试试。
[/Quote]

我试试.

[kkbac]

[Quote=引用 36 楼 的回复:]

加上时间的话，效率就没有那么高了，需要加一个索引试试：

CREATE INDEX IX_createtime_Userid_score ON users (createtime,userid) INCLUDE(score)
[/Quote]
谢谢. 已经问的够多了. 先结这个帖子, 再开帖子提问.

[kkbac]

[Quote=引用 36 楼 的回复:]

加上时间的话，效率就没有那么高了，需要加一个索引试试：

CREATE INDEX IX_createtime_Userid_score ON users (createtime,userid) INCLUDE(score)
[/Quote]

加了之后速度快多鸟.