Nice clear description Benjamin.  This should be a big help for query performance.  It is such a common problem in support when we have to help customers deal with outliers.  Now the RTPC should do that work for them!

This looks like it is just a bug.  I reproduced this in InterSystems IRIS latest.  Looking at the generated code we are able to resolve the the index global in other places, but not in this one case.

I will report the bug to development to get it fixed.

My question is what do you plan to use this index for?

What are the datatypes for the Properties in the SELECT list?  This error is coming from VB and most likely is caused by the data not matching up with the datatype def. of the Property.  Doing a quick google search for this error the first three hits were related to Streams.

When you run a query in the portal we do not do any type validation so if you have a MAXLEN on a %String set we are not going to check that, we assume it passed on data input.  External tools might be typed and can fall over if a string is longer than we said it was.

Limiting the fields in the SELECT list to figure out what data type is causing the problem is step 1.  Step 2 would be limiting the rows to figure out which one is producing the error and then looking at that data to see why it might be causing the problem.

I guess Should have read the last post.

Cache SQL Storage does not work with inheritance.

If you use the example I have and instead of having the first subscript level loop over different values you could set the value there.  You could try using it with a class parameter, I have not tried that but it should work.

Arto

You can have one class look at multiple globals by defining the first subscript level to be of type Global and the global name for the map would be *

I have an example of this in the Mapping zip file that is part of the SQL storage series I did a couple of years back.  Mapping.TwoGlobals.cls

In that class, I have 2 globals:  Member and Provider.

/// 
/// ^Member(1,"08/24/2005 11:39",4)=One Memorial Drive
///                              5)=3rd Floor
///                              7)=Cambridge
///                              8)=MA
///                              9)=02142
///  
/// Array: ^Provider
/// ^Provider(1,"08/24/2005 11:43",10)=
///                                11)=15 Cliff Dr
///                                12)=
///                                13)=
///                                14)=Plainville
///                                15)=MA
///                                16)=02762

Theglobal and first subscript looks like:

<Global>^*</Global>
<Subscript name="1">
<AccessType>Global</AccessType>
<Expression>{GloType}</Expression>
<NextCode> i {L1}="" s {L1}="Member" QUIT
 i {L1}="Member" s {L1}="Provider" QUIT
 s {L1}=""
 </NextCode>
</Subscript>

The class has an extra twist in that the two globals are not exactly the same so the fourth subscript level needs to deal with the data being in different places.

you can get all my mapping examples from here

Not sure if you contacted the WRC or not, but if not here is what I would try.

1)  Can you use a different ODBC tool?  Our fav in support is WinSQL.  If this works then we know we are dealing with an ADO issue, if it fails then most likely we have an ODBC bug.

2)  Turn on the ODBC client logging. If you are using the ODBC Data Source Manager then there is a checkbox ODBC logging.  You will need to stop and start your ADO client to pick up the flag, reproduce the problem and then look for the irisODBC.log file in C:\Users\Public\Logs

Ken

We have removed our old FTP server.  The best way to get an ODBC Drive is to contact InterSystems Support:  Phone 1 617 621 0700 or Email Support@InterSystems.com

As for the error you are getting, older versions of Cache did have issues with a long-lasting (days) connection getting into a state where it would no longer work and reconnecting would be needed.

Neerav

When the optimizer is looking at a query it does not look at the parameter values, in fact, they are removed before the query gets to the optimizer, so it sees:  WHERE Tad1.Id  > ?  Because the value is removed the optimizer can only guess at how restrictive the condition is, and it is an optimist so it assumes it will be restrictive.   That is why the Relative Cost changed so much.  The optimizer thinks this query will return only a small number of rows.

Range conditions that are not restrictive can cause performance problems if there is an index on the property involved.  Since the optimizer thinks the condition is restrictive it might choose to use the index and end up doing a table scan.  If you have a range condition that you know is NOT restrictive you might want to put the %NOINDEX hint in front of the condition so the optimizer knows not to use it.

WHERE %NOINDEX Tad1.Id > ?

Hello

I was able to get this to work.  I think the only chagne I made was to add the SQLName to the query

Here is my class with your code

Class DC.ProcClass [ Abstract ]
{Query ClosedFutures(FromDate As %Date, ToDate As %Date) As %Query(ROWSPEC = "FundCode:%String, Asset:%String, TradeDate:%Date,BuySell:%String, Price:%Numeric, Quantity:%Numeric, CumulativePosition:%Numeric, PreviousCumulativePosition:%Numeric, ClosingTrades:%Integer, ClosingTradesInPeriod:%Integer, ClosedTradeValueInPeriod:%Numeric, NetQuantityInPeriod:%Integer, WeightedClosedAverageTradePriceInPeriod:%Numeric, InitialTradeDate:%Date, InitialTrade:%Integer, LastInPeriod:%Integer") [ SqlName = ClosedFuturesProc, SqlProc ]
{
}ClassMethod ClosedFuturesExecute(ByRef qHandle As %Binary, FromDate As %Date, ToDate As %Date) As %Status
{
// Contains complex implementation including setting ^||justClosedTrades
}ClassMethod ClosedFuturesClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = ClosedFuturesExecute ]
{
kill ^||justClosedTrades
Quit $$$OK
}ClassMethod ClosedFuturesFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = ClosedFuturesExecute ]
{
set nextid=$order(^||justClosedTrades(qHandle),1,rec)
if $length(nextid) { set Row=$listbuild($piece(rec,"~",1),$piece(rec,"~",2),$piece(rec,"~",3),$piece(rec,"~",4),$piece(rec,"~",5),$piece(rec,"~",6),$piece(rec,"~",7),$piece(rec,"~",8),$piece(rec,"~",9),$piece(rec,"~",10),$piece(rec,"~",11),$piece(rec,"~",12),$piece(rec,"~",13),$piece(rec,"~",14), $piece(rec,"~",15), $piece(rec,"~",16)),qHandle=nextid }
else { set AtEnd=1,Row="" }Quit $$$OK
}}

and here is the view using my schema and SQLName

Class DC.NewClass1 [ ClassType = view, ViewQuery = {
SELECT * FROM DC.ClosedFuturesProc(65257,65286) --'2019-09-01','2019-09-30'
} ]
{Parameter READONLY = 1;}

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.

Flávio

Could you please include the query that is getting this error?

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.