Query Problem

I have a table with columns -- ims_num, last_name, pres_num, territory_num

I need retrieve the max pres_num grouped by territory

I got so far

select max(pres_num) from table
group by territory_num

I need to show all the data -- ims_num, last_name, pres_num, territory_numselect *
from tableA ta
where pres_num = (select max(presnum) from tableA where territory_num = ta.territory_num)|||I tired this, but it didn't work.

The problem is the the max(pres_num) of one territory can be the pres_num in another terriotory, and the pres_num might not be the max(pres_num) in that territory.

I hope that made sense.
Thanks so much for helping!|||"I hope that made sense."

nope, it didn't

it made things a lot worse

perhaps you could show some sample rows, and then an example of what the result set should be|||Consider the following example.

SQL> select * from a;

NAME DEPT AMT
---- ---- ----
EMPA DEPTA 80000
EMPB DEPTA 60000
EMPC DEPTB 80000
EMPD DEPTB 60000

SQL> select dept, max(amt)
2 from a
3 group by dept;

DEPT MAX(AMT)
---- ----
DEPTA 80000
DEPTB 80000

SQL> select *
2 from a a1
3 where a1.amt = (select max(amt) from a where dept = a1.dept);

NAME DEPT AMT
---- ---- ----
EMPA DEPTA 80000
EMPC DEPTB 80000

SQL> select a1.name, V.*
2 from a a1
3 INNER JOIN
4 (select dept, max(amt) MAX_AMT
5 from a
6 group by dept) V ON
7 V.dept = a1.dept AND
8 V.MAX_AMT = a1.amt;

NAME DEPT MAX_AMT
---- ---- ----
EMPA DEPTA 80000
EMPC DEPTB 80000

Query Problem

Hi,
I'm having a problem with a query... Asume I have the one table with two
columns
Col1 Col2
Mark 3
Clara 5
Bob 7
Clara 5
Clara 8
What I whant is to get the value of the most repeated entry in Col1 (in this
case, Clara)... any ideas?
ThanksHere's one way:
SELECT TOP 1 WITH TIES Col1
FROM Tbl
GROUP BY Col1
ORDER BY COUNT(*) DESC
... Note I added WITH TIES -- It is possible that there can be multiple
values with the same number of rows, so you need to decide how you want to
handle that situation.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Alejandro" <Alejandro@.discussions.microsoft.com> wrote in message
news:6749A743-6C80-4A1E-8393-113079F1F8C7@.microsoft.com...
> Hi,
> I'm having a problem with a query... Asume I have the one table with two
> columns
> Col1 Col2
> Mark 3
> Clara 5
> Bob 7
> Clara 5
> Clara 8
> What I whant is to get the value of the most repeated entry in Col1 (in
this
> case, Clara)... any ideas?
> Thanks|||Thanks... It works fine...
"Alejandro" wrote:
> Hi,
> I'm having a problem with a query... Asume I have the one table with two
> columns
> Col1 Col2
> Mark 3
> Clara 5
> Bob 7
> Clara 5
> Clara 8
> What I whant is to get the value of the most repeated entry in Col1 (in this
> case, Clara)... any ideas?
> Thanks

Query performance problems with join on UDF-based computed column

We have a table with a couple of computed columns. The value of the computed column represents a foreign key reference into another table. We're seeing a major performance problem doing a query joining between the two tables with one of the columns, but not the other. In other words, this kind of query is very fast:

select * from TheTable A, FKeyTable B
where A.ComputedColumn1 = B.KeyColumn

but this one sends the CPU usage of SQL Server to 99% for a very long time:

select * from TheTable A, FKeyTable B
where A.ComputedColumn2 = B.KeyColumn

