关于SQL一对多关系转换的效率思考（续）

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

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

 

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

Student：

ID	StuName	ClassID
1	张三	1
2	张三	2
3	李四	1
4	王五	2
5	王五	1

 

 

 

 

 

Class：

ID	ClassName
1	数学
2	语文
3	英语

 

 

 

 

 　　

 

想要获得的数据效果为

ID	ClassName	StuName
1	数学	张三，李四，王五
2	语文	张三，王五
3	英语	NULL

 

 

 

 

 

 

第三种方法：使用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中加载程序集，在查询时调用程序集

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

    /// <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：欢迎留言讨论，欢迎转载。不足之处望海涵