MSSql存储过程高效应用

关于存储过程的安全性

最近在做大型聊天室项目，我是负责数据库和软件后台这一块的。为了安全性性考虑，我们开发小组决定将所有SQL语句封装成存储过程，然后只开通一个远程访问sql账户，并且将这些存储过程的执行权限赋予到这个sql账户中去。编写存储过程的时候我们的存储过程名称都取得比较有意义和规律，为了进一步的提高安全，我们小组又把存储过程的名称再进行了一次加密。

关于存储过程的性能

大型聊天室程序读取数据库是非常平凡的，比如每一个用户进入一个聊天室，他首先要从数据库中读取在这个聊天室中的玩家信息，而每一个玩家信息字段差不多有接近100个字段，且分布到各个扩展表中，有些时间某个扩展表的多条数据会和主表中的某一条数据相关联，那么就需要用到分部查询了。这样的查询在程序中还是比较好实现的，但是也有他的弊端，比如房间中了1万个用户，那么我们要读取到一万的用户的基本信息，就需要查询1万次数据库。如果我们还需要显示这一万个用户的详细信息，那么就需要查询几万次数据库了，这样做显然不行。

一次性读取10000条数据，远远比一次读取10条数据，连续读1000次的效率快。

我们自己的办法就是将逻辑处理写入到存储过程当中去，服务端只需要调用一个这样的存储过程就能达到目的：

create proc proc_name

(

@userIds varchar(max)  --这个房间的所有用户Id,以逗号分隔

)

as

begin

//逻辑处理，各种循环 判断 游标

select * from #tableName

end

EXEC proc_name '1,2,3,4,5,6,7'

这样就只需要访问一次数据库就能得到所有数据。

下面分享一下这样的代码

[sql]  view plain copy

1. create proc [dbo].[Proc_#CombinationService]    
2.     (    
3.        @SplitString nvarchar(100),    
4.         @FieldSeparator nvarchar(10)='|',    
5.         @GroupSeparator nvarchar(10)='&',    
6.         @ReturnData nvarchar(2000) output    
7.     )    
8.     AS    
9.     BEGIN    
10.         DECLARE @CurrentIndex int;    
11.         DECLARE @NextIndex int;    
12.         DECLARE @ReturnText nvarchar(100);    
13.         DECLARE @Return nvarchar(1000)=''    
14.         DECLARE @ServiceNumber varchar(100);    
15.         DECLARE @ServiceName varchar(100);    
16.         DECLARE @ServiceLevel varchar(100);    
17.         DECLARE @IconNumber varchar(100);    
18.         DECLARE @Explain varchar(150);    
19.         SELECT @CurrentIndex=1;    
20.         WHILE(@CurrentIndex<=len(@SplitString))    
21.             BEGIN    
22.                 SELECT   
23.                 @NextIndex=charindex(@FieldSeparator,@SplitString,@CurrentIndex);  
24.                 IF(@NextIndex=0 OR @NextIndex IS NULL)    
25.                     SELECT @NextIndex=len(@SplitString)+1;    
26.                     SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);  
27.                     select @ServiceNumber=ServiceNumber,@ServiceName=ServiceName,  
28.         @ServiceLevel=ServiceLevel,    
29.                     @IconNumber=IconNumber,@Explain=Explain    
30.                     from UserServiceInfo where ServiceNumber=@ReturnText    
31.                     set @Return=@ServiceNumber+@FieldSeparator+@ServiceName+  
32.         @FieldSeparator+@ServiceLevel  
33.         +@FieldSeparator+@IconNumber+@FieldSeparator+@Explain+@GroupSeparator+@Return    
34.                     SELECT @CurrentIndex=@NextIndex+1;    
35.                 END    
36.           set @ReturnData=@Return    
37.        RETURN;    
38.     END    

[sql]  view plain copy

1. create PROCEDURE [dbo].[Proc_#UsersInfo_GetDataInfo]    
2.      (    
3.          @SplitString nvarchar(100),    
4.         @FieldSeparator nvarchar(10)='|'    
5.      )    
6.      AS    
7.      begin    
8.     CREATE TABLE #t(UserNumber INT PRIMARY KEY,NickName varchar(20),  
9.     LiangNumber varchar(20),GroupNumber int,  
10.     Avatar varchar(100),ServicesNumber varchar(100),ReturnData nvarchar(2000))    
11.         DECLARE @CurrentIndex int;    
12.         DECLARE @NextIndex int;    
13.         DECLARE @ReturnText nvarchar(100);    
14.         DECLARE @ReturnData nvarchar(2000);    
15.         DECLARE @UserNumber INT;    
16.         DECLARE @NickName varchar(20);    
17.         DECLARE @LiangNumber varchar(20);    
18.         DECLARE @GroupNumber int;    
19.         DECLARE @Avatar varchar(100);    
20.         DECLARE @ServicesNumber nvarchar(100);    
21.         SELECT @CurrentIndex=1    
22.         WHILE(@CurrentIndex<=len(@SplitString))    
23.             BEGIN    
24.          SELECT @NextIndex=charindex(@FieldSeparator,@SplitString,@CurrentIndex);    
25.                 IF(@NextIndex=0 OR @NextIndex IS NULL)    
26.                     SELECT @NextIndex=len(@SplitString)+1;    
27.                     SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);    
28.                     select  @UserNumber=UserNumber,@NickName=NickName,  
29.             @LiangNumber=LiangNumber,@GroupNumber=GroupNumber,  
30.             @Avatar=Avatar,@ServicesNumber=ServicesNumber from View_ClientUserList    
31.                     where UserNumber=@ReturnText    
32.                     exec [Proc_#CombinationService] @ServicesNumber,'|','#',  
33.             @ReturnData output    
34.                     insert into #t(UserNumber,NickName,LiangNumber,GroupNumber,Avatar,  
35.             ServicesNumber,ReturnData)     
36.                   values(@UserNumber,@NickName,@LiangNumber,@GroupNumber,@Avatar,  
37.             @ServicesNumber,@ReturnData)    
38.                      SELECT @CurrentIndex=@NextIndex+1;    
39.      END    
40.      select * from #t    
41.      drop table #t    
42.      end    

[Proc_#UsersInfo_GetDataInfo] '1036|1039|1035|','|'

结果

1035 NULL 65484 3 NULL 2 2|土财主|1|[tcz]|一次性充值5万#
1036 test1 10361036 NULL NULL 1|2|3 3|大款|1|[dk]|使用100万虚拟币#2|土财主|1|[tcz]|一次性充值5万#1|贵族|1|[gz]|消费超过1万#
1039 test 1037 1 NULL 2|3 3|大款|1|[dk]|使用100万虚拟币#2|土财主|1|[tcz]|一次性充值5万#


以上操作虽然降低了程序与数据库之间的链接频率，但是却加大了数据库的运算量和数据的传输量。

最后我们小组放弃了让存储过程从数据库读取最后一列的数据（@ReturnData），而是将这个数据表里面的数据一次性读到程序内存当中去，然后在程序里面根据它的编号集（倒数第二列）来进行解析。