#sql #sql-server #stored-procedures
#sql #sql-сервер #хранимые процедуры
Вопрос:
SQL Server 2008 R2. Запуск хранимой процедуры с помощью цикла, управляемого курсором, из SS Management Studio. (ниже). После 3000 из 9000 циклов я получаю сообщение об ошибке нехватки памяти. Я полагаю, что это связано только с SS Mgmt Studio. Итак, как мне отключить вывод как вызывающей процедуры (ниже), так и вызываемого SPROC? Я открыт для другого подхода к вызову 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 — ВОТ SPROC — Мне НУЖЕН SPROC ДЛЯ РАБОТЫ С более чем 9000 СТРОКАМИ В ДРУГОЙ ТАБЛИЦЕ.
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
Комментарии:
1. Что делает ваша хранимая процедура ‘populate_ema’? Если вы выполняете выборки любого рода, это приведет к выводу результата — если только выборки не будут вставлены (как указано выше). Вы не можете подавить выходные результаты, если вы выполнили ‘select’ — вот что делает ‘select’…
2. @M.R. — ну, вы можете, повторно вставив результаты в другую таблицу, если sproc возвращает только один результирующий набор
3. Я не могу придумать никакой мыслимой причины, по которой я бы использовал курсор для любой вставки. Это просто плохое кодирование. Вы должны создать процедуру так, чтобы она принимала табличную переменную для ввода и выполняла операцию на основе набора, или вам ВООБЩЕ НЕ следует использовать дочернюю процедуру и выполнять вставку на основе набора в вызывающей процедуре. То, как это написано, всегда будет занимать слишком много времени и приведет к снижению производительности вашей системы. Исправьте это сейчас.
4. @tao — вот что я сказал — вы можете отключить это, только если вы вставляете … в противном случае прямой выбор выдаст результаты…
5. @M.R. справедливо, я (неправильно) понял ваш комментарий как утверждение, что результаты этого вызова процедуры не могут быть захвачены / удалены из предоставленного нам контекста (без изменения процедуры, которая выполняет выбор)
Ответ №1:
Вы можете попробовать в пункте меню: Запрос-> Параметры запроса…
В дереве выберите Результаты-> Сетка и Результаты-> Текст (в зависимости от того, что для вас применимо), там установлен флажок «Отменить результаты после выполнения». Я не использовал его, но, похоже, он может сделать то, что вам нужно.
РЕДАКТИРОВАТЬ: Быстрый тест показывает, что при этом отбрасываются как результаты запроса, так и вывод инструкции ПЕЧАТИ. Кроме того, цикл из 100 вызовов простой хранимой процедуры занял от нескольких секунд (в основном время, затрачиваемое на отображение результатов) до практически мгновенного времени выполнения. Я думаю, это то, что вы ищете.
Ответ №2:
Вы пробовали добавить следующее в начало своего SPROC:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET NOCOUNT ON
Комментарии:
1. Что для вас делают ANSI_NULLS и ANSI_WARNINGS? NOCOUNT избавится от количества строк, но не от фактических результатов.
Ответ №3:
Исходя из того факта, что курсоры являются ресурсосберегающими, если курсоры не являются обязательным требованием, я бы предложил использовать табличные переменные sql server в качестве альтернативы.
Также посмотрите на эту ссылку для преобразования процедуры на основе курсора в процедуру на основе табличной переменной.
Ответ №4:
Как указывает @Mark Kram, SET NOCOUNT ON это единственное, что вам нужно, чтобы предотвратить вывод из sproc, который вы нам показали.
Для вызванного sproc 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;