频道栏目
首页 > 数据库 > 其他综合 > 正文
关于SQL函数效率的一些测试与思考
2012-02-25 16:49:02           
收藏   我要投稿
在项目中我们经常能遇到数据库有“一对多”的关系,比如下面两张表:


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
点击复制链接 与好友分享!回本站首页
相关TAG标签 函数 效率
上一篇:数据库连接池与非连接池效率的直观对比
下一篇:一些SQLite技巧
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站