Как оптимально написать T-SQL запрос для выборки настройки Table/Group/All (например, счет ГК из профиля разноски)

[Vadik]

Не надо лохматить профили разноски. Материализуй связь между проводкой по клиенту и клиентскому счету в ГК через поле в CustTrans или таблицу сбоку и заполняй при разноске. И работать будет "удобно", и проблем с выверкой не будет

[Ярослав Щекин]

Цитата:

Сообщение от mazzy

и все таки... может у кого есть что сказать?

Может быть, стоит пойти с этим вопросом на профильный форум на SQL.RU (хотя там задать его будет посложнее, скорее всего, будут требовать привести скрипты таблиц, статистику, а то и repro, конкретный запрос, и т.д. и т.п.), зато там уж скорее подскажут.

Кроме того, какие-то немного шаманские сравнения получаются.

1. IMHO, стоит включить SET STATISTICS TIME / SET STATISTICS IO (можно в опциях в Management Studio) и хотя бы смотреть, чтобы примерно то же количество чтений были логическими/физическими, и не ждал ли MS SQL во время выполнения кого-то ещё).

2. "То, о чем предупреждал Владимир Максимов" --- посмотреть Estimated number of rows в каждом плане, т.к. если оценки сильно отличаются от реальных, и Вы выберете этот план, то потом можно очень сильно пожалеть... Дело в том, что, выбирая в таких случаях по реальному времени выполнения, Вы просто надеетесь на удачу.

3. Да, а (если не накладывать условий на pSumAccount) сколько занимает такая же выборка из CustTrans? У Вас, похоже, большой её кусок считывается и передаётся на клиента, может всё время вообще на это уходит, откуда нам знать?

4. А какую версию MS SQL Server Вы используете (в разных версиях поведение может кардинально отличаться)?

[mazzy]

Цитата:

Сообщение от Vadik

Материализуй связь между проводкой по клиенту и клиентскому счету в ГК через поле в CustTrans или таблицу сбоку и заполняй при разноске. И работать будет "удобно", и проблем с выверкой не будет

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

во-вторых, сделал список объектов в Аксапте, который использует перечисление TableGroupAll

там 143 объекта (и это я еще кастомно-проектные убрал).
среди них:

• Asset и ОС
• comission - расчет комиссионных и агентских
• CustPaym, VendPaym - управление платежами
• разноски по клиентам, поставщикам, подотченикам, номенклатуре
• прайс-лист и цены !!!!
• управление качеством
• упаковка
• накладные расходы
• PBA - бывший конфигуратор продукции
• Proj - проекты (дофига!)
• Purch, Sales - закупки и заказы на продажу
• Route - маршруты
• RPay - расчет зарплаты (дофига!)
• RTax - налоговый учет (дофига!)
• smm
• supp

я подумал, что этого достаточно, чтобы поговорить о принципе в целом
тема ветки: Как оптимально написать T-SQL запрос для выборки настройки Table/Group/All (например, счет ГК из профиля разноски)

свою то проблему я давно решил. причем как это ни странно, наивным способом. А он оказался еще и самым быстрым.

Цитата:

Сообщение от Ярослав Щекин

Может быть, стоит пойти с этим вопросом на профильный форум на SQL.RU

ну, можно и так....

[KiselevSA]

select ct.ACCOUNTNUM, cla.ACCOUNTCODE, cla.POSTINGPROFILE, dim.DISPLAYVALUE, cla.DATAAREAID from CUSTTABLE as ct
join CUSTLEDGERACCOUNTS as cla on
	ct.ACCOUNTNUM = cla.NUM and
	ct.DATAAREAID = cla.DATAAREAID and
	cla.ACCOUNTCODE = 0
join DIMENSIONATTRIBUTEVALUECOMBINATION as dim on
	cla.SUMMARYLEDGERDIMENSION = dim.RECID
	where 
		ct.DATAAREAID = 'usmf'
