如何关闭存储过程输出

Question

SQL Server 2008 R2。通过 SS Management Studio 中的游标控制循环运行存储过程。 （以下）。在 9,000 个循环中的 3,000 个循环之后，我收到内存不足错误。我相信这仅与 SS Mgmt Studio 有关。那么如何关闭调用例程（如下）和调用的 SPROC 的输出呢？我愿意接受另一种调用存储过程的方法（执行 POPULATE_EMA @sym_in, 20,50,100,12,26, @mink_in, @maxk_in;）

DELETE FROM TA_HISTORY

DECLARE tables_cursor CURSOR
   FOR
   SELECT symbol, MinDSeqKey, MaxDSeqKey
   FROM STOCK_VITALS;

OPEN tables_cursor;

DECLARE @sym_in NVARCHAR(10);
DECLARE @mink_in bigint;
DECLARE @maxk_in bigint;

FETCH NEXT FROM tables_cursor INTO @sym_in, @mink_in, @maxk_in;

WHILE (@@FETCH_STATUS <> -1)
BEGIN;
    --PRINT 'Now Processing. ' + @sym_in;
   EXECUTE POPULATE_EMA @sym_in, 20,50,100,12,26, @mink_in, @maxk_in;

   INSERT INTO TA_HISTORY(SYMBOL, DSEQKEY, EMA20, EMA50, EMA100, EMA12, EMA26)
   SELECT @sym_in, DSEQKEY, EMA1, EMA2,EMA3,EMA4,EMA5
   FROM temp_ema_data
   WHERE @maxk_in - @mink_in > 49

   FETCH NEXT FROM tables_cursor INTO @sym_in, @mink_in, @maxk_in;
END;

CLOSE tables_cursor;

DEALLOCATE tables_cursor;

** 编辑 5/18 - 这是存储过程 - 我需要运行存储过程另一个表中有 9,000 多行。

BEGIN

DROP TABLE temp_ema_data

CREATE TABLE [dbo].[temp_ema_data](
    [n] [int] IDENTITY(1,1) NOT NULL,
    [dseqkey] [bigint] NULL,
    [close_price] [decimal](6, 2) NULL,
    [ema1] [decimal](8, 4) NULL,
    [ema2] [decimal](8, 4) NULL,
    [ema3] [decimal](8, 4) NULL,
    [ema4] [decimal](8, 4) NULL,
    [ema5] [decimal](8, 4) NULL
) ON [PRIMARY]

insert into temp_ema_data (dseqkey, close_price)
select dseqkey,prclose
from STOCK_HIST
where Symbol = @Symbol and dseqkey > @MinKey 
order by dseqkey asc

--declare variables needed
declare @K1 decimal(4,4), @K2 decimal(4,4), @K3 decimal(4,4), @K4 decimal(4,4)
, @K5 decimal(4,4)
declare @prev_ema_1 decimal(8,4), @prev_ema_2 decimal(8,4), @prev_ema_3 decimal(8,4)
, @prev_ema_4 decimal(8,4), @prev_ema_5 decimal(8,4),@initial_sma_1 decimal(8,4)
, @initial_sma_2 decimal(8,4), @initial_sma_3 decimal(8,2), @initial_sma_4 decimal(8,4)
, @initial_sma_5 decimal(8,4)
declare @anchor int

    set @K1 = 2/(1 + @ema_1_intervals + .000)
    set @K2 = 2/(1 + @ema_2_intervals + .000)
    set @K3 = 2/(1 + @ema_3_intervals + .000)
    set @K4 = 2/(1 + @ema_4_intervals + .000)
    set @K5 = 2/(1 + @ema_5_intervals + .000)           

select  @initial_sma_1 = avg(case when n < @ema_1_intervals 
        then close_price else null end),    
        @initial_sma_2  = avg(case when n < @ema_2_intervals 
        then close_price else null end),
        @initial_sma_3  = avg(case when n < @ema_3_intervals 
        then close_price else null end),
        @initial_sma_4  = avg(case when n < @ema_4_intervals 
        then close_price else null end),
        @initial_sma_5  = avg(case when n < @ema_5_intervals 
        then close_price else null end)                     
from temp_ema_data
where n < @ema_1_intervals or n < @ema_2_intervals or 
      n < @ema_3_intervals or n < @ema_4_intervals or
      n < @ema_5_intervals

