Do you have an index on c_drugfull?

The most likely difference between the 2 queries above would be the first using that index and the second not using the index.  If the index has bad data that would explain the issue.

Of course there is a good chance your test from SQL Server would also use that index so I could be wrong.

Can you run the problem query directly on the Cache server in the System Management Portal?  If you get wrong results there you can look at the Show Plan to see if an index is involved.  If Yes you ran run $SYSTEM.SQL.%ValidateIndices() to see if there is a problem


If that does not explain what is going on please contact InterSystems support so we can dig in further.


You must be starting on a version that supports Frozen Query Plans.  In order to freeze a plan the query compiler needs to save some internal information so it can regenerate the same code on recompile.

2016.2 is the first version to support frozen plans.

Any upgrade from a version of 2016.2 or higher will automatically freeze all query plans.  This means you should see the exact same SQL performance after upgrade.  No more surprises with a query running much slower on the new version.  Of course that mean no improvement either until you test the plan to see if it has changed.

I think what you want to do is write this a a JOIN without the subquery and then use %INORDER to force the compiler to start with the account table.  then we should pick the Type index and everything should work the way you want.

from %INORDER Account  JOIN Transaction 
on Account.Id = Transaction.Account  



Not that I have seen a lot of outbound adaptor code, but I have never see TSQL and a query sent over the wire like this.

If you just send the SELECT does it work?

are you getting any info back from the execute you are logging?

$$$LOGINFO("tSC = "_tSC)

THe next set would be to look at Java Gateway log or any logging SQL Server might have to see what we are sending over to SQL Server.

If none of this point to the issue I would suggest opening a WRC issue and getting one of our Ensemble  to have a look.

I think I am getting more confused :(


Can you email me the class so i can see what you have?


when you talk about the wizard generating the storage are you talking about the fm2class tool?



So guessing one more time it sounds like you have a class that maps data at ^DPT(#)=data

and you want to now add mapping for ^DPT(#,21600,#,0)=data


This looks like a child table to me.  the first subscript identifies a given row and then the third subscript identifies more data that is related to the first one:  Tests for a given patient or items for a given invoice.

Hi Loren


So does your class already map:

^DPT(incrementing unique,21600,incrementing starting at 1) and you just want to look at the subnode ...,0) so you can get data1?


if that is the case you can define Data1 as a new property of the class, then in the Storage Def go to the data section of your master map and add the new Property.  Node would be 0, Delimiter would be "^" and Piece would be 2.


I don't have your full class so I will not show the wizard, but the Storage def should end up with a new data entry that looks like this:


<Data name="Data1">

You should not need to map all the other pieces of data that are stored on that node.


If you are using Objects and SQL to modify data in this class please make sure you can modify Data1 without changing the rest of the data on that node.  I looked at the code we generate in our current product and it is safe, but I know at some point in the distant past that was not the case.  If you are on an old version of Cache it might delete all the other values on that node when you save Data1.


Please let me know if you have any other questions.



As Danny already said you need to add the Property to the map subscript as well, but I am not sure that is the right thing to do.  Most of the time when a new subscript is added it would be a new child class because there can be multiple nodes at that subscript level with repeating data.


If the data is not repeating over multiple node, does that subscript only have one value?  If yes then it would be a constant not a new property.  In the data section you can add constants. this is used for when the data of one row is spread out over multiple global nodes.


Again like Danny said can we see the class and some sample data to better understand what you need to do?

Thanks for sending the class to me Yaniv.  

Life is always easier when you don't have to guess what is happening. 

The 2 options list above are not really options, but rather the steps you should take when defining any type of index for Cache SQL Storage.  Defining the Index def helps us correctly report info to external databases, but it is the map in the storage that the query optimizer is looking for, so #1 is required for all types of indices.  

Here is the property and index

Property StatusCode As %String(COLLATION "EXACT");

Index StatusCodeIdx On StatusCode [ Type = bitmap ];

When you want to define a bitmap you do not add the IDKey as a subscript, it will be generated in the data.  For Yaniv's class this is what the bitmap storage def will look like:

<SQLMap name="StatusCodeMap">
<Subscript name="1">
<Subscript name="2">

If you do not want to use EXACT collation then the Collation that is defined in the Property needs to match the collation defined in the map.  If no collation is listed for a property the default is SQLUPPER, so when the Property looks like this:

Property StatusCode As %String;

the Map needs to have a Subscript that looks like this:

<Subscript name="2">

Also when ever you define a bitmap in a class you should also define a Bitmap Extent.  The class compiler does this automatically for Default Storage, but for Cache SQL Storage you need to define the extent map.  The <Type> is bitmapextent and no fields are listed as subscripts:

<SQLMap name="BitmapExtent">
<Subscript name="1">

If you are using Objects or SQL to modify the data then these indices will be maintained by the generated class code.  If you application is still doing Global sets and kills then you will need to write code to maintain the indices.  You can look at the class Mapping.BitMapExample that is in The Art of Mapping Globals to Classes (5 of 3) to see what that code would look like.

If you have any questions please let me know.

Here is my version of Yaniv's class


Did you look at the Show Plan to see if we are using the Bitmap index you defined?  I am going to guess the answer is no.


Can you show us how you defined the bitmap index?  If the property is defined as a string you should change the collation to Exact, or define the collation in the index.

If you have a look at The Art of Mapping Globals to Classes (5 of 3) there should be an example of defining a bitmap index in Cache SQL Storage.

This looks like a bug in InterSystems code.  Can you please email this problem  to  the WRC:


Please include this table, the parent table and the Version you are using.