关于SQL函数效率的一些测试与思考

在项目中我们经常能遇到数据库有“一对多”的关系，比如下面两张表：

Student：

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

 

 

 

 

 

Class：

ID	ClassName
1	数学
2	语文
3	英语

 

 

 

 

 

Class-Student就这样构成了一个简单的一对多关系。当然在实际的项目中，也可以再建立一张Relation表来保存他们之间的关系，在这里为了简单，就不做Relation表了。

现在在项目中，我需要将Class表中的数据list显示，当然也想显示选择了这门课的Student的StuName。也可以说是将一对多关系转换为一对一关系。我所期望的显示格式是这样的：

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

 

 

 

 

 

 

要做到这一点并不难，大体有两种思路：
 
1、在数据库中写一个函数
2、在程序中获取表Class与表Student所有数据，然后对比ClassID
 
那么，那种方法效率比较高呢？于是我写了下面的代码来进行一个简单的测试
  View Code
    class Program
    {
        static void Main(string[] args)
        {
            Sql sql = new Sql();
            Stopwatch time1 = new Stopwatch();
            Stopwatch time2 = new Stopwatch();
            for (int j = 0; j < 10; j++)
            {
                time2.Start();
                for (int i = 0; i < 1000; i++)
                {
                    string sql1 = "select ID,[StuName],[ClassID] FROM [Student]";
                    string sql2 = " SELECT  ID,ClassName from Class";
                    List<string> item = new List<string>();
                    string bl="";
                    DataTable dt1 = sql.getData(sql1);
                    DataTable dt2 = sql.getData(sql2);
                    foreach (DataRow dtRow2 in dt2.Rows)
                    {
                        foreach (DataRow dtRow1 in dt1.Rows)
                        {
                            if (dtRow1["ClassID"].ToString() == dtRow2["ID"].ToString())
                            {
                                 bl+=dtRow1["StuName"].ToString()+",";
                            }   
                        }
                        item.Add(bl);
                        bl = "";
                    }
                }
                time2.Stop();
                Console.WriteLine(time2.Elapsed.ToString());
 
                time1.Start();
                for (int i = 0; i < 1000; i++)
                {
                    string sql3 = "SELECT C.ID, C.ClassName, dbo.f_getStuNamesByClassID(C.ID)as stuName FROM Class C";
                    DataTable dt = sql.getData(sql3);
                }
                time1.Stop();
                Console.WriteLine(time1.Elapsed.ToString());
 
                float index = (float)time1.Elapsed.Ticks / (float)time2.Elapsed.Ticks;
                Console.WriteLine("效率比" + index.ToString());
                Console.WriteLine("=============================");
            }
 
            Console.ReadLine();
        }
    }
复制代码
  View Code
    class Sql
    {
        public DataTable getData(string sql)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Test;User Id=sa;Password=1;";
            SqlCommand comm = new SqlCommand(sql, conn);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds, "ds");
            conn.Close();
            return ds.Tables[0];
        }
    }
复制代码
  View Code
--根据课程ID，返回选此课程的学生的名字，以逗号隔开
ALTER function [dbo].[f_getStuNamesByClassID] (@ID int)
RETURNS nvarchar(50)
begin
    declare @Result nvarchar(50);
    declare @stuName nvarchar(50);
    Set @Result='';
 
    declare cur cursor for
    (
        SELECT S.StuName FROM Class C
        LEFT JOIN Student S ON C.ID=S.ClassID
        WHERE C.ID=@ID
    )
    open cur;
    fetch next from cur into @stuName;
    while(@@fetch_status=0)
    begin
        set @Result=@Result+@stuName+',';
        fetch next from cur into @stuName;
    end;
--去除最后多余的一个逗号
    IF @Result <> ''
        SET @Result=SUBSTRING(@Result, 1, LEN(@Result)-1);
    ELSE
        SET @Result=NULL;
    return @Result;
en
复制代码
 
测试结果如下：
00:00:00.5466790
00:00:00.7753704
效率比1.418329
=============================
00:00:01.0251996
00:00:01.5594629
效率比1.521131
=============================
00:00:01.5124349
00:00:02.3286227
效率比1.539652
=============================
00:00:01.9882458
00:00:03.1007960
效率比1.559564
=============================
00:00:02.4476305
00:00:03.8717636
效率比1.581842
=============================
00:00:02.9129007
00:00:04.6332828
效率比1.590608
=============================
00:00:03.4006140
00:00:05.3971930
效率比1.587123
=============================
00:00:03.8655281
00:00:06.2574500
效率比1.618783
=============================
00:00:04.4532249
00:00:07.0674710
效率比1.587046
=============================
00:00:04.9540083
00:00:07.8596999
效率比1.586533
=============================
 
 
分析一下测试结果，不难发现每一个一千次所用的时间基本符合一个等差数列。当然第一个一千次由于要初始化，所以显得慢一些。
总体来说，在程序中用处理一对多关系，比在数据库中用函数处理效率要高35%这样。
那么如果我们在Student表中再添加一行这样的数据：
ID     StuNameClassID
6       李四         3
测试结果如下：
00:00:00.5519228
00:00:00.8206084
效率比1.486817
=============================
00:00:01.0263686
00:00:01.5813210
效率比1.540695
=============================
00:00:01.4886327
00:00:02.3516000
效率比1.579705
=============================
00:00:01.9807901
00:00:03.1495472
效率比1.590046
=============================
00:00:02.4613411
00:00:03.9278171
效率比1.595804
=============================
00:00:02.9246678
00:00:04.6961790
效率比1.605714
=============================
00:00:03.3911521
00:00:05.5018374
效率比1.62241
=============================
00:00:03.8737490
00:00:06.2716150
效率比1.619004
=============================
00:00:04.4047347
00:00:07.1796579
效率比1.629986
=============================
00:00:04.8688508
00:00:07.9477787
效率比1.632372
=============================
发现添加数据之后，效率比进一步加大
环境：vs2008,sql 2005
总结：根据测试结果来说，对于大规模高并发的数据库操作（在这里是10次循环，每次1000次读取数据），我们应该尽可能的避免使用数据库函数，而应该将数据全部取出来，在程序中进行处理
写在最后的话：对于我的程序、代码、思路等等一切的一切有不同见解者，欢迎留言讨论。这是我的第一篇博客，希望大家多多支持，如有不足望海涵
  
作者 CrazyJinn