Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Почему не работает процедура?  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Добрый день, коллеги.

Есть незатейливая процедура,
+

/*
Procedure:  S_DBC_delivery_by_carrier
Schema:     report
Database:   distribution
Server:     TRI SQL Server
Author:     OVEN
Date:       8.6.2009
Info:
Parameters:
Modified:
===================================================================================================
*/
--Create PROCEDURE [report].[S_DBC_delivery_by_carrier]
--(
declare    @car_code               INT=-1,
    @from_out_stock_date    DATETIME='20160101',
    @to_out_stock_date      DATETIME='20160104',
    @debug                  BIT = 0
--)
--AS
BEGIN

    SET FMTONLY OFF;

    IF (@car_code = -1) SET @car_code = NULL
    
    SELECT
        deliv_recept_date = dbo.FN_FlatDate(DATEADD(DAY, PBO.pbo_deliv_recept_day, PBO.pbo_preparation_date)),
        CAD.cus_code,
        CAD.vou_code,
        DEL.dad_code,
        DEL.da1_code,
        DEL.car_code,
        DEL.cdd_code,
        CDD.cdd_name,
        DEL.del_number,
        DEL.ord_number,
        DEL.del_amount,
        PBO.pbo_code,
        pbo_type = 'PRO'
    INTO
        #T_PBO_IDS
    FROM
        dbo.T_PBO_produced_box PBO
    JOIN
        dbo.T_DEL_delivery DEL
        ON  DEL.del_number = PBO.del_number
    JOIN
        dbo.T_DAD_deliv_addr DAD
        ON  DAD.dad_code = DEL.dad_code
    JOIN
        dbo.T_CAD_custom_addr CAD
        ON  CAD.cad_code = DAD.cad_code
    JOIN
        dbo.T_CDD_carrier_delivery_detail CDD
        ON  CDD.cdd_code = DEL.cdd_code
    WHERE
        ((@car_code IS NULL) OR (@car_code = DEL.car_code))
        AND DATEADD(DAY, PBO.pbo_deliv_recept_day, PBO.pbo_preparation_date) BETWEEN @from_out_stock_date AND @to_out_stock_date

    UNION

	SELECT
		deliv_recept_date = dbo.FN_FlatDate(DATEADD(DAY, IDS.pbo_deliv_recept_day, IDS.pack_date)),
        CAD.cus_code,
        CAD.vou_code,
        DEL.dad_code,
        DEL.da1_code,
        DEL.car_code,
        DEL.cdd_code,
        CDD.cdd_name,
        DEL.del_number,
        DEL.ord_number,
        DEL.del_amount,
        PBO.pbo_code,
        pbo_type = 'SKL2'
	FROM
	    dbo.T_PBO_produced_box PBO
	JOIN
	    dbo.T_IDS_invoice_details_stock IDS
		ON  IDS.pbo_code = PBO.pbo_code
	JOIN
	    dbo.T_DEL_delivery DEL
		ON  IDS.del_number = DEL.del_number
	LEFT JOIN
        dbo.T_DAD_deliv_addr DAD
        ON  DAD.dad_code = DEL.dad_code
    LEFT JOIN
        dbo.T_CAD_custom_addr CAD
        ON  CAD.cad_code = DAD.cad_code
	JOIN
	    dbo.T_CDD_carrier_delivery_detail CDD
	    ON  CDD.cdd_code = DEL.cdd_code
	WHERE
	    ((@car_code IS NULL) OR (@car_code = DEL.car_code))
	    AND DATEADD(DAY, IDS.pbo_deliv_recept_day, dbo.FN_FlatDate(IDS.pack_date)) BETWEEN @from_out_stock_date AND @to_out_stock_date


    IF @debug = 1
    BEGIN
        SELECT 'SELECT * FROM #T_PBO_IDS (PRO)';
        SELECT * FROM #T_PBO_IDS WHERE pbo_type = 'PRO';
        SELECT 'SELECT * FROM #T_PBO_IDS (SKL2)';
        SELECT * FROM #T_PBO_IDS WHERE pbo_type = 'SKL2';
    END


    SELECT
        deliv_recept_date,
        cdd_code,
        cdd_name,
        count_cus_code = COUNT(DISTINCT cus_code),
        count_del_number = COUNT(DISTINCT del_number),
        cout_pbo_code = COUNT(DISTINCT pbo_code)
    FROM
        #T_PBO_IDS
    GROUP BY
        deliv_recept_date,
        cdd_code,
        cdd_name


