New post
Question
· Aug 3, 2020

$order cached? Delete Cache of SQL query/$order called in an API.

#API #Globals #JSON #Caché

Hello Intersystems-Community,

I build an API and there I use $order to get the Data from my Globals.
When I first time use that API it's get an error (504 Gateway-Timout > 60 Sec.).

After 2 reloads it works with a loading time of 2 Sec.! Sounds for me like the $order is cached and it works faster. Is it true $order will be cached?

When it works, I only get the error at the next day. So it's very hard to reproduce that error. Is it possible to delete the cache for $order?

Example:

 

        set a = ""
        for {
            set a = $order(^SD031S("0","7",x,a))
            if (a = "") {quit}
            set b = ""
            for {
                set b = $order(^SD031S("0","7",x,a,b))
                if (b = "") {quit}
                if ($piece($get(^SD031("0",a,b,1),""),Y,9) = x) {
                    
                    set c = a
                    set d = ""
                    for {
                        set d = $order(^FA060S("0","22",c,b,c,d))
                        if (d = "") {quit}
                        if ($piece($get(^FA060("0",c,d,2),""),Y,25) = b) {
             
                            #dim innerObject = ##class(%ZEN.proxyObject).%New()
                            set articleList = ##class(test.test).getArticle("SB02906",c,d,"0","1")
                       
                            set dataLength = 0
                            set dataLength = artcleList.Count() 
                            
                            set innerObject.d = d
                            set innerObject.c = c
                            set innerObject.dataExist = $case(dataLength, 0:0, :1)                             
                            do responseList.Insert(innerObject)                         }
                    }                 }
                     
            }
            
                
        }
        
    }
            
    set object.response = responseList
    set object.result = $$$YES
    set object.message = ""     set x = ##class(%ZEN.Auxiliary.jsonArrayProvider).%WriteJSONStreamFromObject(.json,object)     return $$$OK

Thanks and Best regards

Florian Hansmann

Discussion (8)2
Log in or sign up to continue
Robert Cemper · Aug 3, 2020

As these buffers are a (hopefully large) pool that belongs to your whole installation.
They are only cleared if you restart Caché.
But you should probably take a look at your "disk drives" (whatever type they are).
Their performance might be worth to check
[in quotes as they might be virtual in some way]  

In addition, a  performance check with WRC might make sense.
They are top experts to the subject.

If you have a large buffer pool you might run a $QUERY() loop across the whole global
in the morning and hope it stays there long enough. Some installations practice it that way.

1 0
Jochen Roese · Aug 3, 2020

I'm not sure if I'm correct here but I think you can eliminate 2 for-loops and the call to getArticle():

In SD031 there should be only one key for "x" (should be a 1:1 relationship), also try to avoid setting up a list just to .count() to see if "dataExists". You should use something like $data (in ^SB02906 there could be one dataset for every item you sale, for every customer). That should speed everything up a little.

My approach:

set a = $order(^SD031S("0","7",x,a))

        if (a '="") {

            set b = $order(^SD031S("0","7",x,a,b))

            if (b '= "") {

                if ($piece($get(^SD031("0",a,b,1)),Y,9) = x) {

                    set c = a

                    set d = ""

                    for {

                        set d = $order(^FA060S("0","22",c,b,c,d))

                        if (d = "") { quit }

                        if ($piece($get(^FA060("0",c,d,2),""),Y,25) = b) {

                            #dim innerObject = ##class(%ZEN.proxyObject).%New()

                            set innerObject.d = d

                            set innerObject.c = c

                            set k = $o(^SB02906("0",c,d,""))

                            if (k'="") {

                                set innerObject.dataExist = ($d(^SB02906("0",c,d,k,"EUR","0","1"))'=0)

                            } else {

                                set innerObject.dataExist = 0

                            }

                            do responseList.Insert(innerObject)

                        }

                    }

                }     

            }   

        }

Also that caching problem should not exist in production because FA060 and SD031 are read for almost everything in your ERP so it stays in the cache. Otherwise you probably need to create a background-job that reads a few global-nodes every 5-10 minutes to get it cached.

0 0
Danny Wijnschenk · Aug 3, 2020

You should not always rely that all data is in cache and processing of the data can be done before a timeout occurs. (data will grow, more users can use the system, ...)
For REST queries that have the risk of running too long,  you can do as follows :
- the api responds immediately with a queryId (some sequential number),
- the query continues to run in the background, and the response of the query will be stored in some table/global/...  with the queryId as the key
- a separate api call is used by the client (with the queryId) to get the response (or a 'not ready' status when the query is still running).
It is a little more work on the client to call once to launch the query, and a second.. time to get the results, but you are safe when your systems  grows with data or users

2 0