最近在做大型聊天室项目，我是负责数据库和软件后台这一块的。为了安全性性考虑，我们开发小组决定将所有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'
这样就只需要访问一次数据库就能得到所有数据。
下面分享一下这样的代码
create proc [dbo].[Proc_#CombinationService]
(
@SplitString nvarchar(100),
@FieldSeparator nvarchar(10)='|',
@GroupSeparator nvarchar(10)='&',
@ReturnData nvarchar(2000) output
)
AS
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(100);
DECLARE @Return nvarchar(1000)=''
DECLARE @ServiceNumber varchar(100);
DECLARE @ServiceName varchar(100);
DECLARE @ServiceLevel varchar(100);
DECLARE @IconNumber varchar(100);
DECLARE @Explain varchar(150);
SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=len(@SplitString))
BEGIN
SELECT
@NextIndex=charindex(@FieldSeparator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=len(@SplitString)+1;
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
select @ServiceNumber=ServiceNumber,@ServiceName=ServiceName,
@ServiceLevel=ServiceLevel,
@IconNumber=IconNumber,@Explain=Explain
from UserServiceInfo where ServiceNumber=@ReturnText
set @Return=@ServiceNumber+@FieldSeparator+@ServiceName+
@FieldSeparator+@ServiceLevel
+@FieldSeparator+@IconNumber+@FieldSeparator+@Explain+@GroupSeparator+@Return
SELECT @CurrentIndex=@NextIndex+1;
END
set @ReturnData=@Return
RETURN;
END
create PROCEDURE [dbo].[Proc_#UsersInfo_GetDataInfo]
(
@SplitString nvarchar(100),
@FieldSeparator nvarchar(10)='|'
)
AS
begin
CREATE TABLE #t(UserNumber INT PRIMARY KEY,NickName varchar(20),
LiangNumber varchar(20),GroupNumber int,
Avatar varchar(100),ServicesNumber varchar(100),ReturnData nvarchar(2000))
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(100);
DECLARE @ReturnData nvarchar(2000);
DECLARE @UserNumber INT;
DECLARE @NickName varchar(20);
DECLARE @LiangNumber varchar(20);
DECLARE @GroupNumber int;
DECLARE @Avatar varchar(100);
DECLARE @ServicesNumber nvarchar(100);
SELECT @CurrentIndex=1
WHILE(@CurrentIndex<=len(@SplitString))
BEGIN
SELECT @NextIndex=charindex(@FieldSeparator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=len(@SplitString)+1;
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
select @UserNumber=UserNumber,@NickName=NickName,
@LiangNumber=LiangNumber,@GroupNumber=GroupNumber,
@Avatar=Avatar,@ServicesNumber=ServicesNumber from View_ClientUserList
where UserNumber=@ReturnText
exec [Proc_#CombinationService] @ServicesNumber,'|','#',
@ReturnData output
insert into #t(UserNumber,NickName,LiangNumber,GroupNumber,Avatar,
ServicesNumber,ReturnData)
values(@UserNumber,@NickName,@LiangNumber,@GroupNumber,@Avatar,
@ServicesNumber,@ReturnData)
SELECT @CurrentIndex=@NextIndex+1;
END
select * from #t
drop table #t
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），而是将这个数据表里面的数据一次性读到程序内存当中去，然后在程序里面根据它的编号集（倒数第二列）来进行解析。