Can I use "union" instead of "or"

Question

Today When I optimize the sql, I encounter a problem. The Sql is following:
select d.OUID,c.Id,d.ArchiveDate,GETDATE(),0,0,0
 from c
 join d on c.OUId is null
 union
 select d.OUID,c.Id,d.ArchiveDate,GETDATE(),0,0,0
 from c
 join d on c.OUId = dbo.UF_GetParentOUId(d.OUID)

I run this sql and it spends about 3 senconds, and then I modify it as follow:
select d.OUID,c.Id,d.ArchiveDate,GETDATE(),0,0,0
 from c
 join d on c.OUId is null or c.OUId = dbo.UF_GetParentOUId(d.OUID)

and What happen? It takes me about 12 minutes.
I don't know Why they are so different?
Thank you for everyone tell me the reason.

---

郭勇成

Answer 1

You can compare execution plans.

Answer 2

yeah, I had done that and didn't find any other problem. Can you tell the work theory of sql server "or"?

---

郭勇成

Answer 3

They have same plan? Then should finish in same speed. Possible to post plans?

Answer 4

No, they didn't have the same plan. I mean I didn't find out the wrong or inefficient execute plan between them. Thank you for your reply.

---

郭勇成

Answer 5

通過執行計劃找原因，做優化需要看函數的結構

---

ROY WU(吳熹)

Answer 6

Today When I optimize the sql, I encounter a problem. The Sql is following:
select d.OUID,c.Id,d.ArchiveDate,GETDATE(),0,0,0
 from c
 join d on c.OUId is null
 union
 select d.OUID,c.Id,d.ArchiveDate,GETDATE(),0,0,0
 from c
 join d on c.OUId = dbo.UF_GetParentOUId(d.OUID)

I run this sql and it spends about 3 senconds, and then I modify it as follow:
select d.OUID,c.Id,d.ArchiveDate,GETDATE(),0,0,0
 from c
 join d on c.OUId is null or c.OUId = dbo.UF_GetParentOUId(d.OUID)

and What happen? It takes me about 12 minutes.
I don't know Why they are so different?
Thank you for everyone tell me the reason.

---

郭勇成

就你提供的信息无法判断应该不应该替换，把你执行的计划贴上来看看 你把union ->union all 省了排序更快

---

More: blog.csdn.net/happyflystone

Answer 7

函数 dbo.UF_GetParentOUId
是什么结构？可以列出来么？