Contents tagged with CTE

  • Improve performance of grouping operations in Acumatica

    Hello everybody,

    today I want to write a note about interesting case I faced recently. 

    I was asked to load from Acumatica database Sales orders joined with PO Receipts by inventory, but I had one issue. It was needed to join only by latest PO Receipt, not by allo PO Receipts. In other words it was needed to take only one element of each group. 

    As usually such kind of implementation is done with help of overloading delegate, which loads all data from SQL Server into memory and then does grouping operaions in memory. Such approach is workable on relatively small cases, but if database of customer 

    is around 130 Gb, then we faced timeout issue. 

    How to deal with it. 

    After some trial … more

  • Hierarchy for CTE in MS SQL

    Hello everybdoy,

    today I want to write some notes about hierarchy research in ms sql database. 

    Imagine you have following structure in your table 


    id               |           rootID   |     some additional fields

    1                |            1          |      ----

    2                |            1          |      ----

    3                |            2          |      ----

    4                |            2          |      ----

    5                |            2          |      ----

    and image that you need to get info like who is below 1. 

    for this purpose following sql can be useful:

    -- wrap up tree selection into tvf

    create function HierarchyFunction(@ … more