标量函数花费如此多的时间

发布于 2024-12-23 01:52:27 字数 816 浏览 5 评论 0原文

我需要有人能告诉我我错过了什么。

我在 SQL Server 2008 中有这个标量函数：

ALTER function [dbo].[SKU](@id1 int, @id2 int)
returns int
begin
return (
 SELECT  SUM(Value)
 FROM Table
 where id_1 = @id1
 and id_2 = @id2)
end

表是这样的：

 id_1      id_2    Value
 1004       1       10
 1004       1       30
 1004       2       100
 1005       1       90
 1005       1       5
 1005       1       5

如果我执行：

select [dbo].[SKU](1004,1)

它返回 40 - 没关系

select [dbo].[SKU](1004,2)

返回 100 - 好的

select [dbo].[SKU](1005,1)

返回 100 - 好的

此时一切看起来都不错，但我的表有几乎一百万行... SKU 的结果进入同一个表（更新部分）。

但我现在运行了两个小时，并且仍在运行...

我的问题：我从未见过如此耗时的查询。没关系？我错过了什么吗？

谢谢！，新年快乐！ d:

原文

I need someone who can tell me what I'm missing.

I have this scalar function in SQL Server 2008:

ALTER function [dbo].[SKU](@id1 int, @id2 int)
returns int
begin
return (
 SELECT  SUM(Value)
 FROM Table
 where id_1 = @id1
 and id_2 = @id2)
end

And the table is like this:

 id_1      id_2    Value
 1004       1       10
 1004       1       30
 1004       2       100
 1005       1       90
 1005       1       5
 1005       1       5

If I execute:

select [dbo].[SKU](1004,1)

it returns 40 - That's ok

select [dbo].[SKU](1004,2)

returns 100 - OK

select [dbo].[SKU](1005,1)

returns 100 - OK

At this point all seems ok, but my table has almost a millon rows... the result of SKU goes to the same table (update part).

But I ran it for two hours now, and is still running...

My question: I've never seen such as long time consuming query. It's ok? I'm missing something?

Thanks!, and happy new year ! D:

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

○闲身 2024-12-30 01:52:27

如果无法更改表设计或对其进行编程，一个简单的解决方案是在函数中使用的字段上创建覆盖索引。

像这样的东西

CREATE INDEX IX_TABLE_ID_1_ID_2_VALUE ON dbo.Table (id_1, id_2) INCLUDE (Value)

If changing the table design or programming to it is not an option, an easy solution would be to create a covering index on the fields you are using in your function.

Something like

CREATE INDEX IX_TABLE_ID_1_ID_2_VALUE ON dbo.Table (id_1, id_2) INCLUDE (Value)

回复收藏 0 原文

じ违心 2024-12-30 01:52:27

这不应被解释为答案，而是试图深入探讨真正的问题
目前，这就是我解释执行的操作的方式

从初始表开始

 id_1      id_2    Value  Result
 1004       1       10    NULL
 1004       1       30    NULL
 1004       2       100   NULL
 1005       1       90    NULL
 1005       1       5     NULL
 1005       1       5     NULL

更新表集结果 = dbo.SKU(1004, 2) 之后将变为

 id_1      id_2    Value  Result
 1004       1       10    40
 1004       1       30    40
 1004       2       100   40
 1005       1       90    40
 1005       1       5     40
 1005       1       5     40

更新表集结果 = dbo.SKU(1004, 1)后，这将变为

 id_1      id_2    Value  Result
 1004       1       10    100
 1004       1       30    100
 1004       2       100   100
 1005       1       90    100
 1005       1       5     100
 1005       1       5     100

更新表集结果 = 后dbo.SKU(1005, 1) 这将变成（保留）

 id_1      id_2    Value  Result
 1004       1       10    100
 1004       1       30    100
 1004       2       100   100
 1005       1       90    100
 1005       1       5     100
 1005       1       5     100

不知何故，结果除以 id_2

 id_1      id_2    Value  Result
 1004       1       10    100
 1004       1       30    100
 1004       2       100   50
 1005       1       90    100
 1005       1       5     100
 1005       1       5     100

显然，我的解释以及真正的意思 发生不匹配（至少我希望如此）。

This is not to be interpreted as an answer but an attempt to drill down to the real problem
Currently, this is as how I interpretate the actions that get executed

Starting from the initial table

 id_1      id_2    Value  Result
 1004       1       10    NULL
 1004       1       30    NULL
 1004       2       100   NULL
 1005       1       90    NULL
 1005       1       5     NULL
 1005       1       5     NULL

After update table set result = dbo.SKU(1004, 2) this would become

 id_1      id_2    Value  Result
 1004       1       10    40
 1004       1       30    40
 1004       2       100   40
 1005       1       90    40
 1005       1       5     40
 1005       1       5     40

After update table set result = dbo.SKU(1004, 1) this would become

 id_1      id_2    Value  Result
 1004       1       10    100
 1004       1       30    100
 1004       2       100   100
 1005       1       90    100
 1005       1       5     100
 1005       1       5     100

After update table set result = dbo.SKU(1005, 1) this would become (remain)

 id_1      id_2    Value  Result
 1004       1       10    100
 1004       1       30    100
 1004       2       100   100
 1005       1       90    100
 1005       1       5     100
 1005       1       5     100

and somehow after that, the result is divided by id_2

 id_1      id_2    Value  Result
 1004       1       10    100
 1004       1       30    100
 1004       2       100   50
 1005       1       90    100
 1005       1       5     100
 1005       1       5     100

Clearly, my interpretation and what really happens don't match (at least I hope so).

回复收藏 0 原文

北城半夏 2024-12-30 01:52:27

如果您不必使用函数，这可能会更快地满足您的需要。

;with sumVal
as
(
select t1.id_1, t1.id_2, SUM(t1.value) [result]
from [table] t1
group by t1.id_1, t1.id_2
)

select t2.*, s.result
from sumVal s
left join [table] t2 on s.id_1 = t2.id_1 and s.id_2 = t2.id_2

在我的测试中，它在不到 5 秒的时间内运行了超过 800,000 行。

This might get you what you need a little quicker if you don't have to use a function.

;with sumVal
as
(
select t1.id_1, t1.id_2, SUM(t1.value) [result]
from [table] t1
group by t1.id_1, t1.id_2
)

select t2.*, s.result
from sumVal s
left join [table] t2 on s.id_1 = t2.id_1 and s.id_2 = t2.id_2

It ran in less than 5 seconds on over 800,000 rows on my test.

回复收藏 0 原文

~没有更多了~