Sergey

The biggest difference between the 2 queries is the second one is really a JOIN, not a Left Outer Join.  By adding the second WHERE condition on a property in pcu you are making this a JOIN and allowing the Optimizer to start in either table.  I created the tables on my system and here is what I am seeing

The first query starts in ps using the item index and then joins to the pcu table using a temp file built from the master map (same as the plan you show)

The second query is starting with the pcu table and the doing a join to the ps table using a temp file build from the Item index and the master map.

Why don't we use the Item index in pcu?  I can't tell you exactly why, but I know the 2 biggest factors we are looking at are the size of the maps on disk and the Selectivity of the properties involved.  In both cases, we are going to need to read the master map so the question is this:  Will reading in the Index map reduce the number of blocks we need to read of the master map to make it worth the effort?  Without proper values generated by TuneTable, or supplied by you, the optimizer is left to guess at what these values are.  In the first case, it has decided that reading the index will not reduce the number of block reads of the master map so it is not using it.  This generally happens when we think the Master Map is very small.

To take this any further I would need you to run TuneTable on both of these tables and see if the poor plan is still used.  If it is still used please provide the storage info for both classes so I can investigate this.

Hi Kurt

Sorry about that.  I have corrected the issue with downloading studio.

Please let us know if you run into anything else.

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.

Keith

Here is a simple example I came up with that can show you the 2 different ways to do this.  As the comments in the class state the restriction for Mapping.OneClassParentChild is every parent must have at least 1 child as the IDKey for the rows is the Child row.  If you need to support parents without children this will not work.  I will need to think about how we might be able to do that if that is needed.  I would also push harder for the 2 classes and a view approach if that is needed.

The Main trick here is to make understand that each subscript level is made up of 3 different parts that you can change or reference: 

     1) Data Access Expression and Data Access Variables:  This is the global and subscripts to be used at this level by the Next code, for example, you could have 3 levels that loop over 1 global and then at level 4 switch to a different global and only use 2 of the subscript values....  Data Access Variables are variables you can set at a given level before the Next code is executed.  You can use them at this level or any higher level and you can set them to values from lower levels.

     2) Next Code:  This is the code to get from one value row to the next in a table.  This could be the default $ORDER() code that is generated for a simple Subscript, or it could be override code you provide.

     3) Invalid Conditions:  If you provide Next Code at a given level then you should also provide Invalid Conditions.  This is a test you can use to see if you have a valid row when the subscript value is provided as a constant instead of needing to loop to find the next one.

For your class I reference the Data Access Expression at Subscript Level 3 {3D} in the data mapping.  This would be the global with the subscripts defined from the 2 lower Subscript levels:  ^Mapping({L1},{L2})

OK so that was a lot of details you did not ask for, but just in case someone wanted to understand all the gory details I figured I would write them down.

This xml file has the different classes I created:  Examples

Hi Keith

You can map anything!

the question is do you really want to and is it the best thing to do?

This looks like a classic Parent Child relationship that would be defined by 2 classes.  You could then create a view that would JOIN the 2 tables together and expose that view via ODBC and you would have what you want.

I can create a simple example that will show this data in one class, but I don't think it will add much value.

Giray

Your code does not match your class descriptions.  In your code you are using a property, Parent to do the child lookup, but there is no parent property defined in Example.Child.

If you truly want a Many to Many then you can't define a Parent property in the child, unless it is a list, and I am not recommending that!

I am not a fan of Lists of Objects, but this might not be too bad.  I think I would still rather go with an array of objects as that would give you a third table and line up with the classic Many to Many format using 3 tables.  If you want to stay with a List then I would suggest that you pass the List of Child IDs into the QueryChildren method.  You can then use the list of Object IDs to get each child via SQL or Objects.

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.