gifts2017

Получение реквизитов движений для множества документов в рамках одного запроса

Опубликовал Юрий Строжевский (y-str) в раздел Программирование - Практика программирования

Данная статья описывает механизм получения всех реквизитов для движений партионного учета для всех документов, по всей номенклатуре, в рамках одного запроса. В качестве теоретической базы для данной статьи используется материал двух предыдущих статей автора – «Использование нарастающих итогов в партионном учете и не только» и «Вычисление нарастающего итога для N таблиц в рамках одного запроса».

В статье «Использование нарастающих итогов в партионном учете и не только» рассматривался вопрос получения всех реквизитов движений партионного учета (списания) для всей номенклатуры только одного документа. Зачастую построение движений партионного учета (списание) в организациях выполняется уже после ввода всех документов прихода и расхода товаров, сразу для всех документов расхода. Ниже рассматривается вариант решения данной задачи.

Для начала вспомним запрос, позволяющий решать аналогичную задачу для одного документа:

SELECT
 ТаблицаНоменклатуры.Номенклатура,
 tab1.Партия,
 CASE
  WHEN
   ( SUM( ISNULL( tab2.КоличествоОстаток, 0 ) ) + MAX( ISNULL( tab1.КоличествоОстаток, 0 ) ) ) > MAX( ТаблицаНоменклатуры.Количество )
  THEN
   MAX( ТаблицаНоменклатуры.Количество ) - SUM( ISNULL( tab2.КоличествоОстаток, 0 ) )
 ELSE
  MAX( ISNULL( tab1.КоличествоОстаток, 0 ) ) 
 END AS КоличествоСписания
FROM ТаблицаНоменклатуры
LEFT JOIN РегистрНакопления.ОстаткиНоменклатуры.Остатки AS tab1
ON ТаблицаНоменклатуры.Номенклатура = tab1.Номенклатура
LEFT JOIN РегистрНакопления.ОстаткиНоменклатуры.Остатки AS tab2
ON ( tab1.Номенклатура = tab2.Номенклатура ) AND ( tab1.Партия > tab2.Партия )
GROUP BY ТаблицаНоменклатуры.Номенклатура, tab1.Партия
HAVING SUM( ISNULL( tab2.КоличествоОстаток, 0 ) ) < MAX( ТаблицаНоменклатуры.Количество );

Вполне логичным решением для задачи обобщения данного запроса на случай множества документов вроде бы является следующий запрос:

SELECT
 ТаблицаДокументов.Документ,
 ТаблицаДокументов.Номенклатура,
 tab1.Партия,
 CASE
  WHEN
   ( SUM( ISNULL( tab2.КоличествоОстаток, 0 ) ) + MAX( ISNULL( tab1.КоличествоОстаток, 0 ) ) ) > MAX( ТаблицаДокументов.Количество )
  THEN
   MAX( ТаблицаДокументов.Количество ) - SUM( ISNULL( tab2.КоличествоОстаток, 0 ) )
 ELSE
  MAX( ISNULL( tab1.КоличествоОстаток, 0 ) ) 
 END AS КоличествоСписания
FROM ТаблицаДокументов 
LEFT JOIN РегистрНакопления.ОстаткиНоменклатуры.Остатки AS tab1
ON ТаблицаДокументов.Номенклатура = tab1.Номенклатура
LEFT JOIN РегистрНакопления.ОстаткиНоменклатуры.Остатки AS tab2
ON ( tab1.Номенклатура = tab2.Номенклатура ) AND ( tab1.Партия > tab2.Партия )
GROUP BY ТаблицаДокументов.Документ, ТаблицаДокументов.Номенклатура, tab1.Партия
HAVING SUM( ISNULL( tab2.КоличествоОстаток, 0 ) ) < MAX( ТаблицаДокументов.Количество );

Но на самом деле этот запрос является ошибочным. Основная проблема этого запроса это отсутствие «истории» списания партий по предыдущим строкам запроса. Поясню на следующем примере: допустим, мы имеем два документа и три партии, две из которых полностью списываются на первый документ, а одна - на второй. В приведенном выше запросе для первого документа списание пройдет правильно, а вот для второго документа количество с первой и второй партии будут списаны еще раз, так как нет информации какие именно партии и в каком количестве были списаны для предыдущего документа.