END

drop table #T_PBO_IDS


Если её запустить вот так, как она исправлена - как батч Management Studio, то результат будет через 8 секунд.

SQL:BatchCompleted	CPU 1513	READS 68572	WRITES 10	DURATION 1545	StartTime 2016-01-25 16:03:30.630	EndTime 2016-01-25 16:03:32.177	


Но если вызвать процедуру, в которую этот код завёрнут, что результата можно дождаться на заведомо пустой выборке, если же в выборку попадают данные, то результата дождаться не удаётся. Через 10 минут я его убил и вижу
SQL:BatchCompleted	CPU 575893	READ 16336423	WRITES 0	DURATION 617408	StartTime 2016-01-25 16:07:43.607	EndTime 2016-01-25 16:18:01.013		

Процедура создана на схеме [report], с которой работает ReportManager. До сегодняшнего дня всё работало, а сейчас всё встало. Пересоздание процедуры эфекта не даёт, что за нафиг??
25 янв 16, 18:25    [18727467]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
Ну план строится плохой, скорее всего.
25 янв 16, 18:30    [18727512]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Crimean
Member

Откуда:
Сообщений: 13148
AND DATEADD(DAY, IDS.pbo_deliv_recept_day, dbo.FN_FlatDate(IDS.pack_date)) BETWEEN @from_out_stock_date AND @to_out_stock_date

"так мило"...
25 янв 16, 18:32    [18727525]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
((@car_code IS NULL) OR (@car_code = DEL.car_code))

это плохая конструкция, попробуйте по частям для начала.
25 янв 16, 18:34    [18727542]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Да, проглядел... То, где dateadd() вообще не будет оптимизировано плюс заколочено скалярной функцией.
25 янв 16, 18:36    [18727558]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
o-o
Guest
а вот это случаем не скалярище?
dbo.FN_FlatDate(DATEADD(DAY, PBO.pbo_deliv_recept_day, PBO.pbo_preparation_date))

и не дай бог лезет в какую-то таблицу...
25 янв 16, 18:44    [18727584]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Владислав Колосов
((@car_code IS NULL) OR (@car_code = DEL.car_code))

это плохая конструкция, попробуйте по частям для начала.


Зачам по частям? В ManagementStudio оборот за месяц всё обрабатывается за 2 секунды, а если всё в запрос обернуть, оно 1 день разжевать не может. Тут явно не в синтаксисе дело, а какой-то глюк рангом по-выше...

Создаю новую процедуру TEST_TEST_delivery_by_carrier, пытаюсь её запустить на 2016-01-04, и всё, не работает вообще.

Если запустить за 3 января, то планы запросов получаются разные, если запускаит как батч, или как процедуру. Но процедура зависает, если в выборку попадают данные, а батч обрабатывает без проблемм выборку вплоть до года

К сообщению приложен файл (plan.zip - 25Kb) cкачать
25 янв 16, 18:55    [18727629]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Шыфл,

option( recompile ) вам в помощь тогда
25 янв 16, 19:00    [18727648]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
aleks2
Guest
Шыфл
Тут явно не в синтаксисе дело, а какой-то глюк рангом по-выше...


Да, ты прав. Дело тут в генетическом коде.

ЗЫ. Если тебе, страдалец, лень написать очевидный union ALL, вместо, ведущего к куче лишней работы, union - ты заслужил свою участь.
25 янв 16, 19:04    [18727666]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
o-o
Guest
dbo.T_CDD_carrier_delivery_detail
вот етого по HASH JOIN насильно соединяйте, у вас оценки вообще не те, он делает NL, получая дурдом
25 янв 16, 19:12    [18727689]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
o-o
dbo.T_CDD_carrier_delivery_detail
вот етого по HASH JOIN насильно соединяйте, у вас оценки вообще не те, он делает NL, получая дурдом


О! Вот это я понимаю совет - всё сразу заработало! Примного благодарствую и бью челом - пока что сам в ехесution plan разобраться не могу.
25 янв 16, 19:37    [18727758]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
o-o
Guest
Шыфл,

