Slow query using non-deterministic user defined function
Slow query using non-deterministic user defined function
Recently we worked with a customer who reported a query that used to run a few seconds in SQL Server 2000 but it never finishes in SQL Server 2008 R2 following upgrade.
We went around and tried quite a few things but couldn't get SQL Server 2008 R2 to generate similar plan. Upon closer look at 2008 R2's query plan, we noticed something unusual. The plan has a warning "NO JOIN PREDICATE". What this means is that a cartesian product is introduced.
To illustrate the problem, let's use an example setup:
drop function dbo.myfunc
go
drop view v1, v2
go
drop table t1, t2
go
create table t1 (c1 int not null, c2 varchar(100))
go
create table t2 (c1 int not null, c2 varchar(100))
go
set nocount on
go
declare @i int
begin tran
select @i = 0
while (@i < 1000)
begin
insert into t1 (c1, c2) values (@i, 'a')
insert into t2 (c1, c2) values (@i, 'b')
select @i = @i + 1
end
commit tran
go
drop function dbo.myFunc
go
create function dbo.myfunc (@c1 int)
returns int
--with schemabinding
as
begin
return (@c1 * 100 )
end
go
create view v1 as select c1, c2, dbo.myfunc(c1) as c3 from t1
go
create view v2 as select c1, c2, dbo.myfunc(c1) as c3 from t2
go
Now, let's run the following query
dbcc freeproccache
go
set statistics profile on
go
-- But by pulling UDF above join in this query we actually introduce a cartesian product (NO JOIN PREDICATE)
-- UDF is called 1 million times instead of 1000 times each for the two views!
select count(*) from v1 as t1 join v2 as t2 on t1.c3 = t2.c3
go
set statistics profile off
go
The above query is very slow as illustrated in the query plan below. In the line 6 for the query plan, there is a warning "no join predicate". The join resulted in 1,000,000 rows (1,000 x 1,000 rows from each table).
In line 5, the myfunc is called 2,000,000 times (1,000,000 for computing t1.c1 and 1,000,000 for t2.c1).
This is because starting SQL Server 2005, optimizer has rule changes that will disallow non-deterministic scalar functions to be 'pushed down' in some situations (like this one).
Solution
Many times, you can simply make a function deterministic by adding schemabinding option. In the above example, re-write the function with schemabinding, it will be much faster.
From the query plan, you will no longer see that the "NO JOIN PREDICATE". The scalare UDF is pushed down right after table scan and applied only 100 times on each table.
drop function dbo.myFunc
go
create function dbo.myfunc (@c1 int)
returns int
with schemabinding
as
begin
return (@c1 * 100 )
end
Obviously, the function can be made deterministic. If you use following, the function will not be deterministic even you use schemabidning because of getdate(). In such cases, you will continue to see "NO JOIN PREDICATE" Cartesian product joins.
drop function dbo.myFunc
go
create function dbo.myfunc (@c1 int)
returns int
with schemabinding
as
begin
return (@c1 * 100 * datepart (mm,getdate()))
end
Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2013-10-23 SQL2005解密已经被加密的存储过程
2013-10-23 使用SQLServer 2008的CDC功能实现数据变更捕获