Однако, надо заметить, вопрос хранения «истории» уже решался - для правильного списания партий для одного документа и всего набора номенклатуры тоже необходима «история» предыдущих «списаний». В качестве механизма «сохранения истории» было применено вычисление «нарастающего итога» по таблице партий (в сумме «нарастающего итога» присутствовует информация о цифрах из предыдущих партий, что и позволяет учитывать «историю» по таблице партий). Следовательно, для получения возможности учитывать «историю» для «ТаблицыДокументов» нам необходима возможность получения по ней «нарастающего итога».

Таким образом, для получения запроса, дающего возможность «использовать историю» как для таблицы партий, так и для таблицы документов, необходима возможность получения нарастающих итогов для двух таблиц в рамках одного запроса. Теоретическая часть решения данной проблемы уже была описана ранее мною в статье «Вычисление нарастающего итога для N таблиц в рамках одного запроса». Здесь я приведу только конечный запрос:

SELECT
 tab_doc_1.Документ,
 tab_doc_1.Номенклатура,
 SUM( tab_doc_2.Количество) 
 \
 CASE
  WHEN
   COUNT( DISTINCT tab_part_2.Партия ) = 0
  THEN
   1
 ELSE
  COUNT( DISTINCT tab_part_2.Партия )
 END AS ДокСуммаПредыдущих,
 tab_part_1.Партия,
 SUM( tab_part_2.КоличествоОстаток ) 
 \
 CASE
  WHEN
   COUNT( DISTINCT tab_doc_2.Документ ) = 0
  THEN
   1
 ELSE
  COUNT( DISTINCT tab_doc_2.Документ )
 END AS ПартСуммаПредыдущих
FROM ТаблицаДокументов AS tab_doc_1
LEFT JOIN ТаблицаДокументов AS tab_doc_2
ON tab_doc_1.Документ > tab_doc_2.Документ
LEFT JOIN ТаблицаПартий AS tab_part_1
ON tab_doc_1.Номенклатура = tab_part_1.Номенклатура
LEFT JOIN ТаблицаПартий AS tab_part_2
ON ( tab_part_1.Номенклатура = tab_part_2.Номенклатура ) AND ( tab_part_1.Партия > tab_part_2.Партия )
GROUP BY tab_doc_1.Документ, tab_doc_1.Номенклатура, tab_part_1.Партия
ORDER BY tab_doc_1.Документ, tab_doc_1.Номенклатура, tab_part_1.Партия

В данном запросе есть вычисление «нарастающего итога» для двух таблиц, есть соединение «таблицы документов» и «таблицы партий» (каждая строка из «таблицы документов» соединяется со всеми строками из «таблицы партий»). Однако для окончательного решения вопроса нахождения всех реквизитов для движений списания осталось решить несколько вопросов. Во-первых, для каждого отдельного документа из «таблицы документов» важен не весь список партий, а только некоторые из них, партии с которых будет производиться списания по номенклатуре данного конкретного документа. Следовательно, необходимо получить выражение, ограничивающее список партий для каждого конкретного документа. Во-вторых, необходимы формулы, по которым будут рассчитываться значения числовых реквизитов движений списания (количество и сумма списания).

ВНИМАНИЕ!!!
В следующих запросах намеренно опущено деление на соответствующие множители при вычислении нарастающего итога (см. «Вычисление нарастающего итога для N таблиц в рамках одного запроса»). Сделано это для облегчения понимания материала читателем.

Сначала рассмотрим решение вопроса об ограничении списка партий для каждого конкретного документа.

Для дальнейшего правильного понимания всех нюансов проиллюстрируем «взаимоположения» документов и партий следующим рисунком:

ПОЯСНЕНИЕ.

На рисунках изображены все возможные варианты «взаимоположения» документов из таблицы документов и партий из таблицы партий. Для упрощения приняты следующие сокращения:

SUM(Док) - сумма по всем предыдущим документам;
SUM(Док)+MAX(Док) - нарастающий итог по документам;
SUM(Парт) - сумма по всем предыдущим партиям;
SUM(Парт)+MAX(Парт) - нарастающий итог по партиям;

