Sql server 中Union 与 Case

 

在sql server中,我们对于一些查询,当选择某一个表的一部分与另一部分,或者两个表组合成一个临时表或者视图时,最常用的就是Union All语句了,我的写作水平不强，以下只是我做项目时经历的一个真实案例。

 

如:

Select a.Name a.ID,a.Type, 1 as From from a where a.Type=’ 本站’

Union all

Select a.Name a.ID,a.Type, 2 as From from a where a.Type= ‘其它站’

 

下面以一个 2000万数据表的建立的视图来看看同等的Union 与Case的效率.

Union 视图

----资源信息视图---

-if exists (select 1

            from sysobjects

           where id = object_id('dbo.KT_V_Resources')

            and   type = 'V')

 drop view dbo.KT_V_Resources

go

Create View KT_V_Resources

As

SELECT      SiteCode, SourceID, SourceType, SourceFlag,

FromSiteCode, FromSourceID, CreateDate, ShareStatus, FromID=(Case

         When SourceFlag='2' then FromSourceID

    when SOurceFlag='0' then SourceID

end )

ROM          dbo.KT_Resources 

go

 

当把该视图做成原子视图，再建一个文章视图或者其它视图，通过该视图与文章表，或者其它资源表联接起来的

如:

----文章视图----

if exists (select 1

            from sysobjects

           where id = object_id('dbo.KT_V_Articles')

            and   type = 'V')

   drop view dbo.KT_V_Articles

go

Create View KT_V_Articles

As

SELECT      dbo.KT_V_Resources.*, dbo.KT_Articles.CategID, dbo.KT_Articles.Title, dbo.KT_Articles.SubTitle, dbo.KT_Articles.Keywords,

                      dbo.KT_Articles.TItlePrix, dbo.KT_Articles.Logo, dbo.KT_Articles.Author, dbo.KT_Articles.[From], dbo.KT_Articles.Comment, dbo.KT_Articles.[Content],

                      dbo.KT_Articles.RefrenceUrl, dbo.KT_Articles.ViewCount, dbo.KT_Articles.CommentCount, dbo.KT_Articles.AutoPaging, dbo.KT_Articles.PageSize,

                      dbo.KT_Articles.Status, dbo.KT_Articles.CreateUser, dbo.KT_Articles.ModUser, dbo.KT_Articles.ModDate

FROM          dbo.KT_V_Resources INNER JOIN

                      dbo.KT_Articles ON dbo.KT_V_Resources.FromID = dbo.KT_Articles.SourceID AND dbo.KT_V_Resources.SourceType = 1

go

 

假如文章表共中有10万条的数据，执行查询

Select getDate();

Select * from dbo.KT_V_Articles

Select getDate();

 

时间在1分钟左右

 

2008-04-06 12:37:19.717

…..记表

2008-04-06 12:38:10.717

51秒的时间

 

但是查询

Select getDate();

Select * from dbo.KT_V_Resources

Select getDate();

时间差在3秒以后，这很正常.

 

有人看到以上表的结构时，可能会说是不是表结构有问题，或者说资源表，及文章表中的主键应该用Identity的自增字段，会不会是因为字符串做的主键在数据量大时的速度很慢.

其实用Guid做主键，跟数字做主键，速度是有一点差别，但是这不是主要的。为什么速度这么慢呢。

根据经验，修改相关表的主索引，以及建立相关其它索引。这样会不会快一点呢，经过一些修改再试，

Select getDate();

Select * from dbo.KT_V_Articles

Select getDate();

 

时间还是51秒

这是怎么回事呢？

 

首要考虑的是索引视图，或者精减原始视图(资源视图),因为索引视图确实会提高查询速度.

 

一查建索引视图的操作，要求实体字段一一对应，不用select * ,建立时加上WITH SCHEMABINDING

再试

 

Select getDate();

Select * from dbo.KT_V_Articles

Select getDate();

 

时间还是51秒

这是怎么回事呢？

 

最后把问题点归结到Union all 语句上，通过Case语句来代替.

if exists (select 1

            from sysobjects

           where id = object_id('dbo.KT_V_Resources')

            and   type = 'V')

   drop view dbo.KT_V_Resources

go

 

Create View  KT_V_Resources WITH SCHEMABINDING

As

SELECT      SiteCode, SourceID, SourceType, SourceFlag,

FromSiteCode, FromSourceID, CreateDate, ShareStatus, FromID=(Case

         When SourceFlag='1' then FromSourceID

    when SOurceFlag='0' then SourceID

end )

FROM dbo.KT_Resources

go

---聚集索引

CREATE UNIQUE CLUSTERED INDEX IV_KT_V_Resources_SourceID ON KT_V_Resources (SourceID)

Go

 

 

这一次再查询10万条数据

Select getDate();

Select * from dbo.KT_V_Articles

Select getDate();

 

时间3秒，正常

 

晕，速度快了，问题解决

 

 

总结：Union 是所有查询速度中最慢的，需求的计算也是最多的，对于单个表或者其它用于非原子视图或者查询语句时，使用Union是解决问题的最简单办法，速度也不会有太大影响。但是做为一些原始视图或者大量数据查询时，请尽量不要采用Union All ，请使用对应的Case When语句来代替。该实例来源于自己的项目中,希望给其它朋友做类似的项目时一定的帮助.