It is up to the developer to decide how much processing may be delegated to the central database engine and how much may be processed locally, for example by the WebScript engine.
Usually data of global scope are processed centrally and data of local scope are processed locally by the WebScript engine running on web servers.
The challenge is when there are too many results of local scope data produced, which then require further processing, normally performed by the database. Processing such results using arrays, strings, JSON objects or other data structures on web servers can be inefficient, since, in many cases, data of local scope are still tables (cursors). Therefore developers often delegate such processing of local scope to the central database engine, which natively supports table structures. With heavy concurrent activity, such delegation can elevate the risk of inadvertently overloading the database engine,
Arnica Platform provides a solution for cases like this by engaging Arnica NanoQuery-based CacheDB.
To engage CacheDB, the query headers must specify query output destination to CacheDB:
<! Language=SQL DataSource=MyProductionDatabase1 Output=@CacheDB CursorName=Products !>
This way, the query executed against production database will send result for the final processing to CacheDB instead of the WebScript. In this example, the results will be present in CacheDB as a cursor Products.
Subsequent query executions can add more cursors to the same CacheDB instance:
<! Language=SQL DataSource=MyProductionDatabase2 Output=@CacheDB CursorName=Orders !>
<! Language=SQL DataSource=MyProductionDatabase3 Output=@CacheDB CursorName=Customers !>
In this example, CacheDB will get cursors Products, Orders, Customers as a result. API engine (WebScript) doesn't yet have access to this data, but it can send another query which can do post-processing of the results in the CacheDB, creating possibly a new cursor OrderResults as a derivative of JOINing and UPDATEing data in the three existing cursors. WebScript would typically send a query with the following query header:
<! Language=SQL DataSource=@CacheDB !>
The actual queries are not presented in this article, but they can be any SQL queries.
Finally, when the results of post-processing are ready in the CacheDB-based cursor OrderResults, the following 1 line command may be executed in the API code to bring results to the web server tier and use it to create presentation or API response:
core.dbs.CacheDBToCursor("OrderResults")