Первично вернемся к запросу для одного документа и рассмотрим его выражение для ограничения списка партий:

HAVING SUM( ISNULL( tab2.КоличествоОстаток, 0 ) ) < MAX( ТаблицаНоменклатуры.Количество )

Учитывая то, что для множества документов необходимо учитывать предыдущую «историю» получим следующее выражение для ограничения списка партий «сверху» (отсекаем партии, которые будут участвовать в списаниях для документов, следующих после данного конкретного документа):

SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) < SUM( ISNULL( tab_doc_2.Количество, 0 ) ) + MAX( tab_doc_1.Количество )

Так как одна и та же партия может участвовать в списаниях для нескольких соседних документов, то кроме ограничения для списка партий «сверху» понадобиться также ограничение и «снизу» (ограничение «снизу» отсекает партии, которые были полностью списаны в движениях по документам, предшествующих данному конкретному документу). В запросе для одного документа это ограничение отсутствовало так как «точкой отсчета» для партий всегда являлся 0 (то есть можно было записать «SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) > 0», что не имело бы смысла).

Выражение для ограничения списка партий «снизу» имеет следующий вид:

SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) > SUM( ISNULL( tab_doc_2.Количество, 0 ) ) - MAX( tab_part_1.КоличествоОстаток)

Полное выражение для ограничения списка партий приведено в следующем запросе:

HAVING 
( SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) < SUM( ISNULL( tab_doc_2.Количество, 0 ) ) + MAX( tab_doc_1.Количество ) ) 
AND
( SUM( ISNULL( tab_doc_2.Количество, 0 ) ) - MAX( tab_part_1.КоличествоОстаток) < SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) )

<ПРЕДУПРЕЖДЕНИЕ. Так как в выражении для ограничения списка партий применяется ограничение «сверху» и «снизу» для одной и той же величины, то возникает естественное желание оптимизации с помощью оператора BETWEEN. Единственная проблема здесь - данный оператор включает как верхнюю, так и нижнюю границы в область сравнения (фактически оператор сравнения «>» заменяется на «>=»). В результате это приводит к возникновению «пустых» движений - в выходной таблице запроса могут появиться строки с нулевыми значениями для количества и суммы списания.>

Теперь перейдем к выражению для получения количества списания для каждой конкретной партии. Прежде всего, опять запишем аналогичное выражение для случая одного документа:

CASE
 WHEN
  ( SUM( ISNULL( tab2.КоличествоОстаток, 0 ) ) + MAX( ISNULL( tab1.КоличествоОстаток, 0 ) ) ) > MAX( ТаблицаНоменклатуры.Количество )
 THEN
  MAX( ТаблицаНоменклатуры.Количество ) - SUM( ISNULL( tab2.КоличествоОстаток, 0 ) )
ELSE
 MAX( ISNULL( tab1.КоличествоОстаток, 0 ) )
END

Затем перепишем выражение в следующем виде:

CASE
 WHEN
  ( SUM( ISNULL( tab2.КоличествоОстаток, 0 ) ) + MAX( ISNULL( tab1.КоличествоОстаток, 0 ) ) )
  > 
  MAX( ТаблицаНоменклатуры.Количество )
 THEN
  MAX( ТаблицаНоменклатуры.Количество )
ELSE
 SUM( ISNULL( tab2.КоличествоОстаток, 0 ) ) + MAX( ISNULL( tab1.КоличествоОстаток, 0 ) )
END
-
SUM( ISNULL( tab2.КоличествоОстаток, 0 ) )

Последний запрос был переписан для иллюстрации того факта, что для вычисления количества списания для данной партии по данной номенклатуре используется разность между «верхней границей» (выражение под CASE) и «нижней границей». Рассмотрим обе эти «границы» более подробно. Для начала еще раз вернемся к рисунку, иллюстрирующему «взаимоположение» документов и партий (см. выше). Для случая одного документа и нескольких партий варианты «взаимодействия» ограничиваются рисунками с номерами 1 и 2. Как видно «нижняя граница» для случая одного документа всегда будет постоянна - одна и та же партия участвует только в списании по одному документу, так что варианты, иллюстрированные на рисунках 3 и 4, для случая одного документа отсутствует (точнее вырождаются в варианты, изображенные на рисунках 1 и 2 соответственно так как «нижняя граница» самой первой партии всегда равна 0). «Верхняя граница» определяется условием превышения нарастающего итога по таблице партий над общим количеством номенклатуры.

