一个产品留言统计查寻的分析比较

 

　　有产品表Product(ProductId,Name,Username,AddTime...)
     留言表 Agency(AgencyId,  ProductId,  TargetUsername,IsRead...)
其中Agency.TargetUsername与Product.Username指这个产品的发布用户(以及这条留言的目标用户--不是指发留言的人),

现在要打印某一指定用户的如下列表:
 产品名称,未读留言数,总留言数

比较下面两种写法

//*******方式1:使用Agency的Targetusername

 

Select com.ProductId,com.Name,com.AddTime,
       sum(
       case rev.IsRead
            When 1 Then 1
            Else 0
       End )
           As Readed,
       sum(
       Case rev.IsRead
            When 0 Then 1
            Else 0
       End )
         As UnReaded,
        count(rev.ProductId) as Amount

From
 Agency as rev
 inner join
 Product as com on rev.ProductId=com.ProductId
 Where  rev.TargetUsername='0576sy.cn'
 Group By com.ProductId,com.Name,com.AddTime,com.OrderId
 Order By com.OrderId

//*******方式二使用Product.Username

 

Select com.ProductId,com.Name,com.AddTime,
       sum(
       case rev.IsRead
            When 1 Then 1
            Else 0
       End )
           As Readed,
       sum(
       Case rev.IsRead
            When 0 Then 1
            Else 0
       End )
         As UnReaded,
        count(rev.ProductId) as Amount

From
 Agency as rev
 inner join
 Product as com on rev.ProductId=com.ProductId
 Where  com.Username='0576sy.cn'
 Group By com.ProductId,com.Name,com.AddTime,com.OrderId
 Order By com.OrderId

//*************End

测试后发现,(Asp.net2.0,Windows2003,SQL2000,比较stopwatch)
   使用Agency.TargetUsername要比使用Product.Username作为指定用户信息过滤的条件,时间少30%左右(产品表4万条记录,留言表5万条记录),
具体分析查询分析器时发现,使用Agency.TargetUseranme时,使用的是Nested Loop 方式来实现inner join,上表是Agency(根据TargetUseranme过滤的后的记录),下表是Product,因为连接字段是productId,而ProductId是Product表的主键故内循环消耗时间比例接近零.

  使用product.Username时查询分析器显示使用 Hash Match 来实现inner join ,上表是Product,下表是Agecny,因为ProductId在Agency表中是外键故性能比较差.

 同时指定条件com.Useranme='0576sy.cn' And rev.TargetUseranme='0576sy.cn' 时,发现查寻分析器会忽略com.Useranme条件,这说明查询分析器自身的优化引擎也认可,采用rev.Targetusername,当然在Agency中引入了TargetUsername带来了数据冗余,另外时间成本降低了,空间成本却增加了.