у вас по описанию проблема в прослушанных параметрах.
когда кусок кода выдернут из процедуры, это уже переменные, в бэтче они не прослушиваются, оценка идет для unknown. и это оказалось лучше, чем план с учетом каких-то значений переменных.
я нормально гляну попозже, это мое с хэшем не решение, а временный костыль, просто видно, что он абсолютно неправильно оценил. а будут параметры как "в первый раз", и с хэшем будет не то, на малом числе строк надо бы NL, как он и выбрал
25 янв 16, 20:22    [18727899]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
o-o
Guest
вы ввели в заблуждение оптимизатор вот этим вот:
IF (@car_code = -1) SET @car_code = NULL
+
where ((@car_code IS NULL) OR (@car_code = DEL.car_code))
ваше идея тут, видимо, такая:
если передается конкретный код, по нему фильтруем dbo.T_DEL_delivery,
а если код NULL, то вообще все возвращаем.
теперь вы запускаете процедуру в самый первый раз с @car_code = -1.
у вас план для процедуры оптимизирован под -1, это в плане сохранено.
теперь надо понять, сколько строк отфильтруется по условию @car_code = DEL.car_code
оптимизатор смотрит распределение car_code и видит, что никакого -1 там нет.
но т.к. он не оценивает ничего в 0 строк, он оценил в 1 строку.
итак, выборка из dbo.T_DEL_delivery это одна строка,
значит, с остальными таблицами выгодно соединить вложенными циклами.
и тут -- сюрприз.
внутри процедуры вы переопределяете -1 на NULL.
т.е. "dbo.T_DEL_delivery не вернет ничего" превращается в "dbo.T_DEL_delivery отдаст все строки"

ну откуда ему знать, что вы параметр внутри процедуры переопределяете на прямо противоположный
25 янв 16, 23:48    [18728475]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
o-o
Guest
в общем, как вам уже посоветовали -- 18727648 --
option (recompile) самому запросу внутри процедуры.
чтобы оптимизатор заново строил план каждый раз,
но не для всей процедуры, а то снова прослушает -1 ("такого нет"), а возвращать придется все (null),
а только для запроса, когда уже параметр переопределен и оптимизатор увидит null вместо -1
26 янв 16, 00:05    [18728501]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Шыфл
o-o
dbo.T_CDD_carrier_delivery_detail
вот етого по HASH JOIN насильно соединяйте, у вас оценки вообще не те, он делает NL, получая дурдом


О! Вот это я понимаю совет - всё сразу заработало!
o-o
это мое с хэшем не решение, а временный костыль
Не надо было сразу то костыли сливать. Щас вы ТСа уже не переубедите что это плохое решение. Все же заработало! Да и согласно экспертному мнению "явно не в синтаксисе дело".


А вообще можно попробовать сделать без костылей и не прибегая к OPTION(RECOMPILE). Нужно убрать вот эту ерунду:
IF (@car_code = -1) SET @car_code = NULL

а в условие обоих запросов добавить:
((@car_code IS NULL) OR (@car_code = DEL.car_code) OR (@car_code = -1))
26 янв 16, 02:44    [18728593]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
o-o
Guest
Mind
... Щас вы ТСа уже не переубедите что это плохое решение. Все же заработало!

а я попробую.
Шыфл, хинтование это говнорешение
это было в спешке, лишь бы сразу ответить, потому что из меня прет, руки вперед головы.
вот сейчас стыдно, но этого мало, раз все равно сперва пишу, потом думаю.
попробуйте переписать, как Mind предложил, и накрайняк option(recompile)
26 янв 16, 08:57    [18728845]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
o-o
Guest
не, я все же за option (recompile).
по методу Mind-а никакой рекомпиляции и теперь все равно все упирается в самый первый запуск.
у меня в процедуре примитивное чтение таблицы чисел dbo.Nums
CREATE TABLE [dbo].[nums](
	[n] [int] NOT NULL,
 CONSTRAINT [PK_nums_n] PRIMARY KEY CLUSTERED ([n] ASC))

create proc dbo.sp_shyfl_mind
   @n int
as 
   select *
   from dbo.Nums 
   where ((@n IS NULL) OR (@n = n) OR (@n = -1)); 

