Фактуры. Помогите с запросом.

[Михаил Петрович]

Здравия всем...

Вот такой вот запросик при обработке фактур занимает сейчас секунд 30 минимум при выполнении...
Местонахождение - classes\FactureCalcSettlement_RU\calcFactureAmounts line 50

// Process line markups
    while select sum(LineAmount), sum(VAT), sum(TaxAmount),sum(Qty), maxOf(ItemName) from factureTrans
        group by TaxCode, Dimension, ExchRate,InventTransId, InternalInvoiceId,InvoiceId,NumberSequenceGroup,markupRefRecId
        exists join custVendInvoiceTrans
        exists join markupTrans
        where factureTrans.FactureId            == _factureJour.FactureId           &&
              factureTrans.Module               == module                           &&
              factureTrans.FactureLineType      == FactureLineType_RU::Markup       &&
              markupTrans.RecId                 == factureTrans.MarkupRefRecId      &&
              markupTrans.TransTableId          == custVendInvoiceTrans.TableId     &&
              custVendInvoiceTrans.RecId        == markupTrans.TransRecId           &&
              custVendInvoiceTrans.SalesPurchId == _custVendInvoiceJour.Num         &&
              custVendInvoiceTrans.InvoiceNum   == _custVendInvoiceJour.InvoiceId   &&
              custVendInvoiceTrans.InvoiceDate  == _custVendInvoiceJour.InvoiceDate &&
              (module == FactureModule_RU::Cust ||
               (module == FactureModule_RU::Vend &&
                custVendInvoiceTrans.PurchInternalInvoiceId == _custVendInvoiceJour.PurchInternalInvoiceId)) &&
              custVendInvoiceTrans.NumberSequenceGroupId
                                                == _custVendInvoiceJour.NumberSequenceGroupId
    {
        this.addFactureTransAmounts(factureAmounts, factureTrans, _factureJour);
    }

При преобразовании его в нечто вот такое, выполнение запроса сводится к сотням миллисекунд, но есть некоторая неуверенность однозначности запросов

// Process line markups
    while select sum(LineAmount), sum(VAT), sum(TaxAmount),sum(Qty), maxOf(ItemName) from factureTrans

        group by TaxCode, Dimension, ExchRate,InventTransId, InternalInvoiceId,InvoiceId,NumberSequenceGroup,markupRefRecId

        where factureTrans.FactureId            == _factureJour.FactureId           &&
              factureTrans.Module               == module                           &&
              factureTrans.FactureLineType      == FactureLineType_RU::Markup      
        
        exists join markupTrans
        where markupTrans.RecId                 == factureTrans.MarkupRefRecId      &&
              markupTrans.TransTableId          == custVendInvoiceTrans.TableId   

        exists join custVendInvoiceTrans
        where custVendInvoiceTrans.RecId        == markupTrans.TransRecId           &&
              custVendInvoiceTrans.SalesPurchId == _custVendInvoiceJour.Num         &&
              custVendInvoiceTrans.InvoiceNum   == _custVendInvoiceJour.InvoiceId   &&
              custVendInvoiceTrans.InvoiceDate  == _custVendInvoiceJour.InvoiceDate &&
              (module == FactureModule_RU::Cust ||
               (module == FactureModule_RU::Vend &&
                custVendInvoiceTrans.PurchInternalInvoiceId == _custVendInvoiceJour.PurchInternalInvoiceId)) &&
              custVendInvoiceTrans.NumberSequenceGroupId
                                                == _custVendInvoiceJour.NumberSequenceGroupId
    {
        this.addFactureTransAmounts(factureAmounts, factureTrans, _factureJour);
    }

С нетерпением жду совета :-)

[miklenew]

Где то видел на форуме, что второй вариант правильнее. Да вы выгрузите куда нибудь результат по какому нибудь столбцу и сверьте. Будете точно уверены, что нигде не ошиблись.

[Raven Melancholic]

Все правильно. Это место практически все переделывают, так как при exists join очень важно, чтобы в условии подзапроса не было фильтрации основного запроса (если посмотреть запрос, который отправляется на сервер в первом случае, станет понятно почему).
Кстати ,еще одним вариантам переделки является изменение exist join на простой join RecId - в этом случае сервер сам справится с оптимизацией запроса.

[Михаил Петрович]

Большое спасибо за ответы :-)

[kashperuk]

а разве это в последних сервис паках не было исправлено? У вас какой, кстати?

[Михаил Петрович]

У нас SP3 стоит...

[konopello]

Цитата:

так как при exists join очень важно, чтобы в условии подзапроса не было фильтрации основного запроса

а можете пояснить что вы этим хотели сказать, просто много раз уже сталуивался что exists глючит но так и не понел чего.

[kashperuk]

Цитата:

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

а можете пояснить что вы этим хотели сказать, просто много раз уже сталуивался что exists глючит но так и не понел чего.

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

select .... from table where (select * from table2...)

вместо

select ... from table exists join table2

[konopello]

Цитата:

чтобы в условии подзапроса не было фильтрации основного запроса

Т.е. если запрос имеет вид

