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

上一篇 / 下一篇  2012-05-16 14:05:35 / 个人分类：数据库

查看( 273 ) / 评论( 0 ) / 评分( 0 / 0 )
在项目中我们经常能遇到数据库有“一对多”的关系，比如下面两张表：

;YKe4\)|J0　　Student：

*?H2j'J1gi%[0

51Testing软件测试网*? V{Oo3j

　　Class：51Testing软件测试网0TR2~iP3n;U

-ZP/L5]~)Y;OS7~0

　　Class-Student就这样构成了一个简单的一对多关系。当然在实际的项目中，也可以再建立一张Relation表来保存他们之间的关系，在这里为了简单，就不做Relation表了。51Testing软件测试网6v Z*X$}E

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

NV4W J$Lv&B9CH0

P \ i DY n FX0

　　要做到这一点并不难，大体有两种思路：51Testing软件测试网]9v^){h#@ q

　　1、在数据库中写一个函数

3U@3uE{(i0

　　2、在程序中获取表Class与表Student所有数据，然后对比ClassID51Testing软件测试网$Gm3z`%]

　　那么，那种方法效率比较高呢？于是我写了下面的代码来进行一个简单的测试

m'h _2x(R/\8^"WO)j0

H qtk)A&d!rO RA0View Code

] oMHE_&b m051Testing软件测试网^Jf3k4[%kn

51Testing软件测试网2Le0~[Cr-jg
    class Program51Testing软件测试网VA@9gEH
    {
gAh/s[l0        static void Main(string[] args)
W3g!Um!j'V(C.}0        {51Testing软件测试网+\3F.T5zd _)[D
            Sql sql = new Sql();51Testing软件测试网WnV4M?Z&}E
            Stopwatch time1 = new Stopwatch();
~i^|tY1uD*m0            Stopwatch time2 = new Stopwatch();51Testing软件测试网YHU)jCUG
            for (int j = 0; j < 10; j++)51Testing软件测试网jEx[A*si z[
            {
Kc w$H0Z$_K0                time2.Start();51Testing软件测试网+x tAC]&h6HT7a
                for (int i = 0; i < 1000; i++)
/kn([9|.?T,zHo0                {
#u JK5u8r:l0                    string sql1 = "select ID,[StuName],[ClassID] FROM [Student]";51Testing软件测试网([3O t MY$sK9?
                    string sql2 = " SELECT  ID,ClassName from Class";51Testing软件测试网2iD@S4O6E$\5N
                    List<string> item = new List<string>();
c(]+q\w i.N*A0                    string bl="";51Testing软件测试网_DI%nVL
                    DataTable dt1 = sql.getData(sql1);51Testing软件测试网)d-xZz.w{5{3x
                    DataTable dt2 = sql.getData(sql2);51Testing软件测试网;m,\ c$Ns
                    foreach (DataRow dtRow2 in dt2.Rows)51Testing软件测试网mV NKs/Qn3P
                    {51Testing软件测试网H0p ^6b(e
                        foreach (DataRow dtRow1 in dt1.Rows)51Testing软件测试网#o:wq+m+cRQ9u
                        {51Testing软件测试网'zL!b/~XK({
                            if (dtRow1["ClassID"].ToString() == dtRow2["ID"].ToString())
$I"J.C |;F5L+aV0                            {51Testing软件测试网2HNt)b\ss C \
                                 bl+=dtRow1["StuName"].ToString()+",";
csp~!E:Rv i0                            }   
#g2M4h#]3n[%i*j0                        }
4]U#x^(MK'G0                        item.Add(bl);51Testing软件测试网 q0YF'T'O
                        bl = "";51Testing软件测试网(^#]1f+zl @D
                    }51Testing软件测试网1_bWB1m
                }51Testing软件测试网/qN_0j-PY[f-f
                time2.Stop();
4G\o6M)]Tk,V!z6H0                Console.WriteLine(time2.Elapsed.ToString());51Testing软件测试网#RpE9eh:G-z

uJq?2g+H \I,a0                time1.Start();51Testing软件测试网j6})Q^ lz$O
                for (int i = 0; i < 1000; i++)
(j}%p]4w [%Nu0                {
@_k*Ze t0                    string sql3 = "SELECT C.ID, C.ClassName, dbo.f_getStuNamesByClassID(C.ID)as stuName FROM Class C";51Testing软件测试网8rxH4Ml_*c+B2c
                    DataTable dt = sql.getData(sql3);51Testing软件测试网3_k0\U9_-wnAt
                }51Testing软件测试网/c7VO+x:U,Ch
                time1.Stop();
"Ife9j4o)Pj9J0                Console.WriteLine(time1.Elapsed.ToString());51Testing软件测试网{?1dn/N)I5B]r

51Testing软件测试网k6l kT3r

51Testing软件测试网Ebn~hH
                float index = (float)time1.Elapsed.Ticks / (float)time2.Elapsed.Ticks;
T I"vn[ G2m'i1v1E0                Console.WriteLine("效率比" + index.ToString());51Testing软件测试网0Q|w8kG _?;b0x
                Console.WriteLine("=============================");
a;~V0EX7dS(_+K0            }

.KV I0T1^%v/J,G051Testing软件测试网v&Qu0pl;TfI B

            Console.ReadLine();
c NF$ZD4w#^0        }
Y*?Fk y6k&a0    }

1@3f:yzO0\3e051Testing软件测试网K{ f$J,H$o;S#Z/r ]

View Code

Z8]%X fyV-f)C@051Testing软件测试网2Hz'|oUe&b

51Testing软件测试网4raf M0e$i4m/h
    class Sql51Testing软件测试网&SJ@Vo-q'z
    {
)LH^^WG.~[0        public DataTable getData(string sql)
~G,a.@!Y g0        {
j,CNH5c.|5m Vd t0            SqlConnection conn = new SqlConnection();51Testing软件测试网KCH9b7L5nAj\!h(Y
            conn.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Test;User Id=sa;Password=1;";51Testing软件测试网;i Un Be?+|G
            SqlCommand comm = new SqlCommand(sql, conn);
8sC~9B+A$i0            conn.Open();51Testing软件测试网 Q XQ1Y t1['b9B3q
            SqlDataAdapter da = new SqlDataAdapter(comm);
tJtP5w'P6d0            DataSet ds = new DataSet();
O|)Di)eHTj0            da.Fill(ds, "ds");51Testing软件测试网4C+Y7]jNX@+Y
            conn.Close();51Testing软件测试网n MTY:x%zc$?\!C}@
            return ds.Tables[0];51Testing软件测试网5iT9{#r Eu0b E0e
        }
7mP;iFq Eg0    }51Testing软件测试网 Etn-v.y,~7x"]v

$M3O(Q0N0J/l e'@PXd0

.L1lGe U m@0
51Testing软件测试网*GgL~o

View Code51Testing软件测试网 } c$~ Qy,]-Ki Q

51Testing软件测试网0U2e5e E8cO

--根据课程ID，返回选此课程的学生的名字，以逗号隔开
|u0Ol%U c@#A0ALTER function [dbo].[f_getStuNamesByClassID] (@ID int)51Testing软件测试网 xF*[XW]:yE6H:Z-@
RETURNS nvarchar(50)
$gNQ,j'^9Z/D| Z0begin
C hsG.R0    declare @Result nvarchar(50);
/NtIR0s3f,e^v8^-g0    declare @stuName nvarchar(50);51Testing软件测试网[pf#G @+A+t
    Set @Result='';51Testing软件测试网d$yL"m:q

51Testing软件测试网 t_ Va}de8n

    declare cur cursor for51Testing软件测试网.Mk?/t3G? L
    (51Testing软件测试网:D;^+ysws(rF
        SELECT S.StuName FROM Class C51Testing软件测试网fe-kR:A d;?
        LEFT JOIN Student S ON C.ID=S.ClassID
3Zx2d j i1r0        WHEREC.ID=@ID51Testing软件测试网Dy9I C B e
    )51Testing软件测试网kga[-Q"T2^ko
    open cur;
_ Pc B Ps-`0    fetch next from cur into @stuName;51Testing软件测试网~7Ta5ZwUw{%k
    while(@@fetch_status=0)51Testing软件测试网 i/ZW!sL ]!Ru0m/B ?
    begin
*fUY:q*@ h0Dr0        set @Result=@Result+@stuName+',';
tJj8o-i~!E8A!k-b0        fetch next from cur into @stuName;
W3wr ^9M'fo0    end;51Testing软件测试网D7m-THq.b$un
--去除最后多余的一个逗号51Testing软件测试网+N)vT+u |6[4n
    IF @Result <> ''51Testing软件测试网#? _~5GU^ M*?
        SET @Result=SUBSTRING(@Result, 1, LEN(@Result)-1);51Testing软件测试网vSR)Q\1]-~G!i
    ELSE51Testing软件测试网K*knV'l d
        SET @Result=NULL;
2u/NhL6Q{tEyz2p0    return @Result;
)T'Vm4N z@0en51Testing软件测试网7V ? Y9UVqkMu

+~e-{'i4[ a%TQI$g0　　测试结果如下：

k_x1ko*np0

N!@.eCkS5i:I0　　00:00:00.5466790
nRY vy |0　　00:00:00.7753704
K6U ]H4Fk^:c;E]%q0　　效率比1.418329
D V%S)_NF}k {0　　=============================
'h)POg*tE0　　00:00:01.025199651Testing软件测试网#E#v_"D4~'x$cF4cMI
　　00:00:01.559462951Testing软件测试网)[ x!ml}7t6p
　　效率比1.521131
cEH\,_0　　=============================51Testing软件测试网}6O"[9} P;{
　　00:00:01.512434951Testing软件测试网2ES'D{[
　　00:00:02.3286227
;Z f#c,wJ0　　效率比1.539652
6D1{.]k C)^Me}0　　=============================
#{q(g2w dD&M#j ?0　　00:00:01.988245851Testing软件测试网+p ?.N0t&R*Wm
　　00:00:03.100796051Testing软件测试网peo3KW!I%Cj
　　效率比1.55956451Testing软件测试网)S7{x_:\H`l H/G
　　=============================
:j1u(M"H`3{M0　　00:00:02.4476305
:@TC)ZxZA)s#` L0　　00:00:03.8717636
(W3E4c}%VEp0　　效率比1.581842
7\!yBv F0　　=============================51Testing软件测试网&w*`|.a1ro6_
　　00:00:02.9129007
wl)I*\*W] Rn0　　00:00:04.6332828
8I/a-e_0?Ij aa8Sj0　　效率比1.590608
*JUP0PnJ.]%f0　　=============================51Testing软件测试网5_GH)M(T5VLN"e
　　00:00:03.400614051Testing软件测试网&| F*j cv
　　00:00:05.397193051Testing软件测试网H p:G,U[-p
　　效率比1.58712351Testing软件测试网f$@1_CS'c]|H
　　=============================51Testing软件测试网S*C%I*C5R M
　　00:00:03.865528151Testing软件测试网0B2H;t v!i;b9}b3Ha
　　00:00:06.2574500
Om*Brb;qQ~#}K8^#x0　　效率比1.618783
'g$RcezjZ!Q-Ma^0　　=============================51Testing软件测试网-?6` |v{,Pf
　　00:00:04.4532249
q\/v"X/HG*c0　　00:00:07.067471051Testing软件测试网 P;l!t@W?p
　　效率比1.587046
'\,V[6w f^\0K0　　=============================51Testing软件测试网k5BuA-q er#c{
　　00:00:04.954008351Testing软件测试网 LZ|x B] g.F k{
　　00:00:07.859699951Testing软件测试网c7u\2A*gc|5Ms.[
　　效率比1.586533
]f'y'J*X;]8r0　　=============================51Testing软件测试网Can.~b"sK4l"f-l5I-t

