Нужна помощь в оптимизации сложного запроса

[Kashesh]

Всем добрый день!
Столкнулся с проблемой оч медленного раскрытия лукапа. Да, запрос там сложный, но как ни старался, все равно медленно открывается, может кто подскажет как еще можно упростить..
Задача на всякий случай состояла отобрать счета-фактуры, имеющие открытые проводки.
Запрос в мониторинге получился следующий:

SELECT A.FACTUREID,A.FACTUREDATE,A.FACTUREAMOUNT,A.FACTURETAX,A.CURRENCYCODE,A.CUSTVENDINVOICEACCOUNT,A.FACTUREEXTERNALID,A.NUMBERSEQUENCEGROUPID,A.NONREALREVENUE,A.OFFSESSIONID,A.CONSIGNEE,A.FACTUREROUNDOFF,A.CONSIGNOR,A.PRINTINMST,A.MODULE,A.FACTURETYPE,A.DOCUMENTNUM,A.DOCUMENTDATE,A.REVERSED,A.VOUCHER,A.CUSTVENDTRANSRECID,A.POSTINGLOGRECID,A.LANGUAGEID,A.DELIVERYADDRESS,A.FACTUREDATE_EXTERNAL,A.REVERSEDATE,A.KDV_STORNOFACTUREEID,A.KDV_STORNO,A.KDV_PRINTFACTUREEID,A.KDV_PRINTFACTUREEIDEDIT,A.KDV_PRINTFACTUREID,A.KDV_STORNOINVOICEID,A.KDV_STORNORECID,A.KDV_OLDPRINTFORM,A.MODIFIEDDATE,A.MODIFIEDTIME,A.RECVERSION,A.RECID
FROM FACTUREJOUR_RU A WITH( NOLOCK)
WHERE ((A.DATAAREAID='SLK') AND (((A.MODULE=0) AND (A.CUSTVENDINVOICEACCOUNT='Ю-РЕЧТРАНС')) AND (A.FACTUREID>='СФВК-051145'))) 

AND EXISTS (SELECT 'X'
FROM FACTURETRANS_RU B WITH( NOLOCK)
WHERE ((B.DATAAREAID='SLK') AND ((A.FACTUREID=B.FACTUREID) AND (A.MODULE=B.MODULE)))

AND EXISTS (SELECT 'X'
FROM CUSTINVOICETRANS C WITH( NOLOCK)
WHERE ((C.DATAAREAID='SLK') AND (((((((B.MODULE=0) AND (B.FACTURELINETYPE=0)) AND (B.INVOICELINENUM=C.LINENUM)) AND (B.INVOICEDATE=C.INVOICEDATE)) AND (B.INVOICEID=C.INVOICEID)) AND (B.SALESPURCHID=C.SALESID)) AND (B.NUMBERSEQUENCEGROUP=C.NUMBERSEQUENCEGROUP)))

AND EXISTS (SELECT 'X'
FROM CUSTINVOICEJOUR D WITH( NOLOCK)
WHERE ((D.DATAAREAID='SLK') AND ((((C.SALESID=D.SALESID) AND (C.INVOICEID=D.INVOICEID)) AND (C.INVOICEDATE=D.INVOICEDATE)) AND (C.NUMBERSEQUENCEGROUP=D.NUMBERSEQUENCEGROUP))) 

AND EXISTS (SELECT 'X'
FROM CUSTTRANS E WITH( NOLOCK)
WHERE ((E.DATAAREAID='SLK') AND (((D.INVOICEID=E.INVOICE) AND (D.INVOICEACCOUNT=E.ACCOUNTNUM)) AND (D.INVOICEDATE=E.TRANSDATE)))

AND EXISTS (SELECT 'X'
FROM CUSTTRANSOPEN F WITH( NOLOCK)
WHERE ((F.DATAAREAID='SLK') AND ((E.ACCOUNTNUM=F.ACCOUNTNUM) AND (E.RECID=F.REFRECID))))))))
ORDER BY A.FACTUREID,A.MODULE OPTION(FAST 1)

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

[ivas]

Добавьте в последний запрос фильтр по клиенту

[Zabr]

Простая проверка на пустое поле FactureJour_RU.voucher Вам не подойдёт ?

[SRF]

Цитата:

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

Простая проверка на пустое поле FactureJour_RU.voucher Вам не подойдёт ?

По-моему это немного не то, поскольку поле voucher заполняется только для фактур по предоплатам, в остальных случаях оно пустое

Цитата:

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

