2007 12月 | werisa

按月存档: 12月 2007

动态sql语句 sp_executesql

在SQL语句中，String前出现的N”标示，作用是著名被标注的字符串是Unicode编码。一般情况下都表明你试图将NCHAR, NVARCHAR or NTEXT 的值类型转换为（或复值到） CHAR, VARCHAR or TEXT中。
另外，有些SQL系统存储过程的参数需要用Unicode编码的值作为参数。
如果当你输入

EXEC sp_ExecuteSQL ‘SELECT 1′

会有以下错误:

Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.

正确的方法是:

— (a) using the N prefix

EXEC sp_ExecuteSQL N’SELECT 1′

— (b) using a variable

DECLARE @sql NVARCHAR(100)
SET @sql = N’SELECT 1′
EXEC sp_ExecuteSQL @sql

动态sql语句基本语法
1 :普通SQL语句可以用Exec执行
eg:    Select * from tableName
          Exec(’select * from tableName’)
          Exec sp_executesql N’select * from tableName’     — 请注意字符串前一定要加N
2:字段名，表名，数据库名之类作为变量时，必须用动态SQL
eg:
declare @fname varchar(20)
set @fname = ‘FiledName’
Select @fname from tableName               — 错误,不会提示错误，但结果为固定值FiledName,并非所要。
Exec(’select ‘ + @fname + ‘ from tableName’)      — 请注意加号前后的单引号的边上加空格
当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = ‘FiledName’ –设置字段名
declare @s varchar(1000)
set @s = ’select ‘ + @fname + ‘ from tableName’
Exec(@s)                 — 成功
exec sp_executesql @s    — 此句会报错

declare @s Nvarchar(1000)   — 注意此处改为nvarchar(1000)
set @s = ’select ‘ + @fname + ‘ from tableName’
Exec(@s)                 — 成功
exec sp_executesql @s    — 此句正确
3. 输出参数
declare @num int,
         @sql nvarchar(4000)
set @sql=’select count(*) from tableName’
exec(@sql)

–如何将exec执行结果放入变量中？
declare @num int, @sql nvarchar(4000)
set @sql=’select @a=count(*) from tableName ‘
exec sp_executesql @sql,N’@a int output’,@num output
select @num

MSSQL优化之————探索MSSQL执行计划

作者：no_mIss QQ:34813284

最近总想整理下对MSSQL的一些理解与感悟，却一直没有心思和时间写，晚上无事便写了一篇探索MSSQL执行计划,本文讲执行计划但不仅限于讲执行计划。
网上的SQL优化的文章实在是很多，说实在的，我也曾经到处找这样的文章，什么不要使用IN了，什么OR了，什么AND了，很多很多，还有很多人拿出仅几S甚至几MS的时间差的例子来证明着什么(有点可笑)，让许多人不知道其是对还是错。而SQL优化又是每个要与数据库打交道的程序员的必修课，所以写了此文，与朋友们共勉。
谈到优化就必然要涉及索引，就像要讲锁必然要说事务一样，所以你需要了解一下索引，仅仅是索引，就能讲半天了，所以索引我就不说了(打很多字是很累的，况且我也知之甚少)，可以去参考相关的文章，这个网上资料比较多了。
今天来探索下MSSQL的执行计划，来让大家知道如何查看MSSQL的优化机制，以此来优化SQL查询。

–DROP TABLE T_UserInfo—————————————————-
–建测试表
CREATE TABLE T_UserInfo
(
    Userid varchar(20), UserName varchar(20),
    RegTime datetime, Tel varchar(20),
)
–插入测试数据
DECLARE @I INT
DECLARE @ENDID INT
SELECT @I = 1
SELECT @ENDID = 100 –在此处更改要插入的数据，重新插入之前要删掉所有数据
WHILE @I <= @ENDID
BEGIN
    INSERT INTO T_UserInfo
    SELECT ‘ABCDE’+CAST(@I AS VARCHAR(20))+’EF’,'李’+CAST(@I AS VARCHAR(20)),
       GETDATE(),’876543′+CAST(@I AS VARCHAR(20))
    SELECT @I = @I + 1
END

