We have a dashboard that allows us to connect GLJrnDtl postings to PartTran and other related data. We use this to give more detail to the financial transactions as there are often follow-up questions for certain accounts.
My issue is that it is terribly slow. If I try to run it for more than 1 or 2 accounts over a period of more than a month, it usually times out. Even if there are only a dozen records that come back. It’s crazy. Is there something I can do to try to optimize this or maybe a better way to go about this? Here is my diagram of my joins.
Have you considered adding subqueries? If you have one subquery that restricts the number of records and then add the extra “decorating” tables, then it won’t have to make all of the joins and then select records. The Execution Engine is pretty smart, but this may help it when creating an execution plan.