Для случая, когда в запросе присутствует множество документов со своими списками номенклатуры, ситуация остается примерно такой же, как и для случая одного документа. Выражение для количества списания также определяется разностью «верхней» и «нижней» границ. Добавляется только несколько отличительных моментов. Во-первых, необходимо учитывать «историю» как для таблицы партий, так и для таблицы документов. Следовательно, в выражении для «верхней границы» должна появиться «история» (сумма по предыдущим документам), а не просто выражение для количества. Во-вторых, в случае множества документов в перечень «взаимоположений» документов и партий полноценно появляются и варианты, проиллюстрированные на рисунках 3 и 4 (необходимо учитывать, что одна партия может участвовать в списании для нескольких документов). Выражение для количества списания в случае множества документов приведено в следующем запросе:

CASE
 WHEN
  ( SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) + MAX( ISNULL( tab_part_1.КоличествоОстаток, 0 ) ) )
  > 
  ( SUM( tab_doc_2.Количество ) + MAX( tab_doc_1.Количество ) )
 THEN
  ( SUM( tab_doc_2.Количество ) + MAX( tab_doc_1.Количество ) )
ELSE
 ( SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) + MAX( ISNULL( tab_part_1.КоличествоОстаток, 0 ) ) )
END
-
CASE
 WHEN
  SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) )
  <
  SUM( tab_doc_2.Количество )
 THEN
  SUM( tab_doc_2.Количество )
ELSE
 SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) )
END

Приведем теперь весь запрос для получения реквизитов движений:

SELECT
 tab_doc_1.Документ,
 tab_doc_1.Номенклатура,
 tab_part_1.Партия,
 (
 CASE
  WHEN
   ( SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) + MAX( ISNULL( tab_part_1.КоличествоОстаток, 0 ) ) )
   >
   ( SUM( tab_doc_2.Количество ) + MAX( tab_doc_1.Количество ) )
  THEN
   ( SUM( tab_doc_2.Количество ) + MAX( tab_doc_1.Количество ) )
 ELSE
  ( SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) + MAX( ISNULL( tab_part_1.КоличествоОстаток, 0 ) ) )
 END<>  -
 CASE
  WHEN
   SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) )
   <
   SUM( tab_doc_2.Количество )
  THEN
   SUM( tab_doc_2.Количество )
 ELSE
  SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) )
 END
 ) AS КоличествоСписания
FROM ТаблицаДокументов AS tab_doc_1
LEFT JOIN ТаблицаДокументов AS tab_doc_2
ON tab_doc_1.Документ > tab_doc_2.Документ
LEFT JOIN ТаблицаПартий AS tab_part_1
ON tab_doc_1.Номенклатура = tab_part_1.Номенклатура
LEFT JOIN ТаблицаПартий AS tab_part_2
ON ( tab_part_1.Номенклатура = tab_part_2.Номенклатура ) AND ( tab_part_1.Партия > tab_part_2.Партия )
GROUP BY tab_doc_1.Документ, tab_doc_1.Номенклатура, tab_part_1.Партия
HAVING
( SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) < SUM( ISNULL( tab_doc_2.Количество, 0 ) ) + MAX( tab_doc_1.Количество ) )
AND
( SUM( ISNULL( tab_doc_2.Количество, 0 ) ) - MAX( tab_part_1.КоличествоОстаток) < SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) )
ORDER BY tab_doc_1.Документ, tab_doc_1.Номенклатура, tab_part_1.Партия

Добавляя деление на соответствующие корректирующие множители (см. «Вычисление нарастающего итога для N таблиц в рамках одного запроса») получаем следующий запрос:

