I agree with Danny.  I would want to see the classes and the Show Plan for the 2 queries.

Did you define any indices in the classes and not populated them?

Ya I am trying to figure that out.  I now have a link that looks like it should work but it does not.

Sorry for not answering this sooner.  In Cache / InterSystems IRIS when you define a Parent Child relationship between 2 classes the Child class has an IDKey made up of 2 values:  a reference back to the Parent and a unique identifier.  Because the IDKey is a compound key you can not define a Bitmap index in the Child Ccass.  That is drawback #1.   Bitmap indices are not the be all / end all, but they can give you GREAT performance so it is nice to keep this option on the table.

The Parent Child relationship Storage is the second drawback, while it is not required, and it might not always be a drawback, the classic storage for a Parent Child relationship is to store all the data in the Parent Global. 

Now to explain this I want to make sure everyone knows what I am talking about when I say Global.  InterSystems stores persistent data in a sparse tree structure that we call Globals.   Subscripts are always stored in ASCII Collated order, so if I did it right the way I show the globals here is the same way they would be stored.  A simple persistent class will store the data in a global like this:  ^Package.ClassD(id), where id in a unique integer.  So 4 rows in this table could look like:

      ^Package.ClassD(1)=$LISTBUILD("data 1")

      ^Package.ClassD(2)=$LISTBUILD("data 2)

      ^Package.ClassD(3)=$LISTBUILD("data 3")

      ^Package.ClassD(4)=$LISTBUILD("data 4")

If we define a Child table in this class then we will store that child data in a lower level subscript

    ^Package.ClassD(1)=$LISTBUILD("data 1")

          ^Package.ClassD(1,"Child",1)=$LISTBUILD("Child 1")

        ^Package.ClassD(1,"Child",2)=$LISTBUILD("Child 2")

      ^Package.ClassD(2)=$LISTBUILD("data 2)

        ^Package.ClassD(12"Child",4)=$LISTBUILD("Child 4")

      ^Package.ClassD(3)=$LISTBUILD("data 3")

      ^Package.ClassD(4)=$LISTBUILD("data 4")

        ^Package.ClassD(4,"Child",3)=$LISTBUILD("Child 3")

Now add a second Parent Child relations where the child class is the parent so we have 3 subscript levels

    ^Package.ClassD(1)=$LISTBUILD("data 1")

         ^Package.ClassD(1,"Child",1)=$LISTBUILD("Child 1")

              ^Package.ClassD(1,"Child",1, "Grand",3)=$LISTBUILD("Grand 3")

        ^Package.ClassD(1,"Child",2)=$LISTBUILD("Child 2")

              ^Package.ClassD(1,"Child",2, "Grand",1)=$LISTBUILD("Grand 1")

              ^Package.ClassD(1,"Child",2, "Grand",2)=$LISTBUILD("Grand 2")

      ^Package.ClassD(2)=$LISTBUILD("data 2)

        ^Package.ClassD(12"Child",4)=$LISTBUILD("Child 4")

      ^Package.ClassD(3)=$LISTBUILD("data 3")

      ^Package.ClassD(4)=$LISTBUILD("data 4")

        ^Package.ClassD(4,"Child",3)=$LISTBUILD("Child 3")

It should be easy to imagine this global becoming very large storing all the different children at all the different levels.  This increases the cost of getting the data for 1 class into memory because we need to load all the above data into memory just to get the 4 rows from the top level parent table.  This increased IO is the second issue with Parent Child Relationships.

When you define a Foreign Key between 2 classes the storage structure is not changed.  Both classes by default will have a positive Integer as the IDKey so they will both support Bitmaps.  They will each have their own D global so you will only need to load the data for the "Child" table when it is needed.  

If you go with the Foreign Key instead of the Parent Child make sure you define the FKey to use Cascade on Delete and you need to define an index in the "Child" class on the reference to the "Parent" class.

OK so that was longer than I thought it would be.  Hope it makes sense.  If you have more questions i will try to answer them faster this time.

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

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