User Answers

Hi Jimmy

Just to make sure we are talking about the same thing:  in Cache the Primary Key is not always the ID Key.  I think you are really talking about the ID Key, used in the subscripts of the D global.  If I am correct then you have an index define on Year , Month, OrderNo with IDKey True.

From a Parent Child point of view having a compound IDKey is fine.  The IDKey of the parent will be something like "2018||11||1" and then the IDKey of the children will just add one more subscript:  "2018||11||1||child 1

Hello I am trying to get a good example, but for now I will just try to give you a general description. As the Docs say this hint is used in a subquery. At compile time the optimizer has 2 options for dealing with a subquery: 1) Treat it as a black box, executing the subquery independently from the other query and then applying the results to the outer query 2) rewrite the query incorporating the subquery into the outer query as a JOIN and combining conditions from the subquery to the WHERE clause of the outer query.


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.


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.

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)

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

If you really are doing just a SELECT COUNT(*) FROM Table does your class have a Bitmap extent?  That is the fastest way to get the answer for a COUNT(*).


If you have a BitMap Extent and the query is still slow then you might need to clean up your bitmap.  For tables that have a large number of rows deleted, Link most Ensemble table, over time a bitmap will slow down.


You can clean them up by using a system utility:  %SYS.Maint.Bitmap

I agree with Vitaliy.  You should look at the index, assuming there is an index on A and make sure it has all the values your expect.


if the index has all the rows and the query is not returning them it is a Bug that ISC needs to look into ASAP.