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. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