The main difference we can see that the computed column that causes problems is based on a UDF, and the other one isn't (but again, both are computed). When I look at the execution plan, the slow query shows a Nested Loop (Inner Join) with a "No Join Predicate" warning, with the estimated # of rows being 70 million (which correponds to the product of 1016 rows in TheTable and 69K rows in FKeyTable). The fast query doesn't have that warning, and shows 1016 rows (the # of rows in TheTable).

Does anyone know why the usage of a UDF would induce this horribly inefficient join behavior? Anything we can do to fix it?

This is SQL Server 2005 SP2, btw.

By default, the engine does not know anything about the udf (i.e. has no statistics). So, bad plan can be generated.

To avoid this, I suggest that you create your udf with schemabinding and persist your computed column.

|||oj,
Thanks for the suggestions. I tried changing the UDF to use schema binding, but it didn't seem to effect the query plan at all. Persisting the computed column isn't a possibility here, as its value changes over time.
|||

Scalar udf is executed once per row which tends to be the cause for horrible performance. Now that we know that the values are non-deterministic, performance hit is almost unavoidable.

Can you post some ddl+sample data (insert)+sample code. We might be able to devise a solution.

|||

I'll see if I can come up with a simple repro.

What I don't understand is why a regular inline computed column (which is also non-deterministic) performs just fine, but the UDF version performs so horribly. I tried taking the computed column, which is essentially computing a time duration by calling datediff between "now" and a datetime stored in another column of the table, and converting the exact logic into a UDF. Once I do that, it has the same (bad) performance characteristics as my original problem - a Nested Loop with a "No Join Predicate" warning, 70M rows.

BTW, actually querying the column value is quite fast. It's the join that craters the performance.

|||

Have you tried explicitly creating a statistic for the column?

e.g.

create statistics _stats on table1(compute_col) with fullscan, norecompute

|||No luck. It complained that it "cannot be used in an index or statistics or as a partition key because it is non-deterministic."|||

Kevin, please post the requested info. Don't forget the udf code, too.

Note: you can use either free tool, objectscriptr or qalite from rac4sql.net to generate ddl+sample data.

|||

Here's the scripts (as far as I can tell, there's no attachment support on the forums....bummer). This is a seriously simplified version of our tables that reproduce the issue we're having.

The Create script:

Code Snippet

CREATE FUNCTION [dbo].[ComputeFKey](@.startTS datetime)
RETURNS float
with schemabinding
AS
BEGIN

RETURN datediff(minute,@.startTS,getutcdate())/(5)*(5)
END

go

CREATE TABLE [dbo].[JoinTable](
[KeyCol] [int] NOT NULL,
[MoreData] [int] NOT NULL,
CONSTRAINT [PK_JoinTable] PRIMARY KEY CLUSTERED
(
[KeyCol] 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

CREATE TABLE [dbo].[MainTable](
[KeyCol] [int] IDENTITY(1,1) NOT NULL,
[CompCol] AS ((datediff(minute,[TSCol],getutcdate())/(5))*(5)),
[UdfCompCol] AS ([dbo].[ComputeFKey]([TSCol])),
[TSCol] [datetime] NOT NULL,
CONSTRAINT [PK_MainTable] PRIMARY KEY CLUSTERED
(
[KeyCol] 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

Script to populate the join table:

Code Snippet

declare @.Start int
declare @.KeyVal int
declare @.RowsToAdd int
declare @.MoreData int

select @.RowsToAdd = 1000

if exists (select * from JoinTable)
select @.Start = max(KeyCol) + 5 from JoinTable
else
select @.Start = 0

select @.KeyVal = @.Start

while (@.KeyVal < (@.Start + @.RowsToAdd * 5))
begin
select @.MoreData = @.KeyVal % 60
insert into JoinTable (KeyCol, MoreData) values (@.KeyVal, @.MoreData)

select @.KeyVal = @.KeyVal + 5
end

Script to populate the main table:

Code Snippet

declare @.Row int
declare @.RowsToAdd int

select @.Row = 0
select @.RowsToAdd = 1000

while (@.Row < @.RowsToAdd)
begin
insert into MainTable (TSCol) values (DateAdd("hh", -1, getutcdate()))

select @.Row = @.Row + 1
end

Queries that demonstrate the issue:

Code Snippet

-- Fast query

select A.KeyCol from MainTable A, JoinTable B where A.CompCol = B.KeyCol

-- Slow query (Nested Loop with table spool)

select A.KeyCol from MainTable A, JoinTable B where A.UdfCompCol = B.KeyCol

The second query joins against the computed column that uses the UDF, and ends up with a Nested Loop with a table spool (a million rows in the Nested loop if you use the default values of the populate scripts - 1000 rows per table).

|||

Hi Kevin!

This is probably a bug, because MS SQL 2k behaves fine in this case (the execution plans are identical).

And btw, your function and column have different data types (float and integer).

|||

Oops - that was an error in creating my simplified repro case. The actual application schema has a CAST in the computed column. Thanks for catching that (though it doesn't affect the result). Replace the UdfCompCol definition with this:

[UdfCompCol] AS (CONVERT([int],[dbo].[ComputeFKey]([TSCol]),0))

|||

Thank you for posting the requested info. It's helpful to see what you're up against.

Now, to the _bad_ news. This is actually by design. Because you use getutcdate() within your udf, you make it non-deterministic. In sql2k, it is not possible to call getdate or getutcdate in a udf. Sql2k5 has loosen up this restriction but because the udf is marked as non-deterministric, the index seek (on jointable) is not possible. Therefore, the only option left for the engine is do a full scan of the jointable. Thus is the performance difference you see.

Btw, you can see that the perf is exactly identical for inline computed column versus udf computed column in sql2k. You will have to use the getdate_view trick though.

e.g.

Code Snippet

create view _utcdate
as
select getutcdate() [dt]
go
create FUNCTION [dbo].[ComputeFKey](@.startTS datetime)
RETURNS int
--with schemabinding
AS
BEGIN

RETURN (select datediff(minute,@.startTS,dt)/(5)*(5) from _utcdate)
END

go

-- Fast query

select A.KeyCol from MainTable A, JoinTable B where A.CompCol = B.KeyCol

-- Slow query (Nested Loop with table spool)

(1 row(s) affected)

StmtText
--
|--Hash Match(Inner Join, HASH.[KeyCol])=(.[CompCol]), RESIDUAL.[KeyCol]=.[CompCol]))
|--Clustered Index Scan(OBJECT[tempdb].[dbo].[JoinTable].[PK_JoinTable] AS ))
|--Compute Scalar(DEFINE.[CompCol]=datediff(minute, MainTable.[TSCol], getutcdate)/5*5))
|--Clustered Index Scan(OBJECT[tempdb].[dbo].[MainTable].[PK_MainTable] AS ))

