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"

Generally speaking we recommend that you leave the IDKey as a system generated integer and instead of using a Parent Child relationship you define a Foreign Key with Cascade on Delete  to get the same behavior.  You can still define a Primary Key index on Year, Month, and OrderNo so deleted will be fast.  By keeping the data in different globals and using Integers as IDKeys you will be able to define a Bitmap index in all of your classes and will keep disc IO to a minimum when queries the different tables.

Now maybe you say you will not be using Bitmaps and you will never query the children without also referencing the Parent data, so having the data in the same global makes sense.  You need to look at everything  case by case.

Will it work, Yes.

Will it perform the way you want it to, Maybe.

Sorry I can't give you a simple yes or no, but hopefully I have provided the needed info so you can make an educated decision.  As always InterSystems Support is happy to go over your specific details and help you come up with what will work best.

Brendan Bannon

InterSystems Support Manager

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.

If you look at the show plan of a query you can see which option the optimizer is taking. If you see a section of the plan with a heading of SubQuery then you know it is option 1.

If the query does not have a Subquery section we are in option 2.

You can see that with the following query:

select home_state, avg(age) from sample.employee e
where home_state = ANY (select MAX(v.address_state) from sample.vendor v where balance > 0)
group by home_state

will be rewritten

while:

select home_state, avg(age) from sample.employee e
where home_state = ANY (select MAX(v.address_state) from %NOMERGE sample.vendor v where balance > 0)
group by home_state

will keep the subquery.

Mark

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

docs:  http://docs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?…

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

No.

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  

WHERE.....

Scott

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.

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">
<Global>^SPMORDP</Global>
<Subscript name="1">
<Expression>2</Expression>
</Subscript>
<Subscript name="2">
<Expression>{StatusCode}</Expression>
</Subscript>
<Type>bitmap</Type>
</SQLMap>

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">
<Expression>$$SQLUPPER({StatusCode})</Expression>
</Subscript>

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">
<Global>^SPMORDP</Global>
<Subscript name="1">
<Expression>3</Expression>
</Subscript>
<Type>bitmapextent</Type>
</SQLMap>

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

Brendan

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

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

2010.2 is 7 years old at this point.  Even if we could identify a fix for this problem back porting a fix that far is not recommended.

There are a couple of versions that use process private memory for GROUP BY but I don't think Cache 2010.2 is one of them, I was questioning myself as I was typing my first entry.

Looking at your post again you don't say what value you have for process memory.  When working with SQL we strongly recommend that you change this to the max value, 49M.

I still would like to see the full error message.

Brendan

Can you give a little more info about the query format and the details of the <STORE> error.  In simplest terms:  

SELECT * FROM VeryLargeTable

will not result in a STORE error.  So you either have a GROUP BY or you are doing other things with the results and that is leading to a <STORE> error.

Brendan

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

There is not much in the docs about this Util but you can have a look at the Class Reference to see examples of how to use it.

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.

If there is no system function exposing this info you could create a a class using Cache SQL Storage to "Map" this global and expose the info to SQL.

I wrote some stuff explaining the basics here:  The Art of Mapping Globals to Classes 1 of 3

brendan

Nicki

An Array of Objects is basically the same as a Parent Child relationship storing the child data in the same global as the parent.  

When people create the third class CinemaFilms they are do that to create a Many to many relationship between Cinema and Films.  One Cinema shows many Films and one Film will be shown in many Cinemas.

For performance reasons we suggest using Foreign Keys instead of relationships.  You can still setup the Parent Child behavior with Cascading Delete.

I don't see why using a Foreign Key would break Referential Integrity.

brendan

Alexander

I think you need to use 2 parameters in your COS code

s sp1="child"

s sp2="health"

&SQL(

DECLARE c1 CURSOR FOR

SELECT ID INTO :id FROM ICD WHERE Name %CONTAINS (:sp1, :sp2)

This would be equivalent to what you are doing in the Portal.

Brendan