Оптимизация запросов: CustInvoiceTrans + markupTrans

[malex]

На одном из клиентов (версия системы 4.0, SQL 2005) стала жутко тормозить форма обработки фактуры по накладной клиента.

Причина - FactureEditLinesEngineCust_RU.dsExecuteMarkup

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

    select markupCursor
        where   markupCursor.CustVendPosted_RU                         &&
              ! markupCursor.ItemPosted_RU
    exists join tTrans
        where (
               markupCursor.TransTableId  == tTrans.TableId            &&
               markupCursor.TransRecId    == tTrans.RecId              &&
               tTrans.SalesId             == tJour.SalesId             &&
               tTrans.InvoiceId           == tJour.InvoiceId           &&
               tTrans.InvoiceDate         == tJour.InvoiceDate         &&
               tTrans.NumberSequenceGroup == tJour.NumberSequenceGroup
              )
              ||
              (
               markupCursor.TransTableId  == tJour.TableId             &&
               markupCursor.TransRecId    == tJour.RecId
              );

В SQL приходит следующее:
SELECT *
FROM MARKUPTRANS A
WHERE (A.DATAAREAID='exp' AND A.CUSTVENDPOSTED_RU<>0 AND A.ITEMPOSTED_RU=0) AND
EXISTS (SELECT 'x' FROM CUSTINVOICETRANS B
WHERE (B.DATAAREAID='exp' AND
((A.TRANSTABLEID=64 AND A.TRANSRECID=B.RECID AND B.SALESID='РКЗП-00032212' AND B.INVOICEID='14294-11' AND
B.INVOICEDATE='2012-03-13 00:00:00.000' AND B.NUMBERSEQUENCEGROUP='ТД') OR
(A.TRANSTABLEID=62 AND A.TRANSRECID=5637435947) )))

Если сделать по нормальному, то все работает быстро. Но как это можно написать в Аксапте одним запросом непонятно.

SELECT * FROM MARKUPTRANS A
WHERE (A.DATAAREAID='exp' AND A.CUSTVENDPOSTED_RU=0 AND A.ITEMPOSTED_RU=0) AND
((A.TRANSTABLEID=62 AND A.TRANSRECID=5637435947) OR
EXISTS (SELECT 'x' FROM CUSTINVOICETRANS B
WHERE (B.DATAAREAID='exp' AND
((A.TRANSTABLEID=64 AND A.TRANSRECID=B.RECID AND B.SALESID='РКЗП-00032212' AND B.INVOICEID='14294-11' AND
B.INVOICEDATE='2012-03-13 00:00:00.000' AND B.NUMBERSEQUENCEGROUP='ТД') ))))

PS: Индексы перестраивались, статистика обновлялась
PSS: План запроса во вложении (переименован в txt для загрузки)

[ivas]

может на 2 запроса разбить? 1ый по CustInvoiceTrans 2ой по CustInvoiceJour.

[malex]

Цитата:

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

может на 2 запроса разбить? 1ый по CustInvoiceTrans 2ой по CustInvoiceJour.

Разбить можно, но как потом склеить результаты чтобы передать в ds формы?

    MarkupTrans      markupCursor = markupDS.cursor();
/* запрос */
    markupDS.findRecord(markupCursor);

[S.Kuskov]

А если добавить директиву firstonly

select markupCursor
        where   markupCursor.CustVendPosted_RU                         &&
              ! markupCursor.ItemPosted_RU
    exists join firstonly tTrans
        where (...)

не поможет?

Цитата:

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

Разбить можно, но как потом склеить результаты чтобы передать в ds формы?

    MarkupTrans      markupCursor = markupDS.cursor();
/* запрос */
    markupDS.findRecord(markupCursor);

Интересно. А findRecord умеет работать с запросом который возвращает больше одной строки? Я как-то раньше не задумывался об этом.

[Alexanderis.ua]

Умеет.
Фактически в датасорс тут передается все, что получено запросом, с учетом команды next.

Я эту проблему в свое время победил. Топорно немного, но, насколько я знаю, до сих пор работает.

Если другого решения не будет, постараюсь сегодня свое описать

[fed]

Я бы еще попробовал на сервере выключить параллелизм. Там план запроса распараллелен, чего для таких простеньких запросов ни в коем случае нельзя делать. Welcome -- Ax Database Configuration Checklist part 1 Искать по ключевому слову MAXDOP.
IMHO, проблема не в запросе как таковом, а в том что SQL Server кривой план запроса генерит. Я бы начал лечение с MAXDOP==1, а потом попробовал накатить свежие Service Pack и CU на сиквел...

[Alexanderis.ua]

Ну раз все молчат, вверну свой быдлокод.
Собственно в решении ничего неожиданного нет.
Заменил использование markupDS.findRecord на банальное накладываение range по заранее собранным RecId (индекс в таблице такой есть, так что это не проблема). А сбор RecId уже спокойно можно разбить на 2 запроса.

Ниже уже конкретная реализация, если кому интересно.

