New post
AllAccepted answers
Brendan Bannon · Feb 14, 2018

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?P...

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

Brendan Bannon · Jan 4, 2018

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.

Brendan Bannon · Nov 3, 2017

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.

Brendan Bannon · Nov 2, 2017

I think I am getting more confused :(

 

Can you email me the class so i can see what you have?  Brendan@intersystems.com

 

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.

Brendan Bannon · Nov 2, 2017

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">
<Delimiter>"^"</Delimiter>
<Node>0</Node>
<Piece>2</Piece>
</Data>

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.

Brendan Bannon · Oct 31, 2017

Loren

 

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?

Brendan Bannon · Oct 11, 2017

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

Brendan Bannon · Oct 10, 2017

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.

Brendan Bannon · Jul 18, 2017

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

Brendan Bannon · Jun 15, 2017

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.

Brendan Bannon · Jun 7, 2017

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