WEB开发网
开发学院数据库MSSQL Server SQL Server性能调教系列(4)--Profiler(下) 阅读

SQL Server性能调教系列(4)--Profiler(下)

 2010-09-27 00:00:00 来源:WEB开发网   
核心提示: 3.4 问题处理方案二(复杂,精确,SQL Server性能调教系列(4)--Profiler(下)(2),逻辑上相同的SQL,参数用通配符替代),你会得到更多的启发,(1) 模式化查询,这个方法是T-SQL查询技术内幕中介绍的方法,如果需要更加详细的说明

3.4 问题处理方案二(复杂,精确,逻辑上相同的SQL,参数用通配符替代),这个方法是T-SQL查询技术内幕中介绍的方法,如果需要更加详细的说明,请阅读这本书,你会得到更多的启发。

(1) 模式化查询,它对于相同模式的查询是一样的。

T-SQL函数实现

建立函数:

CREATE FUNCTION [dbo].[fn_SQLSigTSQL] 
 (@p1 NTEXT, @parselength INT = 4000) 
RETURNS NVARCHAR(4000) 
 
-- This function will replace the parameters with '#' 
-- This function is provided "AS IS" with no warranties, 
-- and confers no rights. 
-- Use of included script samples are subject to the terms specified at 
-- http://www.microsoft.com/info/cpyright.htm 
-- 
-- Strips query strings 
AS 
BEGIN 
 DECLARE @pos AS INT; 
 DECLARE @mode AS CHAR(10); 
 DECLARE @maxlength AS INT; 
 DECLARE @p2 AS NCHAR(4000); 
 DECLARE @currchar AS CHAR(1), @nextchar AS CHAR(1); 
 DECLARE @p2len AS INT; 
 
 SET @maxlength = LEN(RTRIM(SUBSTRING(@p1,1,4000))); 
 SET @maxlength = CASE WHEN @maxlength > @parselength 
           THEN @parselength ELSE @maxlength END; 
 SET @pos = 1; 
 SET @p2 = ''; 
 SET @p2len = 0; 
 SET @currchar = ''; 
 set @nextchar = ''; 
 SET @mode = 'command'; 
 
 WHILE (@pos <= @maxlength) 
 BEGIN 
  SET @currchar = SUBSTRING(@p1,@pos,1); 
  SET @nextchar = SUBSTRING(@p1,@pos+1,1); 
  IF @mode = 'command' 
  BEGIN 
   SET @p2 = LEFT(@p2,@p2len) + @currchar; 
   SET @p2len = @p2len + 1 ; 
   IF @currchar IN (',','(',' ','=','<','>','!') 
    AND @nextchar BETWEEN '0' AND '9' 
   BEGIN 
    SET @mode = 'number'; 
    SET @p2 = LEFT(@p2,@p2len) + '#'; 
    SET @p2len = @p2len + 1; 
   END 
   IF @currchar = '''' 
   BEGIN 
    SET @mode = 'literal'; 
    SET @p2 = LEFT(@p2,@p2len) + '#'''; 
    SET @p2len = @p2len + 2; 
   END 
  END 
  ELSE IF @mode = 'number' AND @nextchar IN (',',')',' ','=','<','>','!') 
   SET @mode= 'command'; 
  ELSE IF @mode = 'literal' AND @currchar = '''' 
   SET @mode= 'command'; 
 
  SET @pos = @pos + 1; 
 END 
 RETURN @p2; 
END

上一页  1 2 3 4 5  下一页

Tags:SQL Server 性能

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接