Метод dsExecuteMarkup по всему семейству сделал типа boolean, чтобы в форме FactureEditLines_RU у датасорса метод executeQuery имел вид

public void executeQuery()
{
    // SOI -->
    /*
    factureEngine.dsExecuteMarkup();
//    super(); // PS 1791
    */
    if (factureEngine.dsExecuteMarkup())
    {
        super();
    }
    // SOI <--
}

Далее, в классе FactureEditLinesEngineCustVend_RU в метод dsExecuteMarkup добавляем

mrkDS.clearRanges(); // в потомках мы накладываем range, логично его сначала почистить
return false;

И, наконец, в наследниках (тут пример FactureEditLinesEngineCust_RU, в Vend - аналогично)

public boolean dsExecuteMarkup()
{
    MarkupTrans             markupCursor;
    CustInvoiceJour       tJour = custInvoiceJourDS.cursor();
    CustInvoiceTrans     tTrans;
    // SOI -->
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    Set                     set = new Set(Types::Int64);
    SetEnumerator           sE;
    counter                 counter;
    str                     range;
    // SOI <--
    ;

    super();

    // SOI -->
    qbds = markupDs.query().dataSourceTable(tablenum(MarkupTrans));

    // собственно собираем все, что связано и со строками и с заголовком
    while select recid
            from markupCursor
           where markupCursor.CustVendPosted_RU
              && !markupCursor.ItemPosted_RU
     exists join tTrans
           where tTrans.SalesId             == tJour.SalesId
              && tTrans.InvoiceId           == tJour.InvoiceId
              && tTrans.InvoiceDate         == tJour.InvoiceDate
              && tTrans.NumberSequenceGroup == tJour.NumberSequenceGroup
              && tTrans.TableId             == markupCursor.TransTableId
              && tTrans.RecId               == markupCursor.TransRecId
    {
        set.add(markupCursor.RecId);
    }

    while select recid
            from markupCursor
           where markupCursor.CustVendPosted_RU
              && !markupCursor.ItemPosted_RU
              && markupCursor.TransTableId  == tJour.TableId
              && markupCursor.TransRecId    == tJour.RecId
    {
        set.add(markupCursor.RecId);
    }

    // и лепим это все в range 
    sE = set.getEnumerator();
    while (sE.moveNext())
    {
        counter++;
        if (counter > 15) // не по-научному, знаю, но зато работает :)
        {
            qbr = qbds.addRange(fieldnum(MarkupTrans, RecId));
            qbr.value(range);
            range   = strmin();
            counter = 0;
        }
        range = queryRangeConcat(range, sE.current());
    }

    if (!set.elements())
    {
        range = sysQuery::value(0); // ничего не нашли - ничего и не покажем
    }
    if (range)
    {
        qbr = qbds.addRange(fieldnum(MarkupTrans, RecId));
        qbr.value(range);
    }

    return true;
    // SOI <--
}

Повторюсь - решение топорное и где-то даже некрасивое, но решать нужно было быстро. Да и со своей задачей оно справляется. Потому больше ничего и не делалось в этом направлении.
Если у кого есть какие другие варианты или критика/дополнения - буду рад увидеть.

[Владимир Максимов]

Вы сделали распространенную ошибку, решив что на SQL-сервере выполняется напрямую SQL-запрос. На самом деле это не так. Там создается курсор, в который и "оборачивает" SQL-запрос. А вот у курсора и "чистого" запроса планы исполнения могут отличаться. Причем, как правило, отличия возникают именно при объединении по Exists Join.

Т.е. у Вас два варианта решения проблемы

1. Отказаться от Exists Join в пользу Inner Join. Поскольку у Вас ведущей является таблица строк накладных расходов, то связь получится по типу много-к-одному. Т.е. здесь даже не надо проверять на возможные дубли. Их и так не будет

2. Можно заняться оптимизацией. НО! Оптимизацией не того запроса, что Вы выцепили из профайлера, а обернутого в курсор. Т.е. Вам надо оптимизировать вот это

PHP код:

DECLARE cursor1 CURSOR FOR

SELECT *
FROM MARKUPTRANS A 
WHERE (A.DATAAREAID='exp' AND A.CUSTVENDPOSTED_RU<>0 AND A.ITEMPOSTED_RU=0) AND 
EXISTS (SELECT 'x' FROM CUSTINVOICETRANS B 
WHERE (B.DATAAREAID='exp' AND 
((A.TRANSTABLEID=64 AND A.TRANSRECID=B.RECID AND B.SALESID='РКЗП-00032212' AND B.INVOICEID='14294-11' AND 
B.INVOICEDATE='2012-03-13 00:00:00.000' AND B.NUMBERSEQUENCEGROUP='ТД') OR 
(A.TRANSTABLEID=62 AND A.TRANSRECID=5637435947) )))

OPEN cursor1
FETCH NEXT FROM cursor1
CLOSE cursor1
DEALLOCATE cursor1 


Если посмотреть его план выполнения, то Вы увидите, что он принципиально отличается от первоначального варианта

[Alexanderis.ua]

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