union
select ct1.ACCOUNTNUM, cla1.ACCOUNTCODE, cla1.POSTINGPROFILE, dim1.DISPLAYVALUE, cla1.DATAAREAID from CUSTTABLE as ct1
join CUSTLEDGERACCOUNTS as cla1 on
	ct1.CUSTGROUP = cla1.NUM and
	ct1.DATAAREAID = cla1.DATAAREAID and
	cla1.ACCOUNTCODE = 1
join DIMENSIONATTRIBUTEVALUECOMBINATION as dim1 on
	cla1.SUMMARYLEDGERDIMENSION = dim1.RECID
		where 
		ct1.DATAAREAID = 'usmf'
union
select ct2.ACCOUNTNUM, cla2.ACCOUNTCODE, cla2.POSTINGPROFILE, dim2.DISPLAYVALUE, cla2.DATAAREAID from CUSTTABLE as ct2
join CUSTLEDGERACCOUNTS as cla2 on
	ct2.DATAAREAID = cla2.DATAAREAID and
	cla2.ACCOUNTCODE = 2
join DIMENSIONATTRIBUTEVALUECOMBINATION as dim2 on
	cla2.SUMMARYLEDGERDIMENSION = dim2.RECID
	where 
		ct2.DATAAREAID = 'usmf'

у меня такой запрос на demo выполняется мгновенно (план посмотреть не могу, прав не хватает). Думаю, что и select into будет работать так же быстро.

[mazzy]

Цитата:

Сообщение от KiselevSA

у меня такой запрос на demo выполняется мгновенно

))))))

Анекдот:
принимают секретаршу на работу. Спрашивают:
- с какой скоростью печатаете?
- 1000 знаков в минуту
- вы приняты!!!!!
- (думает про себя) такая фигня получается.

и сам в эту сторону думал, и предполагал что и другие в эту сторону пойдут...

Цитата:

Сообщение от mazzy

Аксапта всегда в первую очередь использует настройку для Table, если таковая есть. Если настройки для Table нет, то Аксапта ищет настройку для Group. Если и таковой нет, то Аксапта ищет настройку для All. Вполне возможно, что настройка может отсутствовать.
....
Основная проблема - в настроечной таблице для одной проводки может быть несколько разных подходящих настроек для одной исходной мастер-записи.

нет, нельзя делать просто union.
для одного профиля может быть несколько подходящих настроек - и для конкретного значения Table, и для группы и для всех. Аксапта в этом случае выбирает ОДНУ настройку. union возвратит все подходящие. А настройка Все используется очень часто.

))))))

[Alexius]

Цитата:

Сообщение от mazzy

нет, нельзя делать просто union.
для одного профиля может быть несколько подходящих настроек - и для конкретного значения Table, и для группы и для всех. Аксапта в этом случае выбирает ОДНУ настройку. union возвратит все подходящие. А настройка Все используется очень часто.

Решается с помощью обертки, но какая получится скорость - вопрос.

SELECT ACCOUNTNUM, ACCOUNTCODE, POSTINGPROFILE, DATAAREAID, RECID
  , CASE WHEN MAX(Value0) <> '' THEN MAX(Value0) ELSE CASE WHEN MAX(Value1) <> '' THEN MAX(Value1) ELSE MAX(Value2) END END Value