–相关SQL语句解释
—————————————————————————
–建聚集索引
CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)
–建非聚集索引
CREATE NONCLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)
–删除索引
DROP INDEX T_UserInfo.INDEX_Userid
—————————————————————————
—————————————————————————
–显示有关由Transact-SQL 语句生成的磁盘活动量的信息
SET STATISTICS IO ON
–关闭有关由Transact-SQL 语句生成的磁盘活动量的信息
SET STATISTICS IO OFF
–显示[返回有关语句执行情况的详细信息，并估计语句对资源的需求]
SET SHOWPLAN_ALL ON
–关闭[返回有关语句执行情况的详细信息，并估计语句对资源的需求]
SET SHOWPLAN_ALL OFF
—————————————————————————
请记住：SET STATISTICS IO 和 SET SHOWPLAN_ALL 是互斥的。

OK，现在开始：
首先，我们插入100条数据
然后我写了一个查询语句：
SELECT * FROM T_UserInfo WHERE USERID=’ABCDE6EF’
选中以上语句，按Ctrl+L，如下图

这就是MSSQL的执行计划：表扫描：扫描表中的行

然后我们来看该语句对IO的读写：
执行:SET STATISTICS IO ON
此时再执行该SQL:SELECT * FROM T_UserInfo WHERE USERID=’ABCDE6EF’
切换到消失栏显示如下：
表’T_UserInfo’。扫描计数1，逻辑读1 次，物理读0 次，预读0 次。
解释下其意思：
四个值分别为：
    执行的扫描次数;
    从数据缓存读取的页数;
  从磁盘读取的页数;
    为进行查询而放入缓存的页数
重要：如果对于一个SQL查询有多种写法，那么这四个值中的逻辑读(logical reads)决定了哪个是最优化的。

接下来我们为其建一个聚集索引
执行CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)
然后再执行SELECT * FROM T_UserInfo WHERE USERID=’ABCDE6EF’
切换到消息栏如下显示：
表’T_UserInfo’。扫描计数1，逻辑读2 次，物理读0 次，预读0 次。
此时逻辑读由原来的1变成2，
说明我们又加了一个索引页，现在我们查询时，逻辑读就是要读两页(1索引页+1数据页)，此时的效率还不如不建索引。

此时再选中查询语句，然后再Ctrl+L，如下图:

聚集索引查找：扫描聚集索引中特定范围的行
说明，此时用了索引。

OK,到这里你应该已经知道初步知道MSSQL查询计划和如何查看对IO的读取消耗了吧！

关于权限信息的储存

曾经用过的系统权限储存方法，只用一个整数来表示对象所具有的权限信息。
采用二进制位运算储存和分析权限信息，假设系统中有4种权限 R1，R2，R3，R4。用1，2，4，8分别表示这4个权限。用二进制表示如下：

权限
二进制
十进制

R1
1
1

R2
10
2

R3
100
4

R4
1000
8

我们可以这样设计对象的权限。假如对象具有R1，R4权限，并用1表示具有权限，用0表示不具有权限，那么可以得到下面的表格：

R4
R3
R2
R1

1
0
0
1

这样就得到一个二进制数 1001 ，转换成十进制就是 9 。那么该对象的权限值就是9。
    接下来只要对 9 进行一系列位与操作就可以还原出对象的权限信息。具体方法如下：
    首先用9 和表示R1权限的值 1 进行位与运算（9 & 1），用二进制来看比较方便：
    9 : 1001
    1 : 0001
    位于操作后的结果是 0001 ，就是十进制的 1 (9 & 1 = 1)。这就表示对象具有 R1 权限。
    接下来继续用 9 和表示R2权限的值 2 进行位与运算（9 & 2），同样用二进制来看：
    9 : 1001
    2 : 0010
    位于操作后的结果是 0000，就是十进制的 0 (9 & 2 = 0)。这就表示对象不具有 R2 权限。
    同样的方法对于 R3 可以得到 9 & 4 = 0，对象不具有 R3 权限；对于 R4 可以得到 9 & 8 = 4 ，对象具有 R4 权限。于是，通过 9 和 1、2、4、8 的位与操作，就解析出了 9 中包含的权限信息。
    归纳：假设系统中总共有n个权限，那么第k (1<=k<=n)个权限的标示值为 2的k-1次方。对象的权限值 rv 必须满足 0<=rv<=(2的k次方-1)。如果 rv 和 2的k-1次方位与操作结果为0，那么对象不具有第k个权限；如果 rv 和 2的k-1次方位与操作结果非0（结果为 2的k-1次方），那么对象具有第k个权限。
    下面用AS3.0描述这个过程：
