在Axapta数据库上查询的性能问题-PerformanceissuewithqueryonAxaptadatabase

作者：lanterwhile_557 | 来源：互联网 | 2023-09-10 13:32

IwroteaqueryonourAxaptadatabaseandrewroteitahundredtimes,buttheissueremains.Itper

I wrote a query on our Axapta database and rewrote it a hundred times, but the issue remains. It performs very bad. After 15 minutes there is still no results while almost bringing the server down.

我在Axapta数据库上写了一个查询并重写了一百次，但问题仍然存在。它表现非常糟糕。 15分钟后，几乎没有结果，几乎使服务器关闭。

Any idea on how to improve this statement is most welcome.

关于如何改进这种说法的任何想法都是最受欢迎的。

SELECT
  J.INVOICEID,
  T.AMOUNTCUR,
  T.RECID,
  T.ACCOUNTNUM,
  CASE WHEN O.DATAAREAID IS NULL THEN 0 ELSE 1 END hasOpenTrans,
  CASE WHEN P.DATAAREAID IS NULL THEN 0 ELSE 1 END hasPayment
FROM
  CUSTINVOICEJOUR J
  LEFT JOIN CUSTTRANS T ON
    T.DATAAREAID = J.DATAAREAID AND
    T.INVOICE = J.INVOICEID AND
    T.ACCOUNTNUM = J.INVOICEACCOUNT AND
    T.TRANSDATE = J.INVOICEDATE
  LEFT JOIN (
    SELECT
      DATAAREAID,
      REFRECID,
      ACCOUNTNUM
    FROM
      CUSTTRANSOPEN
    GROUP BY
      DATAAREAID,
      REFRECID,
      ACCOUNTNUM
    HAVING
      COUNT(*) > 0) O ON
        O.DATAAREAID = T.DATAAREAID AND
        O.REFRECID = T.RECID AND
        O.ACCOUNTNUM = T.ACCOUNTNUM
  LEFT JOIN (
    SELECT
      S.DATAAREAID,
      S.TRANSCOMPANY,
      S.TRANSRECID,
      S.ACCOUNTNUM
    FROM CUSTSETTLEMENT S 
      INNER JOIN CUSTTRANS C ON 
        C.DATAAREAID = S.DATAAREAID AND 
        C.RECID = S.OFFSETRECID AND 
        C.TRANSTYPE IN (0, 8, 15) AND 
        S.CANBEREVERSED = 1
    GROUP BY
      S.DATAAREAID,
      S.TRANSRECID,
      S.TRANSCOMPANY,
      S.ACCOUNTNUM
    HAVING
      SUM(S.SETTLEAMOUNTCUR) > 0) P ON
        P.DATAAREAID = T.DATAAREAID AND
        P.TRANSRECID = T.RECID AND
        P.ACCOUNTNUM = T.ACCOUNTNUM AND
        P.TRANSCOMPANY = T.DATAAREAID
WHERE
  J.DATAAREAID = '011' AND
  J.INVOICEDATE >= '2014-06-01'

Since it might matter: here are the existing indexes on the table involved:

因为它可能很重要：这里涉及的表上的现有索引：

CUSTINVOICEJOUR
Index Name               Columns                                                        Clustered   Primary Key   Unique
I_062INVOICEACCOUNTIDX   DATAAREAID, INVOICEACCOUNT, INVOICEDATE                        False       False         False
I_062INVOICENUMIDX       DATAAREAID, INVOICEID, INVOICEDATE, NUMBERSEQUENCEGROUP, RECID True        False         True
I_062ORDERACCOUNTIDX     DATAAREAID, ORDERACCOUNT, INVOICEDATE                          False       False         False
I_062PARMIDX             DATAAREAID, PARMID                                             False       False         False
I_062RECID               DATAAREAID, RECID                                              False       True          True
I_062SALESIDDATEIDX      DATAAREAID, SALESID, REFNUM, INVOICEDATE                       False       False         False
I_062VATNUMIDX           DATAAREAID, VATNUM                                             False       False         False

CUSTTRANS
Index Name               Columns                                    Clustered   Primary Key   Unique
I_078ACCOUNTDATEIDX      DATAAREAID, ACCOUNTNUM, TRANSDATE          True        False         False
I_078BILLOFEXCHANGEIDX   DATAAREAID, BILLOFEXCHANGEID               False       False         False
I_078INVACCOUNTDATEIDX   DATAAREAID, INVOICE, ACCOUNTNUM, TRANSDATE False       False         False
I_078INVOICEACCOUNTIDX   DATAAREAID, INVOICE, ACCOUNTNUM            False       False         False
I_078PAYMIDDATEIDX       DATAAREAID, PAYMID, TRANSDATE              False       False         False
I_078RECID               DATAAREAID, RECID                          False       True          True
I_078VOUCHERDATEIDX      DATAAREAID, VOUCHER, TRANSDATE             False       False         False

