none
Can I use "union" instead of "or" RRS feed

  • 问题

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


    郭勇成
    2010年1月20日 11:26

答案

  • 通過執行計劃找原因,做優化需要看函數的結構
    ROY WU(吳熹)
    2010年1月21日 3:10
    版主
  • 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
    2010年1月21日 15:51

全部回复

  • You can compare execution plans.
    2010年1月20日 14:15
  • yeah, I had done that and didn't find any other problem. Can you tell the work theory of sql server "or"?
    郭勇成
    2010年1月20日 15:45
  • They have same plan? Then should finish in same speed. Possible to post plans?
    2010年1月20日 17:45
  • 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.
    郭勇成
    2010年1月21日 1:16
  • 通過執行計劃找原因,做優化需要看函數的結構
    ROY WU(吳熹)
    2010年1月21日 3:10
    版主
  • 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
    2010年1月21日 15:51
  • 函数 dbo.UF_GetParentOUId
    是什么结构?可以列出来么?





    2010年1月22日 4:08