SELECT A.FACTUREID,A.FACTUREDATE,A.FACTUREAMOUNT,A.FACTURETAX,A.CURRENCYCODE,A.CUSTVENDINVOICEACCOUNT,A.FACTUREEXTERNALID,A.NUMBERSEQUENCEGROUPID,A.NONREALREVENUE,A.OFFSESSIONID,A.CONSIGNEE,A.FACTUREROUNDOFF,A.CONSIGNOR,A.PRINTINMST,A.MODULE,A.FACTURETYPE,A.DOCUMENTNUM,A.DOCUMENTDATE,A.REVERSED,A.VOUCHER,A.CUSTVENDTRANSRECID,A.POSTINGLOGRECID,A.LANGUAGEID,A.DELIVERYADDRESS,A.FACTUREDATE_EXTERNAL,A.REVERSEDATE,A.KDV_STORNOFACTUREEID,A.KDV_STORNO,A.KDV_PRINTFACTUREEID,A.KDV_PRINTFACTUREEIDEDIT,A.KDV_PRINTFACTUREID,A.KDV_STORNOINVOICEID,A.KDV_STORNORECID,A.KDV_OLDPRINTFORM,A.MODIFIEDDATE,A.MODIFIEDTIME,A.RECVERSION,A.RECID
FROM FACTUREJOUR_RU A WITH( NOLOCK)
WHERE ((A.DATAAREAID='SLK') AND (((A.MODULE=0) AND (A.CUSTVENDINVOICEACCOUNT='Ю-РЕЧТРАНС')) AND (A.FACTUREID>='СФВК-051145'))) 
 
AND EXISTS (SELECT 'X'
FROM FACTURETRANS_RU B WITH( NOLOCK)
WHERE ((B.DATAAREAID='SLK') AND ((A.FACTUREID=B.FACTUREID) AND (A.MODULE=B.MODULE)))
 
AND EXISTS (SELECT 'X'
FROM CUSTINVOICETRANS C WITH( NOLOCK)
WHERE ((C.DATAAREAID='SLK') AND (((((((B.MODULE=0) AND (B.FACTURELINETYPE=0)) AND (B.INVOICELINENUM=C.LINENUM)) AND (B.INVOICEDATE=C.INVOICEDATE)) AND (B.INVOICEID=C.INVOICEID)) AND (B.SALESPURCHID=C.SALESID)) AND (B.NUMBERSEQUENCEGROUP=C.NUMBERSEQUENCEGROUP)))
 
AND EXISTS (SELECT 'X'
FROM CUSTINVOICEJOUR D WITH( NOLOCK)
WHERE ((D.DATAAREAID='SLK') AND ((((C.SALESID=D.SALESID) AND (C.INVOICEID=D.INVOICEID)) AND (C.INVOICEDATE=D.INVOICEDATE)) AND (C.NUMBERSEQUENCEGROUP=D.NUMBERSEQUENCEGROUP))) 
 
AND EXISTS (SELECT 'X'
FROM CUSTTRANS E WITH( NOLOCK)
WHERE ((E.DATAAREAID='SLK') AND (((D.INVOICEID=E.INVOICE) AND (D.INVOICEACCOUNT=E.ACCOUNTNUM)) AND (D.INVOICEDATE=E.TRANSDATE)))
 
AND EXISTS (SELECT 'X'
FROM CUSTTRANSOPEN F WITH( NOLOCK)
WHERE ((F.DATAAREAID='SLK') AND ((E.ACCOUNTNUM=F.ACCOUNTNUM) AND (E.RECID=F.REFRECID))))))))
ORDER BY A.FACTUREID,A.MODULE OPTION(FAST 1)

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

Что бросается в глаза сразу :

1. CustInvoiceTrans здесь лишний, можно без него (посмотрите relation на FactureTrans_RU к CustInvoiceJour)

2. Связь с CustTrans было бы нелохо строить по полям (e.voucher = d.ledgerVoucher, e.accountNum = d.invoiceAccount, e.transdate = d.invoicedate), но для этого нужно проверить, чтобы у вас все проводки по клиентам, имели тот же voucher, что и накладные создавшие данные проводки
Также можно добавить индекс на custTrans по полям запроса, или включить поле Invoice в уже сущетсвующие индексы

3. Также может быть Вам поможет добавление индекса на таблице FactureJour_RU - (factureId, custVendInvoiceAccount, module)

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

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

Если можно :

1. Выложите план запроса из AX и его cost в AX(SQL трассировка запросов)
2. И если есть доступ на СУБД, выполните его там и выложите сюда cost из СУБД.

Поверьте, чем больше информации будет предоставлено, тем легче будет Вам помочь

[Kashesh]

Цитата:

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

1. Выложите план запроса из AX и его cost в AX(SQL трассировка запросов)
2. И если есть доступ на СУБД, выполните его там и выложите сюда cost из СУБД.

1. А что именно имеется ввиду? Дерево в плане исполнения? или что, не понимаю.
2. Я не знаю как это сделать в SQL, если подскажите, по попробую сделать и там

[SRF]

Цитата:

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

1. А что именно имеется ввиду? Дерево в плане исполнения? или что, не понимаю.
2. Я не знаю как это сделать в SQL, если подскажите, по попробую сделать и там

1. Да, имелось ввиду дерево в плане исполнения, извините, если не правильно высказался

2. Ну тут нужно смотреть какие средства есть у СУБД, к примеру, если у вас
MS SQL 2005 то можно использовать вот этот инструмент Database Engine Tuning Advisor ( http://msdn.microsoft.com/en-us/library/ms166575.aspx )

[ivas]

всетаки добавьте условие по
CustTrans.AccountNum = 'Ю-РЕЧТРАНС'
CustTransOpen.AccountNum = 'Ю-РЕЧТРАНС'