Thoughts on SQL one-to-many relationship conversion efficiency (continued) test and reflection on SQL function efficiency

I wrote an article about SQL function Efficiency Testing and thinking. I mentioned two ways to convert one-to-one relationship into one-to-one relationship in a database: the first method is to write a function in the database, and the second method is to obtain all the data of the table class and the table student in the program, and then compare the classid.

In addition to the two methods, is there any faster or better way? Here I will introduce two methods to share and discuss with you.

 

If you don't talk much about it, enter the text below. Or the two tables

Student:

ID	Stuname	Classid
1	Zhang San	1
2	Zhang San	2
3	Li Si	1
4	Wang Wu	2
5	Wang Wu	1

 

 

 

 

 

Class:

ID	Classname
1	Mathematics
2	Chinese
3	English

 

 

 

 

 

The desired data effect is

ID	Classname	Stuname
1	Mathematics	Zhang San, Li Si, Wang Wu
2	Chinese	Zhang San, Wang Wu
3	English	Null

 

 

 

 

 

 

Method 3: Use the SQL function stuff

The SQL statement is as follows:

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

Compare the efficiency of the third method with the second method (obtain all the data of the table class and the table student in the program, and then compare the classid). The output result is as follows:

00:00:00. 5497196
00:00:00. 3517834
Efficiency Ratio: 1.562665
==================================
00:00:01. 0181020
00:00:00. 7060913
Efficiency Ratio: 1.441884
==================================
00:00:01. 4912831
00:00:01. 0682834
Efficiency Ratio: 1.395962
==================================
00:00:01. 9636678
00:00:01. 4199062
Efficiency Ratio: 1.382956
==================================
00:00:02. 4391574
00:00:01. 7712431
Efficiency Ratio: 1.377088
==================================
00:00:02. 9111560
00:00:02. 1255719
Efficiency Ratio: 1.369587
==================================
00:00:03. 3923697
00:00:02. 5069699
Efficiency Ratio: 1.353175
==================================
00:00:03. 8671226
00:00:02. 8594541
Efficiency Ratio: 1.352399
==================================
00:00:04. 3314012
00:00:03. 2064415
Efficiency Ratio: 1.350844
==================================
00:00:04. 8019142
00:00:03. 5546490
Efficiency Ratio: 1.350883
==================================

 

The first time is the execution time of the second method, and the second time is the execution time of the third method. Each method loops through 10 times to ensure data accuracy

As mentioned in previous articles, you can change the SQL statement to use it.

 

The data results show that the third method is much better. As for why the third method is fast, I already have a general idea in my mind, but it is too difficult to express it, so I am too lazy to waste my mouth and I just want to remember the conclusion.

 

Next, we will introduce the fourth method: load the Assembly in SQL and call the Assembly during query.

The method for loading the assembly is difficult to express. Interested friends can find relevant information on their own. Here I post the main code:

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());
    }

This method is not much faster than the third method. It only improves the performance by 5% to 10%, but this method is very elegant, I think this method is the best way to solve the one-to-many relationship conversion method.

PS: I am too lazy recently and have never come to the blog Park to write anything. Sin

PS: I want to talk about it. Recently, the miscellaneous things in my life written by a few sisters in the garden have aroused the pursuit of so many people. However, the true technical stickers are unattended, I have to say it is a sorrow.

PS: You are welcome to leave a message for discussion. You are welcome to repost it. Hope haihan