если @n NULL или -1, надо прочесть всю таблицу,
но если на входе любое другое число, надо всего 1 строку вернуть.
и что.
первый запуск с -1, в плане скан, ок, все довольны.
но этот план закэшировался и теперь я запускаю с 1, мне нужен поиск по индексу,
а у меня снова скан.

К сообщению приложен файл. Размер - 82Kb
26 янв 16, 13:25    [18730104]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
o-o,

экранировать параметр?

alter proc dbo.sp_shyfl_mind 
   @n1 int
as
 
declare @n int 
set @n = @n1

   select *
   from dbo.Nums 
   where ((@n IS NULL) OR (@n = n) OR (@n = -1)); 
26 янв 16, 14:21    [18730512]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
o-o
Guest
WarAnt,

не надо ниче экранировать.
у него 2 ситуации: или параметp имеет вменяемое значение и фильтрует,
и мы в плане хотим видеть NL
или же передается NULL или -1, и читается вся таблица.
и если мы хотим 2 разных плана под это дело,
то или в юнион это OR разложить,
или 2 процедуры запускать в зависимости от параметра,
или стейтмент перекомпилировать каждый раз -- OPTION(RECOMPILE)
26 янв 16, 15:01    [18730787]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
komrad
Member

Откуда:
Сообщений: 5251
o-o
или стейтмент перекомпилировать каждый раз -- OPTION(RECOMPILE)

можно еще OPTION (OPTIMIZE FOR UNKNOWN)
26 янв 16, 15:44    [18731047]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
o-o
Guest
komrad
o-o
или стейтмент перекомпилировать каждый раз -- OPTION(RECOMPILE)

можно еще OPTION (OPTIMIZE FOR UNKNOWN)

так он получит вечный хэшевый план.
у него в выдернутом из процедуры виде как раз с переменными --
натуральное unknown.
я за идею двух планов, причем раз *отчет*, пускай перекомпилируется.
от перекомпиляций DWH не страдает, ему только выгоднее становится.
там время чтения куда больше интересует, чем время на компиляцию
26 янв 16, 15:50    [18731086]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Addx
Member

Откуда:
Сообщений: 957
Шыфл

Если её запустить вот так, как она исправлена - как батч Management Studio, то результат будет через 8 секунд.

Но если вызвать процедуру, в которую этот код завёрнут, что результата можно дождаться на заведомо пустой выборке, если же в выборку попадают данные, то результата дождаться не удаётся. Через 10 минут я его убил и вижу

[Процедура создана на схеме [report], с которой работает ReportManager. До сегодняшнего дня всё работало, а сейчас всё встало. Пересоздание процедуры эфекта не даёт, что за нафиг??


Это нормально. Строятся разные планы - получается разное время. С чего бы пересоздание процедуры должно обязательно дать эффект?

o-o
хинтование это говнорешение


Вот когда оптимизатор будет строить идеальные планы, сразу соглашусь. Разумеется, хинтами увлекаться не надо, и пытаться ими говнокод разогнать тоже, но в некоторых случаях это суровая необходимость.
А принудительно ставить каждый раз OPTION(RECOMPILE) - тоже ничего хорошего.
26 янв 16, 15:53    [18731105]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Addx
Вот когда оптимизатор будет строить идеальные планы, сразу соглашусь.

А ваш хинт строит идеальный план на все времена ?
26 янв 16, 15:58    [18731135]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
o-o
Mind
... Щас вы ТСа уже не переубедите что это плохое решение. Все же заработало!

а я попробую.
Шыфл, хинтование это говнорешение
это было в спешке, лишь бы сразу ответить, потому что из меня прет, руки вперед головы.
вот сейчас стыдно, но этого мало, раз все равно сперва пишу, потом думаю.
попробуйте переписать, как Mind предложил, и накрайняк option(recompile)


Простите, почему это хинтование - плохо? В данном конкретном случае HASH - самый эффективный, даже если задан однозначный параметр. С merge - тоже работает, процентов на 10 медленнее, а loop - просто не работает, или работает очень медленно (в несколько раз), так почему ж популярно не объяснить оптимайзеру, что ему делать?
26 янв 16, 16:07    [18731210]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Шыфл
так почему ж популярно не объяснить оптимайзеру, что ему делать?

Ага. Сначала написать такую херню в WHERE, а потом искренне помогать оптимизатору хинтованием
26 янв 16, 16:10    [18731224]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить