qileilove

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

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

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

  Student:

  Class:

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

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

  要做到这一点并不难,大体有两种思路:

  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表中再添加一行这样的数据:

  测试结果如下:

  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次读取数据),我们应该尽可能的避免使用数据库函数,而应该将数据全部取出来,在程序中进行处理

  写在最后的话:对于我的程序、代码、思路等等一切的一切有不同见解者,欢迎留言讨论。这是我的第一篇博客,希望大家多多支持,如有不足望海涵。

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


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


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

导航

统计

常用链接

留言簿(55)

随笔分类

随笔档案

文章分类

文章档案

搜索

最新评论

阅读排行榜

评论排行榜