package
{
public class RightOperation
{
  public function RightOperation()
  {
   var R1:int = 1;
   var R2:int = 2;
   var R3:int = 4;
   var ObjectRightValue:int = 5;
   if(ObjectRightValue & R1)
   {
    trace("Object gets right R1.");
   }
   if(ObjectRightValue & R2)
   {
    trace("Object gets right R2.");
   }
   if(ObjectRightValue & R3)
   {
    trace("Object gets right R3.");
   }
  }
}

     这样，仅用一个整数变量 ObjectRightValue 就包含了对象所有权限的信息，在实际应用开发中有很好的效率。

asp.net的sessionState节点的配置

web.config关于sessionState节点的配置方案，sessionState有四种模式：off,inProc,StateServer,SqlServer。

1、off模式
从字面上就可以看出这个是关闭模式，如果当前页面不需要session的值，为了减少服务器资源，你可以去掉Session的开销。
<sessionState mode="off">或者页面上
<%@ Page EnableSessionState="false" %>来关闭Session。
2、inProc模式(缺省模式)
它允许“无Cookie”的会话，以及在服务器之外存储
会话数据。ASP.NET会话状态模块在Web.config文件中像下面这样配置：
<sessionState mode="InProc" cookieless="false" timeout="20" />
在这个例子中，mode属性设为InProc（默认值），表明会话状态要由ASP.NET存储到内存中，而且
不用Cookie来传递会话ID。采取这种方式，不管Cookie还是隐藏表单字段都用不着了。
所以，即使网页中没有使用表单，也能加入会话。但是这种方法，应用程序的状态将依赖于 ASP.NET进程，当IIS进程崩溃或者正常重启时，保存在
进程中的状态将丢失。
3、StateServer会话管理mos
将mode属性设为StateServer，也就是将会话数据存储到单独的内存缓冲区中，再由单独一台机器上运行
的Windows服务来控制这个缓冲区。状态服务全称是“ASP.NET State Service ”（aspnet_state.exe），计算机管理-服务里面即可看到此服务，启动该服务j
它由Web.config文件中的stateConnectionString属性来配置。该属性指定了服务所在的服务器，以及要监
视的端口：
<sessionState mode="StateServer"
    stateConnectionString="tcpip=myserver:42424"
    cookieless="false" timeout="20" />

在这个例子中，状态服务在一台名为myserver的机器的42424端口（默认端口）运行。要在服务器上改变
端口，可编辑HKLM\SYSTEM\CurrentControlSet\Services\aspnet_state注册表项中的Port值。

显然，使用状态服务的优点在于进程隔离，并可在Web farm中共享。使用这种模式，会话状态的存储将不
依赖于iis进程的失败或者重启，然而，一旦状态服务中止，所有会话数据都会丢失。换言之，状态服务不
像SQL Server那样能持久存储数据；它只是将数据存储在内存中。

解决ASP.NET上传文件大小限制

对于asp.net,默认只允许上传2M文件,增加如下配置,一般可以自定义最大文件大小.
<httpRuntime
executionTimeout="300"
maxRequestLength="40960"
useFullyQualifiedRedirectUrl="false"/>

如果还不行,可以使用思归提供的方案:
我们在上传大文件时都遇到过这样或那样的问题。设置很大的maxRequestLength值并不能完全解决问题，因为ASP.NET会block直到把整个文件载入内存后，再加以处理。实际上，如果文件很大的话，我们经常会见到Internet Explorer显示 "The page cannot be displayed - Cannot find server or DNS Error"，好像是怎么也catch不了这个错误。为什么？因为这是个client side错误，server side端的Application_Error是处理不到的，可以参考这个帖子研究一下产生这个错误的机理。
handling server error when upload file too large
解决的方法是利用隐含的HttpWorkerRequest，用它的GetPreloadedEntityBody 和 ReadEntityBody方法从IIS为ASP.NET建立的pipe里分块读取数据
  IServiceProvider provider = (IServiceProvider) HttpContext.Current;
  HttpWorkerRequest wr = (HttpWorkerRequest) provider.GetService(typeof(HttpWorkerRequest));
  byte[] bs = wr.GetPreloadedEntityBody();
  ….
  if (!wr.IsEntireEntityBodyIsPreloaded())
  {
        int n = 1024;
        byte[] bs2 = new byte[n];
        while (wr.ReadEntityBody(bs2,n) >0)
       {
             …..
        }
  }
Chris Hynes为我们提供了这样的一个方案(用HttpModule)，该方案除了允许你上传大文件外，还能实时显示上传进度：
ASP.NET Upload Magic Part 2
这里有他讲座的PPT文件：
Uploading with ASP.NET (part 1)
Uploading with ASP.NET (part 2)

werisa