update t1 
    set @prev_ema_1 = case 
    when n < @ema_1_intervals then null         
    when n = @ema_1_intervals then t1.close_price * @K1 + @initial_sma_1 * (1-@K1)  
    when n > @ema_1_intervals then t1.close_price * @K1 + @prev_ema_1 * (1-@K1) 
    end,
    @prev_ema_2 = case when n < @ema_2_intervals then null          
    when n = @ema_2_intervals then t1.close_price * @K2 + @initial_sma_2 * (1-@K2)  
    when n > @ema_2_intervals then t1.close_price * @K2 + @prev_ema_2 * (1-@K2)         
    end, 
    @prev_ema_3 = case when n < @ema_3_intervals then null          
    when n = @ema_3_intervals then t1.close_price * @K3 + @initial_sma_3 * (1-@K3)  
    when n > @ema_3_intervals then t1.close_price * @K3 + @prev_ema_3 * (1-@K3)         
    end, 
    @prev_ema_4 = case when n < @ema_4_intervals then null          
    when n = @ema_4_intervals then t1.close_price * @K4 + @initial_sma_4 * (1-@K4)  
    when n > @ema_4_intervals then t1.close_price * @K4 + @prev_ema_4 * (1-@K4)         
    end, 
    @prev_ema_5 = case when n < @ema_5_intervals then null          
    when n = @ema_5_intervals then t1.close_price * @K5 + @initial_sma_5 * (1-@K5)  
    when n > @ema_5_intervals then t1.close_price * @K5 + @prev_ema_5 * (1-@K5)         
    end,            
    ema1 = @prev_ema_1, ema2 = @prev_ema_2, ema3 = @prev_ema_3, ema4 = @prev_ema_4,
    ema5 = @prev_ema_5, @anchor = n --anchor so that carryover works properly   
from temp_ema_data t1 with (TABLOCKX) OPTION (MAXDOP 1)

END

Answer 1

您可以尝试在菜单项下：查询 - >查询选项...

在树上选择结果 - >网格和结果 - >文本（无论哪一个适用于您）都有一个“执行后丢弃结果”复选框”。我没有使用过它，但听起来它可能可以满足您的需要。

编辑：快速测试表明，这会丢弃查询结果以及 PRINT 语句输出。此外，对一个简单存储过程进行 100 次调用的循环从花费几秒钟（主要是显示结果的时间）变为几乎即时运行时间。我想这就是你想要找到的。

Answer 2

您是否尝试将以下内容添加到存储过程的顶部：

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET NOCOUNT ON

Answer 3

基于游标是资源消耗者的事实，如果游标不是必需的，我建议使用 sql server 表变量 作为替代方案。

另请查看此链接，以将基于游标的过程转换为基于表变量的过程。

Answer 4

正如 @Mark Kram 指出的，SET NOCOUNT ON 是您需要的一件事，以防止您向我们展示的存储过程输出。

对于被调用的存储过程POPULATE_EMA，我们无法根据您提供的详细信息知道它输出什么 - 您是否将结果集返回到 SSMS？

如果您每次调用 POPULATE_EMA 时都会返回一个结果集（给客户端），您可以在服务器端INSERT INTO临时表，以避免该表被发送到客户端。这看起来像：

DELETE FROM TA_HISTORY

--This table would need to match the structure of POPULATE_EMA!
CREATE TABLE #TempResults (Column1 Int, Column2 Int) --, etc

DECLARE tables_cursor CURSOR
   FOR
   SELECT symbol, MinDSeqKey, MaxDSeqKey
   FROM STOCK_VITALS;

OPEN tables_cursor;

DECLARE @sym_in NVARCHAR(10);
DECLARE @mink_in bigint;
DECLARE @maxk_in bigint;

FETCH NEXT FROM tables_cursor INTO @sym_in, @mink_in, @maxk_in;

WHILE (@@FETCH_STATUS <> -1)
BEGIN;
    --PRINT 'Now Processing. ' + @sym_in;
   INSERT INTO #TempResults
   EXECUTE POPULATE_EMA @sym_in, 20,50,100,12,26, @mink_in, @maxk_in;

   INSERT INTO TA_HISTORY(SYMBOL, DSEQKEY, EMA20, EMA50, EMA100, EMA12, EMA26)
   SELECT @sym_in, DSEQKEY, EMA1, EMA2,EMA3,EMA4,EMA5
   FROM temp_ema_data
   WHERE @maxk_in - @mink_in > 49

   FETCH NEXT FROM tables_cursor INTO @sym_in, @mink_in, @maxk_in;
END;

CLOSE tables_cursor;

DEALLOCATE tables_cursor;