Gj&[ mFd|c3a.]0分析一下测试结果，不难发现每一个一千次所用的时间基本符合一个等差数列。当然第一个一千次由于要初始化，所以显得慢一些。51Testing软件测试网JKK9g(]#t&^

51Testing软件测试网f nFp5l as

　　总体来说，在程序中用处理一对多关系，比在数据库中用函数处理效率要高35%这样。51Testing软件测试网0t y4~#Z0k ?'OG&r

'Z f9Rz5`D$\*X0　　那么如果我们在Student表中再添加一行这样的数据：

^Wr s6e(fs!~6A0

51Testing软件测试网:o#L#T;\7Q

　　测试结果如下：51Testing软件测试网ovg'o E8]f

　　00:00:00.5519228
a%w)Sy'?H0　　00:00:00.8206084
AeNu Tv3c$}Z0　　效率比1.486817
O|P UpFGR(Gp0　　=============================51Testing软件测试网TG8Dq3EV;u
　　00:00:01.0263686
|3utlhTA0　　00:00:01.581321051Testing软件测试网.~+Yw5S jb
　　效率比1.540695
uv(R(CL'w.R @0　　=============================51Testing软件测试网VL0}~@-JQ7ZB
　　00:00:01.488632751Testing软件测试网9AKD1qP _,y
　　00:00:02.3516000
y)N/_7tU0　　效率比1.579705
7|a'hb]%I/C:N0　　=============================
x1^T5g+`s#dx0　　00:00:01.980790151Testing软件测试网 KAx{`x N&`9|
　　00:00:03.1495472
;F.I4H0Y1wk0　　效率比1.59004651Testing软件测试网sx+t$LOU!m.G pJ;V k
　　=============================
| Ro8k$k P!j.B`0　　00:00:02.4613411
\/s"CIJ%@L)P dST&m0　　00:00:03.9278171
6T] K C+C6ld6G;A0　　效率比1.595804
7y6x3l0q V0　　=============================
7g3H&P%@Rg8A0　　00:00:02.9246678
2@1k&Y5x~"\&T0　　00:00:04.6961790
5\UC/X$@h(O2t0　　效率比1.605714
L:A_A#U#z%A.n0　　=============================
;\K1ry%L0　　00:00:03.391152151Testing软件测试网Y1r&dx~y Cg
　　00:00:05.5018374
&oq"g,?F9Q:e%J0　　效率比1.6224151Testing软件测试网6cRVyS^ e` \ vh
　　=============================
,BE.W9NRI0　　00:00:03.8737490
~DGH"m.B0　　00:00:06.271615051Testing软件测试网H(YT)Mi1D lzI;?H/l
　　效率比1.61900451Testing软件测试网deQh}jH-h
　　=============================51Testing软件测试网BwQmXc|
　　00:00:04.404734751Testing软件测试网)yDBIR-]
　　00:00:07.179657951Testing软件测试网 P2t oBHC
　　效率比1.62998651Testing软件测试网?T9V%OA$LV g.b
　　=============================51Testing软件测试网$n EQ1kH
　　00:00:04.868850851Testing软件测试网*f9e/pq@QVv
　　00:00:07.947778751Testing软件测试网.oD n G&?CS
　　效率比1.632372
4pvT1`^5jC0　　=============================

Ud(S/l6O!~5m%Od7\M0

　　发现添加数据之后，效率比进一步加大

9IH6gB/^0

　　环境：vs2008,sql 2005

0^&w zh$y}_D%E&T0

　　总结：根据测试结果来说，对于大规模高并发的数据库操作（在这里是10次循环，每次1000次读取数据），我们应该尽可能的避免使用数据库函数，而应该将数据全部取出来，在程序中进行处理

2Ou6~}SC"F[:\!h l/q0

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

F:{;E@s J$h~0

收藏 举报

TAG:

 

评分：0

我来说两句

Open Toolbar