select table1 
        where table1.field1 == "11" 
exists join table2 
        where table2.field1 == table1.field1

то на сервер поедет запрос

select .... from table where (select * from table2...)

вместо

select ... from table exists join table2

Это так?

[kashperuk]

Цитата:

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

Это так?

1. Это уже исправлено в последних сервис паках.
2. Нет, по-моему в приведенном вами примере все уйдек как надо.

Глюки будут, если в подзапросе есть фильтрация полей основного запроса, к примеру

select table1 
        where table1.field1 == "11" 
exists join table2 
        where table1.field3 == "321" &&
                  table2.field1 == table1.field1;

[konopello]

Благодарствю за ответ, хорошо что у меня нет привычки писать такие запросы.

[Михаил Петрович]

Продолжаем эпопею с фактурами dsExecuteMarkup() класса FactureEditLinesEngineVend_RU

В методе

public void dsExecuteMarkup()
{
    MarkupTrans      markupCursor = markupDS.cursor();
    VendInvoiceJour  tJour        = vendInvoiceJourDS.cursor();
    VendInvoiceTrans tTrans;

    super();

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

Происходит "прогулка" по VendInvoiceTrans и estimated cost запроса становится буквально от 5000 до 58200 - пока большей "стоимости" sql мониторинг не выдавал.

Так вот теоретический вопрос:

Почему в подзапросе начинается проверка вида

       markupCursor.TransTableId  == tTrans.TableId            &&
               markupCursor.TransRecId    == tTrans.RecId   
           

               markupCursor.TransTableId  == tJour.TableId             &&
               markupCursor.TransRecId    == tJour.RecId

Особенно первая часть до "или"... Мы ж вроде как ищем данные в tTrans, а не наоборот...

То есть

        tTrans.TableId  == markupCursor.TransTableId            &&
                tTrans.RecId  == markupCursor.TransRecId

Или нет?..

А вот сам sql запрос

SELECT A.TRANSTABLEID,A.TRANSRECID,A.LINENUM,A.MARKUPCODE,
A.CURRENCYCODE,A.VALUE,A.TAXAMOUNT,A.POSTED,A.VOUCHER,
A.TRANSDATE,A.TXT,A.TAXGROUP,A.TAXITEMGROUP,A.MARKUPCATEGORY,
A.TAXAUTOGENERATED,A.TAXWRITECODE,A.MODULECATEGORY,A.ORIGRECID,
A.ORIGTABLEID,A.MODULETYPE,A.DEL_FULLYINVOICED,A.KEEP,
A.CALCULATEDAMOUNT,A.FACTUREDAMOUNT_RU,A.CUSTVENDPOSTED_RU,
A.ITEMPOSTED_RU,A.TAXVALUEVAT_RU,A.TAXAMOUNTVAT_RU,
A.TAXAMOUNTEXCISE_RU,A.TAXVATTYPE_RU,A.SOURCETABLEID,
A.SOURCERECID,A.MARKUPALLOCATEAFTER,A.INVENTTRANSID,A.RECID FROM MARKUPTRANS A WHERE ((A.DATAAREAID='ret') AND ((A.CUSTVENDPOSTED_RU<>0) AND ((A.ITEMPOSTED_RU=0)))) 

AND EXISTS (SELECT 'x' FROM VENDINVOICETRANS B WHERE 
((B.DATAAREAID='ret') AND (((((([COLOR="Red"]
((A.TRANSTABLEID=492) [/COLOR]A[COLOR="#ff0000"]ND
 (A.TRANSRECID=B.RECID[/COLOR])) AND (B.PURCHID='         ???00019730'))
 AND (B.INVOICEID='               15548')) AND 
(B.INVOICEDATE={ts '2005-10-02 00:00:00.000'})) AND 
(B.NUMBERSEQUENCEGROUP=' ')) AND
 (B.INTERNALINVOICEID='         ???00013750')) [COLOR="#ff0000"]OR ((A.TRANSTABLEID=491) AND (A.TRANSRECID=277411563))[/COLOR]))) 
ORDER BY A.DATAAREAID,A.TRANSRECID,A.TRANSTABLEID,A.LINENUM OPTION(FAST 1)

Если вынести эту проверку до exist join суть запроса ведь не поменяется?..

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

[gl00mie]

Цитата:

Сообщение от Михаил Петрович

Почему в подзапросе начинается проверка вида

               markupCursor.TransTableId  == tTrans.TableId            &&
               markupCursor.TransRecId    == tTrans.RecId   
           

               markupCursor.TransTableId  == tJour.TableId             &&
               markupCursor.TransRecId    == tJour.RecId

Особенно первая часть до "или"... Мы ж вроде как ищем данные в tTrans, а не наоборот...

Потому что в TSQL пишется

select  *
from    markupTrans      m
join    vendInvoiceTrans t
on      m.transTableId = t.tableId  and
        m.transRecId   = t.recId

т.е. в JoinCondition первым идет поле из "левой" таблицы.

[Михаил Петрович]

Спасибо за разъяснение!

А идей по ускорению сей процедуры случайно нет?..