qileilove

blog已经转移至github,大家请访问 http://qaseven.github.io/

关于SQL一对多关系转换的效率思考(续)

 之前写了一篇文章关于SQL函数效率的一些测试与思考,在当中提到了将数据库中一对多关系转换为一对一关系显示的两种方法:第一种方法是在数据库中写一个函数,第二种方法为在程序中获取表Class与表Student所有数据,然后对比ClassID。

  那么除了这两种方法,还有没有更快、更好的方法呢?在这里我再介绍两种方法与大家分享、讨论

  闲话不多说,下面进入正文。还是那两张表

  Student:

  Class:

  想要获得的数据效果为

  第三种方法:使用SQL函数stuff

  SQL语句如下

SELECT C.ID, C.ClassName,stuff((select ',' + S.StuName from dbo.Student S where S.ClassID = C.ID for xml path('')),1,1,'')as stuName FROM Class C

  将第三种方法与第二种方法(在程序中获取表Class与表Student所有数据,然后对比ClassID)对比效率,输出结果如下:

  00:00:00.5497196
  00:00:00.3517834
  效率比1.562665
  =============================
  00:00:01.0181020
  00:00:00.7060913
  效率比1.441884
  =============================
  00:00:01.4912831
  00:00:01.0682834
  效率比1.395962
  =============================
  00:00:01.9636678
  00:00:01.4199062
  效率比1.382956
  =============================
  00:00:02.4391574
  00:00:01.7712431
  效率比1.377088
  =============================
  00:00:02.9111560
  00:00:02.1255719
  效率比1.369587
  =============================
  00:00:03.3923697
  00:00:02.5069699
  效率比1.353175
  =============================
  00:00:03.8671226
  00:00:02.8594541
  效率比1.352399
  =============================
  00:00:04.3314012
  00:00:03.2064415
  效率比1.350844
  =============================
  00:00:04.8019142
  00:00:03.5546490
  效率比1.350883
  =============================

 第一个时间为第二种方法的执行时间,第二个时间为第三种方法执行时间。每种方法循环了10次以确保数据准确性

  关于测试程序代码在之前的文章中有提到,改一下SQL语句就可以使用了

  数据结果显示第三种方法要优秀不少。至于为什么第三种方法快,我心里已经有了个大致的想法,不过因为太难表述了,就懒得浪费口水说了,大家记住结论就好了

  接下来介绍第四种方法:在SQL中加载程序集,在查询时调用程序集

  加载程序集的方法有些难以表述,感兴趣的朋友可以自己去查找相关资料。在此我贴出主要代码:

View Code

    /// <summary>
    /// The variable that holds the intermediate result of the concatenation
    /// </summary>
    private StringBuilder intermediateResult;

    /// <summary>
    /// Initialize the internal data structures
    /// </summary>
    public void Init()
    {
        this.intermediateResult = new StringBuilder();
    }

    /// <summary>
    /// Accumulate the next value, not if the value is null
    /// </summary>
    /// <param name="value"></param>
    public void Accumulate(SqlString value)
    {
        if (value.IsNull)
        {
            return;
        }

        this.intermediateResult.Append(value.Value).Append(',');
    }

    /// <summary>
    /// Merge the partially computed aggregate with this aggregate.
    /// </summary>
    /// <param name="other"></param>
    public void Merge(Concatenate other)
    {
        this.intermediateResult.Append(other.intermediateResult);
    }

    /// <summary>
    /// Called at the end of aggregation, to return the results of the aggregation.
    /// </summary>
    /// <returns></returns>
    public SqlString Terminate()
    {
        string output = string.Empty;
        //delete the trailing comma, if any
        if (this.intermediateResult != null
            && this.intermediateResult.Length > 0)
        {
            output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
        }

        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
        intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.intermediateResult.ToString());
    }

  这个方法比第三种方法快得不多,大概只有5%到10%的性能提升,但是这种方法十分优雅,我窃以为这种方法是解决一对多关系转换一对一方法中最好的方法

  PS:最近太懒了,都没有来写东西。罪过罪过

  再PS:想吐槽一下,最近园子里几个小妹子写的生活上的杂七杂八的东西居然引起了那么多人的追捧,而真正的技术贴却是无人问津,不得不说是一种悲哀

  再再PS:欢迎留言讨论,欢迎转载。不足之处望海涵

posted on 2012-05-24 09:35 顺其自然EVO 阅读(137) 评论(0)  编辑  收藏 所属分类: 数据库


只有注册用户登录后才能发表评论。


网站导航:
 
<2012年5月>
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789

导航

统计

常用链接

留言簿(54)

随笔分类

随笔档案

文章分类

文章档案

搜索

最新评论

阅读排行榜

评论排行榜