(4 row(s) affected)

StmtText
-

select A.KeyCol from MainTable A, JoinTable B where A.UdfCompCol = B.KeyCol

(1 row(s) affected)

StmtText
--
|--Hash Match(Inner Join, HASH.[KeyCol])=(.[UdfCompCol]), RESIDUAL.[KeyCol]=.[UdfCompCol]))
|--Clustered Index Scan(OBJECT[tempdb].[dbo].[JoinTable].[PK_JoinTable] AS ))
|--Compute Scalar(DEFINE.[UdfCompCol]=[dbo].[ComputeFKey](Convert(MainTable.[TSCol]))))
|--Clustered Index Scan(OBJECT[tempdb].[dbo].[MainTable].[PK_MainTable] AS ))

|||

I kind of figured that would be the answer. However, I still don't understand why the same computation, when done directly in a computed column (but equally non-deterministic), has good performance characteristics. It probably doesn't matter much, since I'm guessing there is no real fix here. I'm just curious.

|||

As shown in the query plan for both queries on sql2k, the index/table scan is used. This is the same as in sql2k5 with the udf.

The inline computed column is calculated differently than the udf. Although, it in itself is non-deterministic, the sql2k5 is enhanced (made smarter) to use the available statistics based on the index key to perform an index seek.

In sql2k8, you can force an index seek but it's still not going to be possible for non-deterministic udf.

|||

Hi Oj!

It is not the same as in sql2k5.

I would understand if 2k5 spools the results of the function, but I don't understand why does it spool JoinTable? It does not make any sense...

Query performance problems with join on UDF-based computed column

We have a table with a couple of computed columns. The value of the computed column represents a foreign key reference into another table. We're seeing a major performance problem doing a query joining between the two tables with one of the columns, but not the other. In other words, this kind of query is very fast:

select * from TheTable A, FKeyTable B
where A.ComputedColumn1 = B.KeyColumn

but this one sends the CPU usage of SQL Server to 99% for a very long time:

select * from TheTable A, FKeyTable B
where A.ComputedColumn2 = B.KeyColumn