SELECT
 tab_doc_1.Документ,
 tab_doc_1.Номенклатура,
 tab_part_1.Партия,
 CASE
  WHEN
  (
   SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) /
   CASE
    WHEN
     COUNT( DISTINCT tab_doc_2.Документ ) = 0
    THEN
     1
   ELSE
    COUNT( DISTINCT tab_doc_2.Документ )
   END + MAX( tab_part_1.КоличествоОстаток )
  )
  >
  (
   SUM( ISNULL( tab_doc_2.Количество, 0 ) ) /
   CASE
    WHEN
     COUNT( DISTINCT tab_part_2.Партия ) = 0
    THEN
     1
   ELSE
    COUNT( DISTINCT tab_part_2.Партия )
   END + MAX( tab_doc_1.Количество )
  )
  THEN
  (
   SUM( ISNULL( tab_doc_2.Количество, 0 ) ) /
   CASE
    WHEN
     COUNT( DISTINCT tab_part_2.Партия ) = 0
    THEN
     1
   ELSE
    COUNT( DISTINCT tab_part_2.Партия )
   END + MAX( tab_doc_1.Количество )
  )
 ELSE
  (
   SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) /
   CASE
    WHEN
     COUNT( DISTINCT tab_doc_2.Документ ) = 0
    THEN
     1
   ELSE
    COUNT( DISTINCT tab_doc_2.Документ )
   END + MAX( tab_part_1.КоличествоОстаток )
  )
 END
 -
 CASE
  WHEN
   (
    SUM( ISNULL( tab_doc_2.Количество, 0 ) ) /
    CASE
     WHEN
      COUNT( DISTINCT tab_part_2.Партия ) = 0
     THEN
      1
    ELSE
     COUNT( DISTINCT tab_part_2.Партия )
    END
   )
   >
   (
    SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) /
    CASE
     WHEN
      COUNT( DISTINCT tab_doc_2.Документ ) = 0
     THEN
      1
    ELSE
     COUNT( DISTINCT tab_doc_2.Документ )
    END
   )
  THEN
   (
    SUM( ISNULL( tab_doc_2.Количество, 0 ) ) /
    CASE
     WHEN
      COUNT( DISTINCT tab_part_2.Партия ) = 0
     THEN
      1
    ELSE
     COUNT( DISTINCT tab_part_2.Партия )
    END
   )
 ELSE
   (
    SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) /
    CASE
     WHEN
      COUNT( DISTINCT tab_doc_2.Документ ) = 0
     THEN
      1
    ELSE
     COUNT( DISTINCT tab_doc_2.Документ )
    END
   )
 END AS КоличествоСписания
FROM ТаблицаДокументов AS tab_doc_1
LEFT JOIN ТаблицаДокументов AS tab_doc_2
ON ( tab_doc_1.Номенклатура = tab_doc_2.Номенклатура ) AND ( tab_doc_1.Документ > tab_doc_2.Документ )
LEFT JOIN ТаблицаПартий AS tab_part_1
ON tab_doc_1.Номенклатура = tab_part_1.Номенклатура
LEFT JOIN ТаблицаПартий AS tab_part_2
ON ( tab_part_1.Номенклатура = tab_part_2.Номенклатура ) AND ( tab_part_1.Партия > tab_part_2.Партия )
GROUP BY tab_doc_1.Документ, tab_doc_1.Номенклатура, tab_part_1.Партия
HAVING
(
 SUM( ISNULL( tab_doc_2.Количество, 0 ) ) /
 CASE
  WHEN
   COUNT( DISTINCT tab_part_2.Партия ) = 0
  THEN
   1
 ELSE
  COUNT( DISTINCT tab_part_2.Партия )
 END + MAX( tab_doc_1.Количество ) 
 >
 SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) /
 CASE
  WHEN
   COUNT( DISTINCT tab_doc_2.Документ ) = 0
  THEN
   1
 ELSE
  COUNT( DISTINCT tab_doc_2.Документ )
 END
)
AND
(
 SUM( ISNULL( tab_doc_2.Количество, 0 ) ) /
 CASE
  WHEN
   COUNT( DISTINCT tab_part_2.Партия ) = 0
  THEN
   1
 ELSE
  COUNT( DISTINCT tab_part_2.Партия )
 END
 <
 SUM( ISNULL( tab_part_2.КоличествоОстаток, 0 ) ) /
 CASE
  WHEN
   COUNT( DISTINCT tab_doc_2.Документ ) = 0
  THEN
   1
 ELSE
  COUNT( DISTINCT tab_doc_2.Документ ) 
 END + MAX( tab_part_1.КоличествоОстаток )
)
ORDER BY tab_doc_1.Документ, tab_doc_1.Номенклатура, tab_part_1.Партия

