CODEWITHSUNDEEP
sql-server-2008functionscalar
Esselans
Esselans

Reputation: 1546

Scalar function taking so much time

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:

Upvotes: 2

Views: 1021

Answers (3)

Rory
Rory

Reputation: 49

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.

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

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)

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

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).

Upvotes: 1

Related Questions