The main difference we can see that the computed column that causes problems is based on a UDF, and the other one isn't (but again, both are computed). When I look at the execution plan, the slow query shows a Nested Loop (Inner Join) with a "No Join Predicate" warning, with the estimated # of rows being 70 million (which correponds to the product of 1016 rows in TheTable and 69K rows in FKeyTable). The fast query doesn't have that warning, and shows 1016 rows (the # of rows in TheTable).

Does anyone know why the usage of a UDF would induce this horribly inefficient join behavior? Anything we can do to fix it?

This is SQL Server 2005 SP2, btw.

By default, the engine does not know anything about the udf (i.e. has no statistics). So, bad plan can be generated.

To avoid this, I suggest that you create your udf with schemabinding and persist your computed column.

|||oj,
Thanks for the suggestions. I tried changing the UDF to use schema binding, but it didn't seem to effect the query plan at all. Persisting the computed column isn't a possibility here, as its value changes over time.
|||

Scalar udf is executed once per row which tends to be the cause for horrible performance. Now that we know that the values are non-deterministic, performance hit is almost unavoidable.

Can you post some ddl+sample data (insert)+sample code. We might be able to devise a solution.

|||

I'll see if I can come up with a simple repro.

What I don't understand is why a regular inline computed column (which is also non-deterministic) performs just fine, but the UDF version performs so horribly. I tried taking the computed column, which is essentially computing a time duration by calling datediff between "now" and a datetime stored in another column of the table, and converting the exact logic into a UDF. Once I do that, it has the same (bad) performance characteristics as my original problem - a Nested Loop with a "No Join Predicate" warning, 70M rows.

BTW, actually querying the column value is quite fast. It's the join that craters the performance.

|||

Have you tried explicitly creating a statistic for the column?

e.g.

create statistics _stats on table1(compute_col) with fullscan, norecompute

|||No luck. It complained that it "cannot be used in an index or statistics or as a partition key because it is non-deterministic."|||

Kevin, please post the requested info. Don't forget the udf code, too.

Note: you can use either free tool, objectscriptr or qalite from rac4sql.net to generate ddl+sample data.

|||

Here's the scripts (as far as I can tell, there's no attachment support on the forums....bummer). This is a seriously simplified version of our tables that reproduce the issue we're having.

The Create script:

Code Snippet

CREATE FUNCTION [dbo].[ComputeFKey](@.startTS datetime)
RETURNS float
with schemabinding
AS
BEGIN

RETURN datediff(minute,@.startTS,getutcdate())/(5)*(5)
END

go