Полученный запрос является корректным и работающим. Получена возможность вычисления нарастающего итога для двух таблиц одновременно, получены выражения как для ограничения списка партий, так и для числового значения количества списания.

Однако осталась еще одна очень важная деталь - соотношения моментов времени ввода документов и партий. В случае одного документа список партий получался просто - находились все партии, по которым есть остаток на дату документа. Но в случае множества документов необходимо находить подобный «остаток партий» для каждой строки из «таблицы документов», то есть накладывать еще одно ограничение для списка партий для каждого конкретного документа. Казалось бы, что может быть проще - напишем простое ограничение на соотношение моментов времени документа и партии:

SELECT
...
FROM ТаблицаДокументов AS tab_doc_1
LEFT JOIN ТаблицаДокументов AS tab_doc_2
ON ( tab_doc_1.Номенклатура = tab_doc_2.Номенклатура ) AND ( tab_doc_1.Документ > tab_doc_2.Документ )

Данное ограничение будет великолепно работать, но вот только в случае списания по методу FIFO. В случае списания по методу FIFO в «истории» для списка партий учитываются только партии, которые стоят по времени раньше данной текущей партии. Таким образом, накладывая ограничение из предыдущего запроса, мы получаем корректное значение «истории» для партии (если данная партия по времени ввода меньше данного документа, то и все партии из «истории» также заведомо меньше). Однако для случая списания по методу LIFO все несколько иначе: в «истории» партии учитываются значения для партий, которые идут позже. Таким образом, даже накладывая ограничение из предыдущего запроса, мы получим ошибочные значения в «истории» партий (в «истории» могут учитываться партии, которые были введены позднее данного документа). Следовательно, необходимо также накладывать ограничение на «историю» партий. Правильное выражение для учета соотношений моментов времени для документов и партий приведено в следующем запросе:

SELECT
...
FROM ТаблицаДокументов AS tab_doc_1
LEFT JOIN ТаблицаДокументов AS tab_doc_2
ON ( tab_doc_1.Номенклатура = tab_doc_2.Номенклатура ) AND ( tab_doc_1.Документ > tab_doc_2.Документ )
LEFT JOIN ТаблицаПартий AS tab_part_1
ON ( tab_doc_1.Номенклатура = tab_part_1.Номенклатура ) AND ( tab_doc_1.Документ > tab_part_1.Партия )
LEFT JOIN ТаблицаПартий AS tab_part_2
ON ( tab_part_1.Номенклатура = tab_part_2.Номенклатура ) AND ( tab_part_1.Партия > tab_part_2.Партия ) AND ( tab_doc_1.Документ > tab_part_2.Партия )
GROUP BY tab_doc_1.Документ, tab_doc_1.Номенклатура, tab_part_1.Партия

Но и этот вариант запроса имеет свои огрехи для случая списания по методу LIFO (а также в некоторых «спорных» случаях даже для списания по методу FIFO). Устранением этих «огрехов» я постараюсь заняться в следующих своих статьях. Пока же, в качестве разминки, предлагаю читателям самостоятельно найти проблемные места данного запроса.

Окончательный вариант запроса вместе с тестовым примером приведен в присоединенном к данной статье файле выгрузки. Конфигурация реализует управляемое приложение и разработана на релизе 8.2.14.528. 

Скачать файлы

Наименование Файл Версия Размер Кол. Скачив.
Тестовая конфигурация
.dt 194,63Kb
02.10.11
12
.dt 194,63Kb 12 Скачать

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Юрий Строжевский (y-str) 03.10.11 07:18
Частично обсуждение данной статьи велось также в комментариях к другой моей публикации: Использование запросов с нарастающим итогом
2. Алексей Константинов (alexk-is) 03.10.11 15:10
(0) С нетерпением жду результатов нагрузочных испытаний.

