Hi Jimmy

If I understand correctly the only table that will support a bitmap is the Order Table on Server A.  All the other tables have compound keys and will not support bitmaps.

It is possible to keep the Parent Child relationship and split the children out into their own globals.  You just change the Global node in the Mapping to something else.  That will make your Journal filter code a little more complex but it could improve performance a good bit.

The fact that you have OrderDetails and OrderDetails2 makes me think there is a lot of details for every order.  If are storing a lot of data for each row and each order has a large number of children your global is going to be large and that will slow down query performance.  Just switching the children to different globals could help.

Building Indices talks about about the fastest ways for you to rebuild an index on different types of systems.  You might find this helpful for your shadow filter code.

I still recommend using default IDKeys and Foreign Keys but without know what the really important queries are and the Properties and TuneTable data it is hard to know how hard I should push you in that directions.

Bitmaps are great for properties that have fewer than 10,000 distinct values.  The fewer the values the better the performance.  For example Gender is a great property to use with a bitmap while PatientID is a very bad property to bitmap.  The other big win with bitmaps is using multiple bitmaps together to solve a query.  While we can do this multi index solution with standard indices as well the performance with bitmap is MUCH faster.  For example if you want to find all the men who live in a given state and have a given eye color your where clause would be:  WHERE Gender = ? and State = ? and EyeColor = ?  If you have a bitmap on each of these properties the query plan would contain something like:  Combine Bitmap GenderIndex INTERSECT StateIndex INTERSECT EyeColorIndex  

Now you are thinking to yourself that you could define a standard index on (Gender, State, EyeColor) and get great performance and you would be correct, but what happens when the query changes so it does not include Gender?  Now your compound index will not perform as well but the bitmaps will still be used and give great performance StateIndex INTERSECT EyeColorIndex.   Compound indices are great to solve a specific query, but defining multiple bitmap instead gives you much more flexibility to solve more queries.

Hope this helps.  If you want to get into more specific questions about a query I can help with that.

Brendan

Hi Sean

2 reasons:  

    1:  In general you get better performance if the data is stored in 2 different global.  The only time having the data in 1 global is better is when you query both the parent and the child together.  If you write a query against just one you still end up loading the other table as well.  So over all 2 globals means less disk IO.

    2:  You can't use bitmaps in the child table because the ID of the child is a String and Bitmaps only work when the IDKey is a positive Integer.

The downside of using a Foreign Key is with Objects you don't have the methods to get at children like you do with a Relationship.

Brendan

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

I don't think this is the right direction.  I believe the problem is DUZ(2) is not always defined so queries get an <UNDEFINED> error.  The user should not need to know anything about this variable, and if it is used to limit what data a person can see they REALLY should not have access to this variable.

In all other cases of mapping stuff like this the application would take care of setting the variable up and the user had no knowledge of the variable at all.  Using SetServerInitCode() will make sure the variable is defined before the query is run.  No changes to the class or your queries are needed.

Brendan

Charles

DUZ(2) is a fileman variable.  Before you can make use of this table you need to run some fileman code that will populate this variable.  You can setup the system to execute the code for people trying to run queries over xDBC or from the Portal by using the following command

$SYSTEM.SQL.SetServerInitCode(code)

Where code would be a COS command to setup any needed variable.

brendan

Hi Jo

the second example talks about this very case, it is limited to 2 namespaces, but you can generalize the code to look at as many Namespaces as you want. 

The class is: Mapping.TwoNamespacesOneGlobal.xml

There is a link at the end of the article to let you download a zip file with all my examples.  If that does not work please let me know and I can send it to you directly.

brendan

Felipe

Something is messed up with my Studio so I can't open the wizard and get you a picture either.  I can give you the storage in XML format.

In this example we have 5 properties.  The IDKey, used as the subscript, 2 properties stored in the person global and 2 properties stored in the emp global.

The storage is setup to loop over the ^person global.  We then use the Retrieval Code for the Title and HireDate Properties in the Data section of the storage to get the data out of the ^emp global

Here is my data:

 ^person(1)="Kaitlin^56009"
 ^person(2)="Melissa^56528"
 ^person(3)="Robin^57079"
 ^person(4)="Kieran^58210"
 
 ^emp(1)="Queen for the Day^64815"
 ^emp(2)="First Bride's Maid^64815"
 ^emp(3)="Second Bride's Maid^64815"
^emp(4)="Asher Boy^64815"

and here is my storage:

<SQLMap name="Map1">
<Data name="DOB">
<Delimiter>"^"</Delimiter>
<Piece>2</Piece>
</Data>
<Data name="HireDate">
<RetrievalCode> s {*}=$P(^emp({L1}),"^",2)</RetrievalCode>
</Data>
<Data name="Name">
<Delimiter>"^"</Delimiter>
<Piece>1</Piece>
</Data>
<Data name="Title">
<RetrievalCode> s {*}=$P(^emp({L1}),"^",1)</RetrievalCode>
</Data>
<Global>^person</Global>
<Structure>delimited</Structure>
<Subscript name="1">
<Expression>{EmpID}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^Mapping.SimpleS</StreamLocation>
<Type>%CacheSQLStorage</Type>

If you need help with an specific example send an email to Support@intersystems.com and I can work on that with you.

brendan

Yes I would like a better understanding of the difference as well.

Sadly I just try both and see if either change the query.

brendan

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.