CREATE TABLE [dbo].[JoinTable](
[KeyCol] [int] NOT NULL,
[MoreData] [int] NOT NULL,
CONSTRAINT [PK_JoinTable] PRIMARY KEY CLUSTERED
(
[KeyCol] 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

CREATE TABLE [dbo].[MainTable](
[KeyCol] [int] IDENTITY(1,1) NOT NULL,
[CompCol] AS ((datediff(minute,[TSCol],getutcdate())/(5))*(5)),
[UdfCompCol] AS ([dbo].[ComputeFKey]([TSCol])),
[TSCol] [datetime] NOT NULL,
CONSTRAINT [PK_MainTable] PRIMARY KEY CLUSTERED
(
[KeyCol] 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

Script to populate the join table:

Code Snippet

declare @.Start int
declare @.KeyVal int
declare @.RowsToAdd int
declare @.MoreData int

select @.RowsToAdd = 1000

if exists (select * from JoinTable)
select @.Start = max(KeyCol) + 5 from JoinTable
else
select @.Start = 0

select @.KeyVal = @.Start

while (@.KeyVal < (@.Start + @.RowsToAdd * 5))
begin
select @.MoreData = @.KeyVal % 60
insert into JoinTable (KeyCol, MoreData) values (@.KeyVal, @.MoreData)

select @.KeyVal = @.KeyVal + 5
end

Script to populate the main table:

Code Snippet

declare @.Row int
declare @.RowsToAdd int

select @.Row = 0
select @.RowsToAdd = 1000

while (@.Row < @.RowsToAdd)
begin
insert into MainTable (TSCol) values (DateAdd("hh", -1, getutcdate()))

select @.Row = @.Row + 1
end

Queries that demonstrate the issue:

Code Snippet

-- Fast query

select A.KeyCol from MainTable A, JoinTable B where A.CompCol = B.KeyCol

-- Slow query (Nested Loop with table spool)

select A.KeyCol from MainTable A, JoinTable B where A.UdfCompCol = B.KeyCol

The second query joins against the computed column that uses the UDF, and ends up with a Nested Loop with a table spool (a million rows in the Nested loop if you use the default values of the populate scripts - 1000 rows per table).

|||

Hi Kevin!

This is probably a bug, because MS SQL 2k behaves fine in this case (the execution plans are identical).

And btw, your function and column have different data types (float and integer).

|||

Oops - that was an error in creating my simplified repro case. The actual application schema has a CAST in the computed column. Thanks for catching that (though it doesn't affect the result). Replace the UdfCompCol definition with this:

[UdfCompCol] AS (CONVERT([int],[dbo].[ComputeFKey]([TSCol]),0))

|||

Thank you for posting the requested info. It's helpful to see what you're up against.

Now, to the _bad_ news. This is actually by design. Because you use getutcdate() within your udf, you make it non-deterministic. In sql2k, it is not possible to call getdate or getutcdate in a udf. Sql2k5 has loosen up this restriction but because the udf is marked as non-deterministric, the index seek (on jointable) is not possible. Therefore, the only option left for the engine is do a full scan of the jointable. Thus is the performance difference you see.

Btw, you can see that the perf is exactly identical for inline computed column versus udf computed column in sql2k. You will have to use the getdate_view trick though.

e.g.

Code Snippet

create view _utcdate
as
select getutcdate() [dt]
go
create FUNCTION [dbo].[ComputeFKey](@.startTS datetime)
RETURNS int
--with schemabinding
AS
BEGIN

RETURN (select datediff(minute,@.startTS,dt)/(5)*(5) from _utcdate)
END

go

-- Fast query

select A.KeyCol from MainTable A, JoinTable B where A.CompCol = B.KeyCol

-- Slow query (Nested Loop with table spool)

(1 row(s) affected)

StmtText
--
|--Hash Match(Inner Join, HASH.[KeyCol])=(.[CompCol]), RESIDUAL.[KeyCol]=.[CompCol]))
|--Clustered Index Scan(OBJECT[tempdb].[dbo].[JoinTable].[PK_JoinTable] AS ))
|--Compute Scalar(DEFINE.[CompCol]=datediff(minute, MainTable.[TSCol], getutcdate)/5*5))
|--Clustered Index Scan(OBJECT[tempdb].[dbo].[MainTable].[PK_MainTable] AS ))

(4 row(s) affected)

StmtText
-

select A.KeyCol from MainTable A, JoinTable B where A.UdfCompCol = B.KeyCol

(1 row(s) affected)

StmtText
--
|--Hash Match(Inner Join, HASH.[KeyCol])=(.[UdfCompCol]), RESIDUAL.[KeyCol]=.[UdfCompCol]))
|--Clustered Index Scan(OBJECT[tempdb].[dbo].[JoinTable].[PK_JoinTable] AS ))
|--Compute Scalar(DEFINE.[UdfCompCol]=[dbo].[ComputeFKey](Convert(MainTable.[TSCol]))))
|--Clustered Index Scan(OBJECT[tempdb].[dbo].[MainTable].[PK_MainTable] AS ))

|||

I kind of figured that would be the answer. However, I still don't understand why the same computation, when done directly in a computed column (but equally non-deterministic), has good performance characteristics. It probably doesn't matter much, since I'm guessing there is no real fix here. I'm just curious.

|||

As shown in the query plan for both queries on sql2k, the index/table scan is used. This is the same as in sql2k5 with the udf.

The inline computed column is calculated differently than the udf. Although, it in itself is non-deterministic, the sql2k5 is enhanced (made smarter) to use the available statistics based on the index key to perform an index seek.

In sql2k8, you can force an index seek but it's still not going to be possible for non-deterministic udf.

|||

Hi Oj!

It is not the same as in sql2k5.

I would understand if 2k5 spools the results of the function, but I don't understand why does it spool JoinTable? It does not make any sense...

Query performance problems with join on UDF-based computed column

We have a table with a couple of computed columns. The value of the computed column represents a foreign key reference into another table. We're seeing a major performance problem doing a query joining between the two tables with one of the columns, but not the other. In other words, this kind of query is very fast:

select * from TheTable A, FKeyTable B
where A.ComputedColumn1 = B.KeyColumn