Вот параметры реальной базы небольшой торговой организации:
Документов поступления товаров 5000, среднее количество строк в документе 10
Документов реализации товаров 30000, среднее количество строк в документе 5
3. Юрий Строжевский (y-str) 03.10.11 15:30
(2) Пока могу сказать, что простой запрос получения нарастающего итога для таблицы в 1 миллион записей вычислил значения для 50 000 записей примерно за час на достаточно слабой системе. То есть порядка 1000 строк за минуту. То есть для всего миллиона потребуется порядка 1000 минут. Учитывая, что в запросе применяется вычисление сразу двух нарастающих итогов получаем, что вычисление подобного запроса будет занимать порядка двух дней (на миллионе записей как по таблице документов, так и по таблице партий + на той системе, что доступна мне). Учитывая то, что на тестовой системе уровень загрузки процессоров (двух) был постоянно около отметки в 100% (использование памяти для вычисления было практически нулевым) считаю, что результат может быть существенно улучшен на профессиональных системах.

P.S.: тест проходил при тестировании native SQL-запроса, прямо на SQL Server 2008R2.
4. Епрст (Ёпрст) 03.10.11 15:52
Да уж.. Вот что значит кастрированный запрос в снеговике.
Изврат да и только.
Сутки ждать выполнения для мульта записей - это п..ц.
Светлый ум; +1 Ответить 2
5. Юрий Строжевский (y-str) 03.10.11 17:13
(4) Данная статья представляет собой основу для произвольной оптимизации. Возможно позже я возьмусь и за эту тему. На сегодняшний момент я считаю, что для любой оптимизации необходимо иметь (и четко понимать) саму "базу", которую в дальнейшем предстоит оптимизировать.
6. Сергей (ildarovich) 03.10.11 18:47
(4) Пусть автор меня поправит, но 1С не причем
P.S.: тест проходил при тестировании native SQL-запроса, прямо на SQL Server 2008R2.

(3) Неправда то, что
То есть для всего миллиона потребуется порядка 1000 минут.
- так как рост квадратичный. Для последних 50000 записей потребуется в 20х20=400 раз больше времени, то есть 400 часов.
Сомневаюсь, что
Учитывая, что в запросе применяется вычисление сразу двух нарастающих итогов получаем, что вычисление подобного запроса будет занимать порядка двух дней
Вроде бы там используется декартово соединение таблиц? 1000000х1000000?
7. Юрий Строжевский (y-str) 03.10.11 19:04
(6) ildarovich, соединение "все со всеми", совершенно верно. Но учитывая, что в результат попадают только выборочные "партии" я уменьшил их количество до "линейного" значения.
Про реальное "полное" время исполнения запрос - тест был прерван, результаты которые я получил, я описал здесь.
8. Епрст (Ёпрст) 03.10.11 19:25
(6) еще как причем!
на обычном t-sql используя все методы скуля этот запрос пишется совсем по-другому без таких извратов. И работает, ну никак не 2-е суток.

А дело как раз в недоделанности 8-ого запроса.
9. Юрий Строжевский (y-str) 03.10.11 19:32
(8) Я же писал что запускал "простой запрос получения нарастающего итога". Написан он был на Transact SQL. Этот запрос используется в моем запросе просто как основа, ничего более.
10. Епрст (Ёпрст) 03.10.11 19:37
(9) ну и ? Есть же over и т.д
+хранимку для реализации фифо.
11. Юрий Строжевский (y-str) 03.10.11 19:40
(10) Целью моего тестирования на SQL Server 2008R2 было проверить принципиальное время выполнения простейшего запроса с нарастающими итогами. Я его проверил. Оптимизация данного запроса например с использованием CTE выходит за рамки моей статьи, и тем более выходит за рамки моего теста.
12. Епрст (Ёпрст) 03.10.11 19:51
ну ясно, практический смысл отсутствует.
13. Юрий Строжевский (y-str) 03.10.11 19:54
(12) Хорошо, пусть для Вас "практический смысл" моей статьи будет нулевым, я удовлетворюсь этим фактом.
14. Александр Иванов (dkprim) 11.10.11 18:35
публикация полезная. автору спасибо. побольше бы таких, осмысленных статей.
15. Юрий Строжевский (y-str) 11.10.11 18:40
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа