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

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

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.

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.