Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос для рассчета последовательности по FIFO.  [new]
Intension
Member

Откуда:
Сообщений: 17
Доброе утро!
Помогите, пожалуйста, оптимизировать запрос.

Есть две таблицы: в первой все приходы на склад, на уровне артикул / дата прихода / размер / склад / количество. Во второй таблице все "выходы" товара со склада, на точно таком же уровне детализации.
Написал запрос по подбору самого раннего выхода со склада к самому раннему приходу на склад.
Система работает, но проблема в том, что запрос отрабатывает достаточно долго, 1млн записей из таблицы расхода обрабатывается примерно за 30 мин, а обработать нужно в суммарном итоге около 300 млн записей, и делать это нужна на постоянной основе.

Заранее спасибо за любую помощь!

Пример таблиц в картинке в приложении.
А вот сам запрос.

set nocount on

DECLARE @step as int = 1
DECLARE @loops as int = 1
DECLARE @id as int = null


select @loops = max(a.Row#) from [tbl_SLT_orders] as a --общее количество лупов в таблице 2


WHILE @step <= @loops
BEGIN

	SELECT
		@id = min(a.[ID])
	FROM [tbl_SLT_calc] as a
		join [tbl_SLT_orders] as b on 1=1
			AND a.Article	= b.Article
			AND a.SC		= b.SC
			AND a.SIZE		= b.SIZE
			AND b.Row#		= @step
	WHERE a.[Placed_Ext_ID] is null

	Update [tbl_SLT_calc] set [tbl_SLT_calc].[Placed_Ext_ID] = @step WHERE [tbl_SLT_calc].[ID] = @id



set @id = null
set @step = @step + 1
END


p.s. о том что делать лупы на sql я знаю, но другого выхода просто нет.

К сообщению приложен файл. Размер - 20Kb
20 фев 15, 09:13    [17291037]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для рассчета последовательности по FIFO.  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20603
Intension, надо так понимать, что прямой связи прихода с расходом нет - то есть один приход может быть поделен на несколько расходов, причём два из них поделены между ним и другим приходом... верно?
В таком случае думаю, что должна существовать связующая таблица ИД_прихода-ИД_расхода-Количество. Причём таблица исключительно для INSERT и SELECT.
20 фев 15, 09:54    [17291192]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для рассчета последовательности по FIFO.  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
по сабжу: я делал разброс по ФИФО с пом. курсора. В отдельной таблице формировались привязки, кот. потом можно легко прочитать или перепривязать.
Хитрож мудрые ФИФО-запросы-на-лету это тупик, т.к. придется их вставлять в сотню мест. Не говоря уже о реально аццких проблемах с производительностью.
И это еще не все проблемы. :)
20 фев 15, 10:18    [17291305]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для рассчета последовательности по FIFO.  [new]
Intension
Member

Откуда:
Сообщений: 17
Akina
Intension, надо так понимать, что прямой связи прихода с расходом нет - то есть один приход может быть поделен на несколько расходов, причём два из них поделены между ним и другим приходом... верно?
В таком случае думаю, что должна существовать связующая таблица ИД_прихода-ИД_расхода-Количество. Причём таблица исключительно для INSERT и SELECT.


Akina, да, все верно, именно так как ты и написал. Я и пытаюсь создать данную таблицу. Все это делается что бы можно было с точностью до дня определять срок хранения каждой SKU на складе.
20 фев 15, 11:06    [17291608]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для рассчета последовательности по FIFO.  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20603
Ну тогда ты фигнёй маешься.

Открой два курсора по этим таблицам с сортировкой по (артикул, дата) и параллельно их обрабатывай. Храним текущий остаток и его дату. Берём очередной расход. Если остатка хватает - пишем расход, уменьшаем остаток, переходим к следующему расходу. Если нехватает - пишем весь остаток, фетчим следующий приход, берём из него недостающее. Если расходы кончились - фетчим до следующего артикула в приходе. Если кончились приходы - орём, что у нас не работает закон сохранения массы... ну как-то так.

Ещё я бы посоветовал по исчерпании расходов по артикулу вносить в таблицу связи ещё одну запись - дата очередного недовыбранного прихода и нулевое количество. Это позволит в дальнейшем сразу раскидывать расход по приходам, не выполняя поиск прихода, с которого надо начинать раскидывание.
20 фев 15, 11:32    [17291821]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для рассчета последовательности по FIFO.  [new]
MangusJR
Member

Откуда:
Сообщений: 23
Без партионного учета вы все равно это правильно не сделаете, и даже с ним нужен постоянный контроль партии и ротация товаров по срокам хранения, иначе пришедший самым первым товар просто умрет у вас под постоянным пополнением сверху. Как вы вообще собираетесь отличать товар пришедший раньше от товара пришедшего позже? Или вам надо какую-то абстрактную картину получить? Ну тогда будьте готовы к тому, что якобы пришедший вчера товар и только что вставший на хранение - на самом деле у вас там уже три года валяется и всякий товарный вид потерял - это как пример.
20 фев 15, 11:33    [17291837]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для рассчета последовательности по FIFO.  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20603
Списание - тоже расход.
20 фев 15, 11:49    [17291978]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для рассчета последовательности по FIFO.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Задача списания выполняется расчетом нарастающего итога по приходу и расходу.
20 фев 15, 17:22    [17294646]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для рассчета последовательности по FIFO.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
У вас, что - миллион записей по приходу одного и того же товара?
20 фев 15, 17:23    [17294651]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для рассчета последовательности по FIFO.  [new]
Intension
Member

Откуда:
Сообщений: 17
Akina
Ну тогда ты фигнёй маешься.

