Rodolfo, This newsgroup is not an 'official' support mechanism, and while Bill is always helpful, and monitors the list as closely as he can, lack of response should not be considered a reflection of InterSystems support. If you think this is important, you should log a support issue with InterSystems. As an aside, I think there is nothing wrong with the way it is working. An index is only a 'map' to the data, and should not be considered the data itself. The data should always be retrieved from the actual data store. InterSystems has gone beyond this by providing the ability to store some of the data redundantly in the index. This is an option I probably will not use, because I believe the overhead of maintaining the extra data (updates, disk useage increases, index is bigger, backups take longer) far outweigh the meager benefit of not retrieving the data from its normal storage location. Mark
"Rodolfo Concepcion" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Sometime ago, IS would have responded this, but maybe they DO NOT have an > answer at all... :( > > Rodolfo. > > "Mark Sires" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > Rodolfo, > > I don't know, maybe Bill will chime in soon! > > At least we have the tool to show us the query path now. I used to have > > to wade through the query generated code to figure out what was going on. > > Mark > > > > "Rodolfo Concepcion" <[EMAIL PROTECTED]> wrote in message > > news:[EMAIL PROTECTED] > > > I'd now tried the %Exact like you said on the query and also changed the > > > collation back to Exact, I then rebuilt the indices and guess what?... > > > Exactly the wrong result!!!.... :) The Cache query optimizer won't use > the > > > index... > > > > > > Maybe Cache deprecated optimizing Exact queries or maybe it didn't > > optimize > > > them at all, who knows, but I'm sorry it didn't worked the way sou said. > > > > > > Rodolfo. > > > > > > "Mark Sires" <[EMAIL PROTECTED]> wrote in message > > > news:[EMAIL PROTECTED] > > > > Rodolfo, > > > > You probably didn't have the collation on the 'Code' property set to > > > Exact - > > > > if the property collation and the index collation don't match, and a > > > > collation key word isn't included in the query, then the index would > not > > > > give the results asked for in the search so it is ignored. I'm > actually > > > > surprised it is using an index at all since a where or order by clause > > > > weren't in the example you gave. > > > > I used to fight this collation issue all the time in the older FDBMS > > sql, > > > > because it was easily confused by collation directives. As I recall, > to > > > use > > > > and exact index on a property that doesn't have exact collation set, > the > > > > query needs to be something like: > > > > Select %Exact(Code) from mydomain.mytable where %Exact(Code)=....... > > > > Mark > > > > > > > > "Rodolfo Concepcion" <[EMAIL PROTECTED]> wrote in message > > > > news:[EMAIL PROTECTED] > > > > > Well, Mark... be impressed, but by the total opposite result this > > > > > brought!!!... :( > > > > > When the collation is set to Exact, don't know why, the Cache don't > > even > > > > use > > > > > the index!... > > > > > Starts using the IDKEY index instead. > > > > > > > > > > I did put it back to an empty collation (although I could use a > > AlphaUp, > > > > but > > > > > it won't make a difference in this case) and Cache used again my > > > glorious > > > > > index... :( > > > > > > > > > > So, what have I learned?... don't mess with the Exact collation, > > unless > > > > > somebody tell me here or knows how to take advantage of it or what > is > > > the > > > > > purpose of this collation in matters of improving SQL queries. > > > > > > > > > > I would recommend Cache to use the Exact collation to avoid > > duplicating > > > an > > > > > index on the data part of an index, but hey!... that just me > thinking > > > > aloud. > > > > > > > > > > Thxs, Mark. > > > > > > > > > > Rodolfo. > > > > > > > > > > "Mark Sires" <[EMAIL PROTECTED]> wrote in message > > > > > news:[EMAIL PROTECTED] > > > > > > That would be exactly what we would hope for. If it indeed does > > that, > > > > > I'll > > > > > > be very impressed with InterSystems optimizations! > > > > > > :-) > > > > > > Mark > > > > > > > > > > > > "Rodolfo Concepcion" <[EMAIL PROTECTED]> wrote in message > > > > > > news:[EMAIL PROTECTED] > > > > > > > So, Mark... that means that if I try changing the collation > > setting > > > > to > > > > > > > "Exact", Cache' will use that subscript from the index?... > > > > > > > > > > > > > > Thanks for the explanation Mark, is good, very good, to get what > > you > > > > > asked > > > > > > > help for!... :) > > > > > > > > > > > > > > "Mark Sires" <[EMAIL PROTECTED]> wrote in message > > > > > > > news:[EMAIL PROTECTED] > > > > > > > > Rodolfo, > > > > > > > > Depending on the collation setting for the property, the value > > of > > > > > 'Code' > > > > > > > in > > > > > > > > the index subscript may not be the same as the actual value of > > > > 'Code' > > > > > > > > (example, if the collation is SQLUPPER, and the value is > "Mark", > > > > then > > > > > > the > > > > > > > > index subscript would be "MARK", which is not the value 'Code' > > > that > > > > > you > > > > > > > > asked for), it would have to go to the master map, or have the > > > data > > > > > > stored > > > > > > > > in the index (which you did in the second part). > > > > > > > > > > > > > > > > So I'd say this is by design to make sure you get what you > asked > > > > for. > > > > > > > > > > > > > > > > Not bad, very bad, good, very good! > > > > > > > > > > > > > > > > :-) > > > > > > > > Mark > > > > > > > > > > > > > > > > "Rodolfo Concepcion" <[EMAIL PROTECTED]> wrote in message > > > > > > > > news:[EMAIL PROTECTED] > > > > > > > > > Has come to my attention the simple, maybe already said, > fact > > > that > > > > > > Cache > > > > > > > > > does not optimizes the generated code from a SQL querie to > > > classes > > > > > if > > > > > > > you > > > > > > > > > don't explicitly put it on the data on an index. > > > > > > > > > > > > > > > > > > For instance: > > > > > > > > > > > > > > > > > > I have a class with a property Code, so I made an index > based > > on > > > > > this > > > > > > > > > property in order to optimize the queries based in it... but > I > > > > > > realized, > > > > > > > > > that when I wrote a query like this: > > > > > > > > > SELECT Code > > > > > > > > > FROM clsX > > > > > > > > > > > > > > > > > > The Query Plan (on the SQL Manager) told me that it was > gonna > > > read > > > > > > first > > > > > > > > the > > > > > > > > > index I made (which is good!!!), but then it'll read the > > master > > > > map > > > > > > > (which > > > > > > > > > contains the IDKEY) which I presume this is done in order to > > > > obtain > > > > > > the > > > > > > > > > value of Code (which is ALREADY in the index!!!, which is > bad, > > > > very > > > > > > > > bad!!!), > > > > > > > > > so I decided to include AGAIN the same property Code, but in > > the > > > > > data > > > > > > > part > > > > > > > > > of the index and rebuilt it, and Voila!.... no need to go > > > reading > > > > > the > > > > > > > > master > > > > > > > > > map 'cause the Code property was already in the index.... > but > > > > > > > > REPEATED!!!... > > > > > > > > > > > > > > > > > > Is this by design or is it a flaw?... > > > > > > > > > > > > > > > > > > Thxs. > > > > > > > > > > > > > > > > > > Rodolfo. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