but this one sends the CPU usage of SQL Server to 99% for a very long time:

select * from TheTable A, FKeyTable B
where A.ComputedColumn2 = B.KeyColumn

The main difference we can see that the computed column that causes problems is based on a UDF, and the other one isn't (but again, both are computed). When I look at the execution plan, the slow query shows a Nested Loop (Inner Join) with a "No Join Predicate" warning, with the estimated # of rows being 70 million (which correponds to the product of 1016 rows in TheTable and 69K rows in FKeyTable). The fast query doesn't have that warning, and shows 1016 rows (the # of rows in TheTable).

Does anyone know why the usage of a UDF would induce this horribly inefficient join behavior? Anything we can do to fix it?

This is SQL Server 2005 SP2, btw.

By default, the engine does not know anything about the udf (i.e. has no statistics). So, bad plan can be generated.

To avoid this, I suggest that you create your udf with schemabinding and persist your computed column.

|||oj,
Thanks for the suggestions. I tried changing the UDF to use schema binding, but it didn't seem to effect the query plan at all. Persisting the computed column isn't a possibility here, as its value changes over time.
|||

Scalar udf is executed once per row which tends to be the cause for horrible performance. Now that we know that the values are non-deterministic, performance hit is almost unavoidable.

Can you post some ddl+sample data (insert)+sample code. We might be able to devise a solution.

|||

I'll see if I can come up with a simple repro.

What I don't understand is why a regular inline computed column (which is also non-deterministic) performs just fine, but the UDF version performs so horribly. I tried taking the computed column, which is essentially computing a time duration by calling datediff between "now" and a datetime stored in another column of the table, and converting the exact logic into a UDF. Once I do that, it has the same (bad) performance characteristics as my original problem - a Nested Loop with a "No Join Predicate" warning, 70M rows.

BTW, actually querying the column value is quite fast. It's the join that craters the performance.

|||

Have you tried explicitly creating a statistic for the column?

e.g.

create statistics _stats on table1(compute_col) with fullscan, norecompute

|||No luck. It complained that it "cannot be used in an index or statistics or as a partition key because it is non-deterministic."|||

Kevin, please post the requested info. Don't forget the udf code, too.

Note: you can use either free tool, objectscriptr or qalite from rac4sql.net to generate ddl+sample data.

|||

Here's the scripts (as far as I can tell, there's no attachment support on the forums....bummer). This is a seriously simplified version of our tables that reproduce the issue we're having.

The Create script:

Code Snippet

CREATE FUNCTION [dbo].[ComputeFKey](@.startTS datetime)
RETURNS float
with schemabinding
AS
BEGIN

RETURN datediff(minute,@.startTS,getutcdate())/(5)*(5)
END

go