FROM 
(
  select ct.ACCOUNTNUM, cla.ACCOUNTCODE, cla.POSTINGPROFILE, dim.DISPLAYVALUE Value0, '' Value1, '' Value2, cla.DATAAREAID, ct.RECID from CUSTTABLE as ct
  join CUSTLEDGERACCOUNTS as cla on
	  ct.ACCOUNTNUM = cla.NUM and
	  ct.DATAAREAID = cla.DATAAREAID and
	  cla.ACCOUNTCODE = 0
  join DIMENSIONATTRIBUTEVALUECOMBINATION as dim on
	  cla.SUMMARYLEDGERDIMENSION = dim.RECID
	  where 
		  ct.DATAAREAID = 'usmf'
  union
  select ct1.ACCOUNTNUM, cla1.ACCOUNTCODE, cla1.POSTINGPROFILE, '', dim1.DISPLAYVALUE, '', cla1.DATAAREAID, ct1.RECID from CUSTTABLE as ct1
  join CUSTLEDGERACCOUNTS as cla1 on
	  ct1.CUSTGROUP = cla1.NUM and
	  ct1.DATAAREAID = cla1.DATAAREAID and
	  cla1.ACCOUNTCODE = 1
  join DIMENSIONATTRIBUTEVALUECOMBINATION as dim1 on
	  cla1.SUMMARYLEDGERDIMENSION = dim1.RECID
		  where 
		  ct1.DATAAREAID = 'usmf'
  union
  select ct2.ACCOUNTNUM, cla2.ACCOUNTCODE, cla2.POSTINGPROFILE, '', '', dim2.DISPLAYVALUE, cla2.DATAAREAID, ct2.RECID from CUSTTABLE as ct2
  join CUSTLEDGERACCOUNTS as cla2 on
	  ct2.DATAAREAID = cla2.DATAAREAID and
	  cla2.ACCOUNTCODE = 2
  join DIMENSIONATTRIBUTEVALUECOMBINATION as dim2 on
	  cla2.SUMMARYLEDGERDIMENSION = dim2.RECID
	  where 
		  ct2.DATAAREAID = 'usmf'
) T
GROUP BY ACCOUNTNUM, ACCOUNTCODE, POSTINGPROFILE, DATAAREAID, RECID

[Ярослав Щекин]

Цитата:

Сообщение от mazzy

я подумал, что этого достаточно, чтобы поговорить о принципе в целом
тема ветки: Как оптимально написать T-SQL запрос для выборки настройки Table/Group/All (например, счет ГК из профиля разноски)

И всё же, какая производительность у "простой" выборки (без PSumAccount) с теми же условиями?

Цитата:

Сообщение от mazzy

свою то проблему я давно решил. причем как это ни странно, наивным способом. А он оказался еще и самым быстрым.

А вот у меня способ с подзапросами (только чуть подправленный) работает не хуже на MS SQL 2014, и что (оттого что план у него похож на тот, что Вы приводили для "наивного") ?

[KiselevSA]

Цитата:

Сообщение от Alexius

Решается с помощью обертки, но какая получится скорость - вопрос.

Обертку можно не делать, достаточно отобрать запись с минимальным значением AccountCode: именно эта запись работает при разноске.
Но есть одна засада, о которой ТС не задумался: а что будет, если настройки профиля меняли после того, как пошли проводки. Насколько я помню, полей Valid... в настроечных таблицах нет.

[Raven Melancholic]

У нас уже несколько лет из портала получают такие настройки способом, который в теме назван "наивным". То есть, получение всех вариантов с left outer join.
Не могу подвести какую-то теоретическую базу под такой выбор - просто когда помогали WEB программистам с построением запроса, получилось, что в экспериментах это был наиболее быстрый вариант.
Только у нас не отбирается потом одно единственное значение, а выводятся все три в разных полях. Возможно, что именно для счета ГК или каких-то других настроек, у которых результат может быть только один, так и нужно отбирать. Но у нас такие запросы, в основном, используются для получения цен-скидок. А там, как всем известно, благодаря флагу "Искать далее" для цен и скидок можно выбирать не просто строку, наиболее точно подходящую под составляющие части настройки (номенклатура более важна, чем группа номенклатур, группа клиентов более важна, чем все клиенты и т.п.), а суммировать скидки и выбирать наиболее низкие цены. Поэтому запрос возвращает все три варианта в разных колонках, а уже бизнес-логика на PHP определяет нужный.