CUSTTRANSOPEN
Index Name            Columns                           Clustered   Primary Key   Unique
I_865ACCOUNTDATEIDX   DATAAREAID, ACCOUNTNUM, TRANSDATE True        False         False
I_865RECID            DATAAREAID, RECID                 False       True          True
I_865REFRECIDX        DATAAREAID, REFRECID              False       False         False

CUSTSETTLEMENT
Index Name                       Columns                                                               Clustered   Primary Key   Unique
I_075OFFSETCOMPANYRECTRANSREC7   DATAAREAID, OFFSETCOMPANY, OFFSETRECID, TRANSRECID, SETTLEMENTGROUP   False       False         False
I_075OFFSETVOUCHERIDX            DATAAREAID, OFFSETTRANSVOUCHER                                        False       False         False
I_075RECID                       DATAAREAID, RECID                                                     False       True          True
I_075SETTLEMENTGROUPIDX          DATAAREAID, SETTLEMENTGROUP                                           False       False         False
I_075TRANSINDEX                  DATAAREAID, TRANSRECID, TRANSDATE                                     True        False         False

Also the size of the tables might matter, or at least gives an idea of the number of records I'm dealing with:

表格的大小也可能很重要，或者至少可以了解我正在处理的记录数量：

CUSTINVOICEJOUR
DATAAREAID       Nbr
011          1513668
012                2
ash           355735
bar           268795
euk           692242
hlm           866154
lil           136163
prv             3180

CUSTTRANS
DATAAREAID       Nbr
011          2383870
012                4
ash           428161
bar           367620
bol               45
euk           630029
hlm          1377005
lil           167405
prv             4148

CUSTTRANSOPEN
DATAAREAID    Nbr
011          6119
012             4
ash          5845
bar          1876
bol            29
euk          8077
hlm          2426
lil          2173
prv           190

CUSTSETTLEMENT
DATAAREAID       Nbr
011          2469546
ash           462982
bar           415329
bol               18
euk           684421
hlm          1419857
lil           178551
prv             4325

2 个解决方案

#1

Without knowing more about the execution plan, or the selectivity of the data of the individual columns, and taking into account the query and indexes you posted, it might be worthwhile trying to create covering indexes on the tables.

如果不了解执行计划或单个列数据的选择性，并考虑到您发布的查询和索引，则可能值得尝试在表上创建覆盖索引。

A quick glance at the query leads me to the following index for the tables involved (basically using every column involved in a WHERE clause, or JOIN:

快速浏览一下查询会引出我所涉及的表的以下索引（基本上使用WHERE子句中涉及的每个列，或者JOIN：

CUSTINVOICEJOUR - DATAAREAID, INVOICEID, NVOICEACCOUNT, INVOICEDATE, RECID, ACCOUNTNUM
CUSTTRANS - DATAAREAID, INVOICE, ACCOUNTNUM, TRANSDATE, RECID
CUSTTRANSOPEN - DATAAREAID, REFRECID, ACCOUNTNUM
CUSTSETTLEMENT - DATAAREAID, RECID, TRANSTYPE, CANBEREVERSED, TRANSRECID, ACCOUNTNUM, TRANSCOMPANY

Keep in mind the above order of the columns is based on the order in which I spotted them in the query. To figure out the ideal order, determine the selectivity per column, and order them in the order based on that, from high selectivity to low.

请记住，列的上述顺序基于我在查询中发现它们的顺序。为了找出理想的顺序，确定每列的选择性，并根据它从高选择性到低的顺序对它们进行排序。

Try and record the selectivity path before adding a new index, then add the index and check the execution path again to see whether or not the new index is being used (they should be. If they aren't it might indicate a column is missing). When then moving onto the next index, also keep an eye on the indexes you might have just added, just to see if the index you just added might impact the execution path from a moment ago (It shouldn't happen if you caught all the columns used, but this type of behavior could never be ruled out 100%).

在添加新索引之前尝试并记录选择性路径，然后添加索引并再次检查执行路径以查看是否正在使用新索引（它们应该是。如果不是，则可能表示缺少列）。然后转到下一个索引，同时关注你刚刚添加的索引，看看你刚刚添加的索引是否会影响刚刚执行的路径（如果你抓到所有索引，就不会发生这种情况）使用的列，但这种行为永远不能排除100％）。

Another good thing to keep an eye on is the statistics returned by the server. When adding new indexes, the goal should be to lower the amount of physical reads (disk-based search), and convert them to logical reads (from memory, which is cheaper).

另一个值得关注的好处是服务器返回的统计信息。添加新索引时，目标应该是降低物理读取量（基于磁盘的搜索），并将它们转换为逻辑读取（从内存中更便宜）。

Your mileage might vary, but the above get you off on a good start.

您的里程可能会有所不同，但上述情况会让您有个好的开始。