Have some free text fields in your application that you wish you could search efficiently? Tried using some methods before but found out that they just cannot match the performance needs of your customers? Do I have one weird trick that will solve all your problems? Don’t you already know!? All I do is bring great solutions to your performance pitfalls!
As usual, if you want the TL;DR (too long; didn’t read) version, skip to the end. Just know you are hurting my feelings.
Looking to breathe new life into an old MUMPS application? Follow these steps to map your existing globals to classes and expose all that beautiful data to Objects and SQL.
Date range queries going too slow for you? SQL Performance got you down? I have one weird trick that might just help you out! (SQL Developers hate this!)*
If you have a class that records timestamps when the data is added, then that data will be in sequence with your IDKEY values - that is, TimeStamp1 < TimeStamp2 if and only if ID1 < ID2 for all IDs and TimeStamp values in table - then you can use this knowledge to increase performance for queries against TimeStamp ranges. Consider the following table:
Class Queries in InterSystems IRIS (and Cache, Ensemble, HealthShare) is a useful tool that separates SQL queries from Object Script code. Basically, it works like this: suppose that you want to use the same SQL query with different arguments in several different places.In this case you can avoid code duplication by declaring the query body as a class query and then calling this query by name. This approach is also convenient for custom queries, in which the task of obtaining the next row is defined by a developer. Sounds interesting? Then read on!
The typical WHERE condition in SQL relates mostly to some content of the rows you work on. So it needs to be calculated and checked for each row you access. Differently (and that's why I named it STATIC) is a WHERE condition that is independent of the rows you access.
Though the access is quite comfortable (in traditional COS sense) there are 2 main restrictions that hurt:
#1) It is not saved to disk unless your application includes code to save it specifically. #2) It cannot be stored in or exposed through SQL tables there are some more I'll show how to overcome these limits
Last week, we announced the InterSystems IRIS Data Platform, our new and comprehensive platform for all your data endeavours, whether transactional, analytics or both. We've included many of the features our customers know and loved from Caché and Ensemble, but in this article we'll shed a little more light on one of the new capabilities of the platform: SQL Sharding, a powerful new feature in our scalability story.
The field test of Caché 2016.2 has been available for quite some time and I would like to focus on one of the substantial features that is new in this version: the document data model. This model is a natural addition to the multiple ways we support for handling data including Objects, Tables and Multidimensional arrays. It makes the platform more flexible and suitable for even more use cases.
While the classic solution followed rather close the concepts and design of the ancestors Caché / IRIS allows a more modern approach to flexible/multidimensional properties
A VIEW in SQL is basically a prepared SQL statement. It has to be executed and assembled like any other SQL query. MATERIALIZED VIEW means that the content is collected before hands and can be retrieved rather fast. I saw the concept first with my favorite competitor named O* and they made a lot of noise about it. { favorite: because I could win every benchmark against them }
The object and relational data models of the Caché database support three types of indexes, which are standard, bitmap, and bitslice. In addition to these three native types, developers can declare their own custom types of indexes and use them in any classes since version 2013.1. For example, iFind text indexes use that mechanism.
InterSystems IRIS 2020.1 brings a broad set of improved and new capabilities to help build important applications. In addition to the many significant performance improvements accrued through 2019.1 and 2020.1, we are introducing one of our biggest changes in recent SQL history: the Universal Query Cache. This article provides more context on its impact to SQL-based applications at a technical level.
As Bill has mentioned earlier in his post, we have carefully reviewed the JSON capabilities and made some adjustments to ensure they deliver the best benefit to you. In this post, I am going to describe the modifications in more detail and provide guidance for you to understand the implication for your code base.
No doubt bitmap indexing, if used with a suitable property, performs just impressive! But there is a major limit: ID key has to be a positive integer. For modern class definitions working with CacheStorage this is a default.
BUT: There are hundreds (thousands ?) old applications out in the field that are still using composite ID keys. Or - to name the origin - work on Globals with 2 subscript levels (or more). They are by construction excluded from our "Bitmap Wonderland".
If you are looking to breathe new life into an old MUMPS application follow these steps to map your globals to classes and expose all that beautiful data to Objects and SQL.
This example is going to cram in 4 or 5 different things beyond what was covered in Part 1
This is a coding example working on IRIS 2020.1 and on Caché 2018.1.3
It will not be kept in sync with new versions
It is also NOT serviced by InterSystems Support !
Every now and then you may encounter a situation where for various reasons ODBC is the only option to access a remote system. Which is sufficient as long as you need to examine or change tables. But you can't directly execute some commands or change some Global.
Special thanks @Anna Golitsyna for inspiring me to publish this.
If you define a Persistent Class / Table the class compiler generates for you an appropriate Storage definition. A different option is to define a SQL mapping for an already existing Global storage. This has been excellently explained already in a different series of articles. The Art of Mapping Globals to Classes 1 of 3
In my earlier article on Adopting Bitmaps I described the technique already. Now you can find a code example also on Open Exchange.
This is a coding example working on Caché 2018.1.3 and IRIS 2020.2
It will not be kept in sync with new versions
It is also NOT serviced by InterSystems Support !
A good writer is supposed to draw you in with the title and bury the answer somewhere in the article. I suppose this makes me a bad writer – don’t think less of me, my whole feeling of self-worth comes from the opinions of internet strangers!
Order is a necessity for everyone, but not everyone understands it in the same way
(Fausto Cercignani)
Disclaimer: This article uses Russian language and Cyrillic alphabet as examples, but is relevant for anyone who uses Caché in a non-English locale. Please note that this article refers mostly to NLS collations, which are different than SQL collations. SQL collations (such as SQLUPPER, SQLSTRING, EXACT which means no collation, TRUNCATE, etc.) are actual functions that are explicitly applied to some values, and whose results are sometimes explicitly stored in the global subscripts. When stored in subscripts, these values would naturally follow the NLS collation in effect (“SQL and NLS Collations”).
As we all know, Caché is a great database that accomplishes lots of tasks within itself. However, what do you do when you need to access an external database? One way is to use the Caché SQL Gateway via JDBC. In this article, my goal is to answer the following questions to help you familiarize yourself with the technology and debug some common problems.
If you are looking to breathe new life into an old MUMPS application follow these steps to map your globals to classes and expose all that beautiful data to Objects and SQL.
If the above does not sound familiar to you please start at the beginning with the following:
There are three things most important to any SQL performance conversation: Indices, TuneTable, and Show Plan. The attached PDFs includes historical presentations on these topics that cover the basics of these 3 things in one place. Our documentation provides more detail on these and other SQL Performance topics in the links below. The eLearning options reinforces several of these topics. In addition, there are several Developer Community articles which touch on SQL performance, and those relevant links are also listed.
There is a fair amount of repetition in the information listed below. The most important aspects of SQL performance to consider are:
The types of indices available
Using one index type over another
The information TuneTable gathers for a table and what it means to the Optimizer
How to read a Show Plan to better understand if a query is good or bad
The Caché System Management Portal includes a robust web-based SQL query tool, but for some applications it’s more convenient to use a dedicated SQL client installed on a user’s PC.
SQuirreL SQL is a well known open source SQL client built in Java, which uses JDBC to connect to a DBMS. As such, we can configure SQuirreL to connect to Caché using the Caché JDBC driver.
By likes
5m read
Open Exchange app
}.png)