Открой два курсора по этим таблицам с сортировкой по (артикул, дата) и параллельно их обрабатывай. Храним текущий остаток и его дату. Берём очередной расход. Если остатка хватает - пишем расход, уменьшаем остаток, переходим к следующему расходу. Если нехватает - пишем весь остаток, фетчим следующий приход, берём из него недостающее. Если расходы кончились - фетчим до следующего артикула в приходе. Если кончились приходы - орём, что у нас не работает закон сохранения массы... ну как-то так.

Ещё я бы посоветовал по исчерпании расходов по артикулу вносить в таблицу связи ещё одну запись - дата очередного недовыбранного прихода и нулевое количество. Это позволит в дальнейшем сразу раскидывать расход по приходам, не выполняя поиск прихода, с которого надо начинать раскидывание.



Akina, спасибо за наводку, подумаю в этом направлении. Надо будет посмотреть разницу в производительность.
25 фев 15, 19:15    [17312163]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для рассчета последовательности по FIFO.  [new]
Intension
Member

Откуда:
Сообщений: 17
Akina
Списание - тоже расход.


Akina, да, конечно, этот нюанс тоже учитывается. У каждого goods issue есть свой type, характеризиющий его.
25 фев 15, 19:16    [17312167]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для рассчета последовательности по FIFO.  [new]
Intension
Member

Откуда:
Сообщений: 17
Владислав Колосов
Задача списания выполняется расчетом нарастающего итога по приходу и расходу.



Владислав, к сожаления, нарастающий итог мне тут не поможет, так как мне нужно понять сколько именно "гипотетически" пролежала каждая единица SKU на складе (т.е., количество дней между goods receipt и goods issue основываясь на принципе first in first out).
25 фев 15, 19:20    [17312180]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для рассчета последовательности по FIFO.  [new]
Intension
Member

Откуда:
Сообщений: 17
Владислав Колосов
У вас, что - миллион записей по приходу одного и того же товара?


Владислав, да все верно. Хотя не думаю что найдется SKU с суммарным оборотом за все время более 1 млн. За 5 лет в данной ERP системе, в таблице goods receipt примерно 250 млн транзакций (одна транзакция = 1 штучка прихода), соответственно, в таблице goods issue примерно такой же порядок цифр.
25 фев 15, 19:25    [17312189]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для рассчета последовательности по FIFO.  [new]
idontcare
Member

Откуда:
Сообщений: 7
Intension
Владислав Колосов
Задача списания выполняется расчетом нарастающего итога по приходу и расходу.



Владислав, к сожаления, нарастающий итог мне тут не поможет, так как мне нужно понять сколько именно "гипотетически" пролежала каждая единица SKU на складе (т.е., количество дней между goods receipt и goods issue основываясь на принципе first in first out).


А Вы уверены, что вариант, предложенный Владиславом, Вам не подойдет?
Вроде все подходит - и период нахождения товара учтён, и его количество.
Пример:

DECLARE @in table
(
    in_rn                    int,
    in_operday               date,
    in_operation_sum         money
)
INSERT @in (in_rn, in_operday, in_operation_sum)
VALUES 
(1, '2015-01-01', 100),
(2, '2015-01-04', 200),
(3, '2015-01-08', 300),
(4, '2015-01-16', 400)




DECLARE @out table
(
    out_rn                  int,
    out_operday             date,
    out_operation_sum       money
)
INSERT @out (out_rn, out_operday, out_operation_sum)
VALUES
(1, '2015-01-05', 70 ),
(2, '2015-01-10', 10 ),
(3, '2015-01-12', 100),
(4, '2015-01-19', 440)




;WITH
cte_in AS
(
    SELECT
        r.*,
        --  нарастающий итог по приходу без учета текущей операции
        in_amount_before = ISNULL(SUM(r.in_operation_sum) OVER(ORDER BY r.in_operday ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0),
        --  нарастающий итог по приходу с учетом текущей операции
        in_amount_after  = SUM(r.in_operation_sum) OVER(ORDER BY r.in_operday ROWS UNBOUNDED PRECEDING)
    FROM @in            r
),
cte_out AS
(
    SELECT
        w.*,
        out_amount_before = ISNULL(SUM(w.out_operation_sum) OVER(ORDER BY w.out_operday ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0),
        out_amount_after  = SUM(w.out_operation_sum) OVER(ORDER BY w.out_operday ROWS UNBOUNDED PRECEDING)
    FROM @out           w
),
cte_pre_calc AS
(
    SELECT
        r.*,
        w.*,
        --  масимально возможная сумма списания из пополнения
        out_sum_max     = r.in_amount_after - w.out_amount_before,
        --  сумма предыдущих снятий, в разрезе одного снятия, если оно разбивается на несколько
        prev_out_sum    = LAG(r.in_amount_after - w.out_amount_before, 1, 0) OVER(PARTITION BY w.out_rn ORDER BY w.out_operday)
    FROM cte_in                             r
        INNER JOIN cte_out                  w
            ON r.in_operday <= w.out_operday            --  здесь считаем, что на одну и ту же дату пополнения происходят раньше снятий
            AND r.in_amount_before < w.out_amount_after
            AND r.in_amount_after > w.out_amount_before
)
SELECT
    --cte.*,
    cte.in_operday,
    cte.out_operday,
    out_sum =   CASE
                    WHEN cte.out_sum_max >= cte.out_operation_sum
                        THEN cte.out_operation_sum - cte.prev_out_sum
                    ELSE cte.out_sum_max - cte.prev_out_sum
                END
FROM cte_pre_calc       cte
ORDER BY cte.out_rn, cte.in_rn
5 мар 15, 17:56    [17348776]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить