Skip to main content

Notifications

Microsoft Dynamics AX forum
Suggested answer

Query using Global Temporary Table

Posted on by 20

Hi Again, You guys were so helpful with my last question, here I am again

Do excuse me, I 'm just a DBA so understand I don't have much (any) knowledge on application side.

We upgraded our Database from SQL Server 2014 to 2016 and have enabled query store, but it keeps filling up.

Investigation shows almost all queries are of format as below:

(@P1 int,@P2 int,@P3 nvarchar(11),@P4 datetime2(7),@P5 datetime2(7),@P6 datetime2(7),@P7 nvarchar(21),@P8 nvarchar(21),@P9 nvarchar(5))SELECT T1.ITEMRELATION,T1.ACCOUNTRELATION,T1.AMOUNT,T1.INVENTDIMID,T1.FROMDATE,T1.TODATE,T1.ITEMCODE,T1.ACCOUNTCODE,T1.UNITID,T1.RECID,T1.CURRENCY,T1.DATAAREAID FROM PRICEDISCTABLE T1 WHERE (((PARTITION=5637144576) AND (DATAAREAID IN (SELECT DATAAREAID FROM ##Tmp75697757FA474E828188E79A5D23CE16) )) AND (((((((ITEMCODE=@P1) AND (ACCOUNTCODE=@P2)) AND  NOT ((UNITID=@P3))) AND (FROMDATE<=@P4)) AND ((TODATE>=@P5) AND (TODATE<=@P6))) AND (ACCOUNTRELATION=@P7)) AND ((ITEMRELATION=@P8) AND (DATAAREAID=@P9)))) ORDER BY T1.RELATION,T1.ACCOUNTCODE,T1.ACCOUNTRELATION,T1.CURRENCY,T1.ITEMCODE,T1.ITEMRELATION,T1.UNITID,T1.QUANTITYAMOUNTFROM,T1.FROMDATE,T1.AGREEMENTHEADEREXT_RU


(@P1 int,@P2 int,@P3 nvarchar(11),@P4 datetime2(7),@P5 datetime2(7),@P6 datetime2(7),@P7 nvarchar(21),@P8 nvarchar(21),@P9 nvarchar(5))SELECT T1.ITEMRELATION,T1.ACCOUNTRELATION,T1.AMOUNT,T1.INVENTDIMID,T1.FROMDATE,T1.TODATE,T1.ITEMCODE,T1.ACCOUNTCODE,T1.UNITID,T1.RECID,T1.CURRENCY,T1.DATAAREAID FROM PRICEDISCTABLE T1 WHERE (((PARTITION=5637144576) AND (DATAAREAID IN (SELECT DATAAREAID FROM ##Tmp6A9EE1F985E947B2A2C26885B3F5B164) )) AND (((((((ITEMCODE=@P1) AND (ACCOUNTCODE=@P2)) AND  NOT ((UNITID=@P3))) AND (FROMDATE<=@P4)) AND ((TODATE>=@P5) AND (TODATE<=@P6))) AND (ACCOUNTRELATION=@P7)) AND ((ITEMRELATION=@P8) AND (DATAAREAID=@P9)))) ORDER BY T1.RELATION,T1.ACCOUNTCODE,T1.ACCOUNTRELATION,T1.CURRENCY,T1.ITEMCODE,T1.ITEMRELATION,T1.UNITID,T1.QUANTITYAMOUNTFROM,T1.FROMDATE,T1.AGREEMENTHEADEREXT_RU

Notice the global temporary table for the DATAAREAID is different each time.

Is this standard Dynamics AX process or query, or do you think it might be some customization?  If the latter where might we look to change it?

Thanks in advvance

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Query using Global Temporary Table

    Not sure but most likely it's related to using a (standard) feature called "Virtual companies". That allows you to share some data between many legal entities.

  • patrickjolliffe Profile Picture
    patrickjolliffe 20 on at
    RE: Query using Global Temporary Table

    Note I've been doing some tracing, beleive that this is the sql statement that creates/populates the GTT

    SELECT D.ID AS DATAAREAID, VIRTUALDATAAREA = CASE WHEN V.VIRTUALDATAAREA IS NULL THEN D.ID ELSE V.VIRTUALDATAAREA END INTO ##Tmp0BB4E28BF15941CFB9A10BA48DDC7F0D FROM DATAAREA D LEFT JOIN VIRTUALDATAAREALIST V ON D.ID = V.ID WHERE D.ID IN(N'7110')

  • patrickjolliffe Profile Picture
    patrickjolliffe 20 on at
    RE: Query using Global Temporary Table

    Yep, I'm reaching out and trying to get this resolved as you suggest, just wondered if someone on this forum might also have some clue.  If not then no worries.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Query using Global Temporary Table

    I suggest you talk with your AX team (or partner) about this. That way you can together pinpoint the exact process that does this query, and they can tell you if there's some customization involved or not.

  • patrickjolliffe Profile Picture
    patrickjolliffe 20 on at
    RE: Query using Global Temporary Table

    Yes, I understand that, but the question is whether for this specific query it is the way it has to be?  The problem really seems isolated to the specific query against PRICEDISCTABLE (only difference being the name of the GTT).  So I'm trying to understand this is a customization, or standard process, and whether any way to adjust to just use the DATAAREAID from a parameter rather than creating and populating GTT and joining to that each time.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Query using Global Temporary Table

    The statements are different all the time since the tempdb tables are created/recreated by AX a lot.

  • patrickjolliffe Profile Picture
    patrickjolliffe 20 on at
    RE: Query using Global Temporary Table

    I have no concern about the queries themselves (well I might be if I started looking but let's not get side-tracked).

    The problem is that because for each execution the SQL statement is slightly different (GTT name is different), it means that it is filling up SQL Server Query Store, one record for each execution.

    These are 99.9% of Query Store contents, and it's filling query store up (I suspect) faster than the purging mechanism is working.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Query using Global Temporary Table

    AX uses TempDB heavily. Are you conserned about these two queries, or large amount of TempDB queries in general?

  • patrickjolliffe Profile Picture
    patrickjolliffe 20 on at
    RE: Query using Global Temporary Table

    No, that's the full query :)

    So the "AND (DATAAREAID IN (SELECT DATAAREAID FROM ##Tmp75697757FA474E828188E79A5D23CE16)" is bit is just the way that Dynamics AX handles the fact there may be one or more DATAAREAID's to query?  Ie it creates a Global Temporary Table, populates it, and then joins to it.

    It's a right pain because all these queries are swamping our Query Store

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Query using Global Temporary Table

    Yes, seems pretty normal. Unfortunately you didn't share full queries, only part of it but to me it looks normal.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,867 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,173 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans