存储过程优化

Question

不过好像sql是不支持boolean类型的。

Answer 1

可是不支持set @flag = @a > @b 

Answer 2

呵呵，好像还挺难合并的：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[c_DueConscriptionQueue] 
@ID INT
AS 
IF NOT EXISTS ( SELECT  [ID]
                FROM    [Conscription_Queue]
                WHERE   [ID] = @ID
                        AND [Completed] = 0 ) 
    BEGIN
        RETURN 0
    END 
    
DECLARE @TroopID INT
DECLARE @VillageID INT
DECLARE @UnitTraining INT
DECLARE @NextUnit DATETIME
DECLARE @Total INT DECLARE @Count INT SELECT  @VillageID = [VillageID], @TroopID = [TroopID], @NextUnit = [NextUnit], @UnitTraining = [UnitTraining], @Total = [Total]
FROM    [Conscription_Queue]
WHERE   [ID] = @ID IF ( @UnitTraining <> 0 ) 
    BEGIN
        SET @Count = DATEDIFF(s, @NextUnit, GETDATE()) / @UnitTraining + 1
    END
ELSE 
    BEGIN
        RETURN 0
    END --检查是否已存在兵种，不存在新增，存在 增加数量
IF NOT EXISTS ( SELECT  *
                FROM    [Village_Troops]
                WHERE   [VillageID] = @VillageID
                        AND [InVillageID] = @VillageID
                        AND [TroopID] = @TroopID
                        AND [GarrisonType] = 1 ) 
    BEGIN
IF ( @Count > @Total ) 
BEGIN
SET @Count = @Total 
END
INSERT  INTO [Village_Troops] ( [TroopID], [quantity], [VillageID], [InVillageID], [GarrisonType] )
                VALUES  ( @TroopID, @Count, @VillageID, @VillageID, 1 )
    END
ELSE 
    BEGIN
IF ( @Count > @Total ) 
BEGIN
SET @Count = @Total
END
UPDATE  [Village_Troops]
                SET     [quantity] = [quantity] + @Count
                WHERE   [VillageID] = @VillageID
                        AND [TroopID] = @TroopID
                        AND [InVillageID] = @VillageID
                        AND [GarrisonType] = 1
    END
    
--增加耗粮
/*
DECLARE @Upkeep INT 
SELECT  @Upkeep = [Upkeep]
FROM    Troops
WHERE   ID = @TroopID
*/UPDATE  [Villages]
        SET     [TroopsUpkeep] = [TroopsUpkeep] + (SELECT  [Upkeep] FROM  Troops
   WHERE   ID = @TroopID) * @Count
WHERE   [TileID] = @VillageID
    
IF ( @Count >= @Total )  --检查是否造完
    BEGIN
        UPDATE  [Conscription_Queue]
        SET     [Total] = 0, [Completed] = 1
        WHERE   [ID] = @ID
    END
ELSE 
    BEGIN
        UPDATE  [Conscription_Queue]
        SET     [NextUnit] = DATEADD(ss, @UnitTraining * @Count, @NextUnit), 
                [Total] = [Total] - @Count
        WHERE   [ID] = @ID
                AND [Total] > 0 
    END

Answer 3

存储过程里面有变量咋办呢？整个存储过程里面有读有写，选select语句只优化查询么？

Answer 4

替换最下面的存储过程名，然后执行，贴结果SELECT  CHAR(13) + CHAR(10)
        + CASE WHEN deqs.statement_start_offset = 0
                    AND deqs.statement_end_offset = -1
               THEN '-- see objectText column--'
               ELSE '-- query --' + CHAR(13) + CHAR(10)
                    + SUBSTRING(execText.text, deqs.statement_start_offset / 2,
                                ( ( CASE WHEN deqs.statement_end_offset = -1
                                         THEN DATALENGTH(execText.text)
                                         ELSE deqs.statement_end_offset
                                    END ) - deqs.statement_start_offset ) / 2)
          END AS queryText ,
        deqp.query_plan
FROM    sys.dm_exec_query_stats deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
        CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) deqp
WHERE   execText.text LIKE '%存储过程名%'

Answer 5

我脚本里面的存储过程名替换成你自己的名字没？你这执行计划是我的脚本啊：SELECT  CHAR(13) + CHAR(10)
        + CASE WHEN deqs.statement_start_offset = 0
                    AND deqs.statement_end_offset = -1
               THEN '-- see objectText column--'
               ELSE '-- query --' + CHAR(13) + CHAR(10)
                    + SUBSTRING(execText.text, deqs.statement_start_offset / 2,
                                ( ( CASE WHEN deqs.statement_end_offset = -1
                                         THEN DATALENGTH(execText.text)
                                         ELSE deqs.statement_end_offset
                                    END ) - deqs.statement_start_offset ) / 2)
          END AS queryText ,
        deqp.query_plan