CREATE TABLE [dbo].[JoinTable](
[KeyCol] [int] NOT NULL,
[MoreData] [int] NOT NULL,
CONSTRAINT [PK_JoinTable] PRIMARY KEY CLUSTERED
(
[KeyCol] 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

CREATE TABLE [dbo].[MainTable](
[KeyCol] [int] IDENTITY(1,1) NOT NULL,
[CompCol] AS ((datediff(minute,[TSCol],getutcdate())/(5))*(5)),
[UdfCompCol] AS ([dbo].[ComputeFKey]([TSCol])),
[TSCol] [datetime] NOT NULL,
CONSTRAINT [PK_MainTable] PRIMARY KEY CLUSTERED
(
[KeyCol] 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

Script to populate the join table:

Code Snippet

declare @.Start int
declare @.KeyVal int
declare @.RowsToAdd int
declare @.MoreData int

select @.RowsToAdd = 1000

if exists (select * from JoinTable)
select @.Start = max(KeyCol) + 5 from JoinTable
else
select @.Start = 0

select @.KeyVal = @.Start

while (@.KeyVal < (@.Start + @.RowsToAdd * 5))
begin
select @.MoreData = @.KeyVal % 60
insert into JoinTable (KeyCol, MoreData) values (@.KeyVal, @.MoreData)

select @.KeyVal = @.KeyVal + 5
end

Script to populate the main table:

Code Snippet

declare @.Row int
declare @.RowsToAdd int

select @.Row = 0
select @.RowsToAdd = 1000

while (@.Row < @.RowsToAdd)
begin
insert into MainTable (TSCol) values (DateAdd("hh", -1, getutcdate()))

select @.Row = @.Row + 1
end

Queries that demonstrate the issue:

Code Snippet

-- Fast query

select A.KeyCol from MainTable A, JoinTable B where A.CompCol = B.KeyCol

-- Slow query (Nested Loop with table spool)

select A.KeyCol from MainTable A, JoinTable B where A.UdfCompCol = B.KeyCol

The second query joins against the computed column that uses the UDF, and ends up with a Nested Loop with a table spool (a million rows in the Nested loop if you use the default values of the populate scripts - 1000 rows per table).

|||

Hi Kevin!

This is probably a bug, because MS SQL 2k behaves fine in this case (the execution plans are identical).

And btw, your function and column have different data types (float and integer).

|||

Oops - that was an error in creating my simplified repro case. The actual application schema has a CAST in the computed column. Thanks for catching that (though it doesn't affect the result). Replace the UdfCompCol definition with this:

[UdfCompCol] AS (CONVERT([int],[dbo].[ComputeFKey]([TSCol]),0))

|||

Thank you for posting the requested info. It's helpful to see what you're up against.

Now, to the _bad_ news. This is actually by design. Because you use getutcdate() within your udf, you make it non-deterministic. In sql2k, it is not possible to call getdate or getutcdate in a udf. Sql2k5 has loosen up this restriction but because the udf is marked as non-deterministric, the index seek (on jointable) is not possible. Therefore, the only option left for the engine is do a full scan of the jointable. Thus is the performance difference you see.

Btw, you can see that the perf is exactly identical for inline computed column versus udf computed column in sql2k. You will have to use the getdate_view trick though.

e.g.

Code Snippet

create view _utcdate
as
select getutcdate() [dt]
go
create FUNCTION [dbo].[ComputeFKey](@.startTS datetime)
RETURNS int
--with schemabinding
AS
BEGIN

RETURN (select datediff(minute,@.startTS,dt)/(5)*(5) from _utcdate)
END

go

-- Fast query

select A.KeyCol from MainTable A, JoinTable B where A.CompCol = B.KeyCol

-- Slow query (Nested Loop with table spool)

(1 row(s) affected)

StmtText
--
|--Hash Match(Inner Join, HASH.[KeyCol])=(.[CompCol]), RESIDUAL.[KeyCol]=.[CompCol]))
|--Clustered Index Scan(OBJECT[tempdb].[dbo].[JoinTable].[PK_JoinTable] AS ))
|--Compute Scalar(DEFINE.[CompCol]=datediff(minute, MainTable.[TSCol], getutcdate)/5*5))
|--Clustered Index Scan(OBJECT[tempdb].[dbo].[MainTable].[PK_MainTable] AS ))

(4 row(s) affected)

StmtText
-

select A.KeyCol from MainTable A, JoinTable B where A.UdfCompCol = B.KeyCol

(1 row(s) affected)

StmtText
--
|--Hash Match(Inner Join, HASH.[KeyCol])=(.[UdfCompCol]), RESIDUAL.[KeyCol]=.[UdfCompCol]))
|--Clustered Index Scan(OBJECT[tempdb].[dbo].[JoinTable].[PK_JoinTable] AS ))
|--Compute Scalar(DEFINE.[UdfCompCol]=[dbo].[ComputeFKey](Convert(MainTable.[TSCol]))))
|--Clustered Index Scan(OBJECT[tempdb].[dbo].[MainTable].[PK_MainTable] AS ))

|||

I kind of figured that would be the answer. However, I still don't understand why the same computation, when done directly in a computed column (but equally non-deterministic), has good performance characteristics. It probably doesn't matter much, since I'm guessing there is no real fix here. I'm just curious.

|||

As shown in the query plan for both queries on sql2k, the index/table scan is used. This is the same as in sql2k5 with the udf.

The inline computed column is calculated differently than the udf. Although, it in itself is non-deterministic, the sql2k5 is enhanced (made smarter) to use the available statistics based on the index key to perform an index seek.

In sql2k8, you can force an index seek but it's still not going to be possible for non-deterministic udf.

|||

Hi Oj!

It is not the same as in sql2k5.

I would understand if 2k5 spools the results of the function, but I don't understand why does it spool JoinTable? It does not make any sense...