r(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
|