FROM    sys.dm_exec_query_stats deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
        CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) deqp
WHERE   execText.text LIKE '%c_DueConscriptionQueue%'用这个试试

Answer 6

那换这个吧，也是要替换名字  SELECT    deqp.dbid ,
            deqp.objectid ,
            CAST(detqp.query_plan AS XML) AS singleStatementPlan ,
            deqp.query_plan AS batch_query_plan , 
        --this won't actually work in all cases because nominal plans aren't 
        -- cached, so you won't see a plan for waitfor if you uncomment it 
            ROW_NUMBER() OVER ( ORDER BY Statement_Start_offset ) AS query_position ,
            CASE WHEN deqs.statement_start_offset = 0
                      AND deqs.statement_end_offset = -1
                 THEN '-- see objectText column--'
                 ELSE '-- query --' + CHAR(13) + CHAR(10)
                      + SUBSTRING(execText.text,
                                  deqs.statement_start_offset / 2,
                                  ( ( CASE WHEN deqs.statement_end_offset = -1
                                           THEN DATALENGTH(execText.text)
                                           ELSE deqs.statement_end_offset
                                      END ) - deqs.statement_start_offset )
                                  / 2)
            END AS queryText
  FROM      sys.dm_exec_query_stats deqs
            CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle,
                                                    deqs.statement_start_offset,
                                                    deqs.statement_end_offset)
            AS detqp
            CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
            CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
  WHERE     deqp.objectid = OBJECT_ID('ShowQueryText', 'p');

Answer 7

SELECT    deqp.dbid ,
            deqp.objectid ,
            CAST(detqp.query_plan AS XML) AS singleStatementPlan ,
            deqp.query_plan AS batch_query_plan , 
        --this won't actually work in all cases because nominal plans aren't 
        -- cached, so you won't see a plan for waitfor if you uncomment it 
            ROW_NUMBER() OVER ( ORDER BY Statement_Start_offset ) AS query_position ,
            CASE WHEN deqs.statement_start_offset = 0
                      AND deqs.statement_end_offset = -1
                 THEN '-- see objectText column--'
                 ELSE '-- query --' + CHAR(13) + CHAR(10)
                      + SUBSTRING(execText.text,
                                  deqs.statement_start_offset / 2,
                                  ( ( CASE WHEN deqs.statement_end_offset = -1
                                           THEN DATALENGTH(execText.text)
                                           ELSE deqs.statement_end_offset
                                      END ) - deqs.statement_start_offset )
                                  / 2)
            END AS queryText
  FROM      sys.dm_exec_query_stats deqs
            CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle,
                                                    deqs.statement_start_offset,
                                                    deqs.statement_end_offset)
            AS detqp
            CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
            CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
  WHERE     deqp.objectid = OBJECT_ID('c_DueConscriptionQueue', 'p');执行完后什么都没有！

Answer 8

算了，不折腾
ctrl+M 再执行exec c_DueConscriptionQueue @id=输入一个ID

Answer 9

ctrl+M是什么快捷键呢？什么都木有。。直接执行后面的语句，结果如下:
表 'Conscription_Queue'。扫描计数 0，逻辑读取 3 次，物理读取 2 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

Answer 10

ctrl+m是打开实际执行计划。或者你手动打开执行计划

Answer 11

你用了set statistics io on ？

Answer 12

http://database.51cto.com/art/201108/284646.htm但是执行出来的和文章里面说的不一样。

Answer 13

老大，ctrl+l是打开执行计划的吧！
你看看这个：

Answer 14

ctrl+l 是预估的，不过一般也可以，你把绿色那行字的索引建一下，右键绿色的字，选择丢失索引xx的那个

Answer 15

改一下索引名字....把[<Name of xxx>]那个随便改掉

Answer 16

再运行一下看看是否快了，如果不是，再用ctrl+L

Answer 17

怎么看出是否快了？执行   exec c_DueConscriptionQueue @id=1 ???执行结果如下：

Answer 18

ctrl + L “exec c_DueConscriptionQueue @id=1 ” 后 那行绿色的字体没有了 。

Answer 19

set statistics io off
exec c_DueConscriptionQueue @id=1

Answer 20

参考：http://database.51cto.com/art/201108/284646.htm基本都试过了，按照文章上来的。--建聚集索引  
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