Hmm, that's not what I would have expected. I'm going to ask around and see if there's something I'm missing here, but in the meantime can you try running TUNE TABLE on this table and see if that changes the behavior/query plans at all? https://docs.intersystems.com/iris20253/csp/docbook/DocBook.UI.Page.cls?...

Comparing the two, it does look like the slower query is performing a full table scan for some reason, which is very odd. What type of index is row_status_idx? If it is not a bitmap index, could you try adding an additional bitmap index on the field (or changing the type of the index to bitmap)?

Thanks for checking that! Is this the query plan for the slow or fast version of the query, or are the query plans identical between them?

A couple questions:

1. Do you have an index on row_status? If not, then filtering on that with values that don't exist in the table likely results in a full table scan, though it is odd that the third query would be faster if this is the case.

2. Can you show us the query plans of the four queries? (see https://docs.intersystems.com/iris20253/csp/docbook/Doc.View.cls?KEY=GSQ... ) This might help us figure out what is going wrong.

Glad it worked out!

I would recommend reporting this on the docs feedback page: https://docs.intersystems.com/irislatest/csp/docbook/Doc.Feedback.cls 

In my experience they are very responsive to this kind of feedback

One way to do this would be to make use of the 'iris merge' command with a CPF file: https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls... 

The CPF file in question can run $SYSTEM.Obj.Load as an Action, for example:

[Actions]
Execute:Namespace="USER",ClassName=%SYSTEM.OBJ,MethodName="Load",Arg1="/home/irisowner/dev/cls/TestClass.xml",Arg2="ck"

So the steps here would be:

1. Define a CPF file that loads the classes you need

2. Create a unix script that runs 'iris merge <instance name> <CPF file location>'

Hi Jerry,

Without knowing more about the specific query plan and IRIS setup it would be hard to know what's going on - I would highly recommend reaching out to the WRC about this if you are able. I will note that TUNE TABLE has had significant functionality improvements since IRIS version 2021.2 (see https://community.intersystems.com/post/20212-sql-feature-spotlight-smar...) , it's possible you are missing a bug fix; again WRC would be the best point of contact. Best of luck!

Great breakdown - just adding that in the terms of the documentation, the "right side" of the && operator in this case is "1". The = sign and everything after it is part of another operation and is not considered in this context.

For defining a fixed length string field in a class definition, you can use the MINLEN and MAXLEN parameters in tandem: https://docs.intersystems.com/iris20231/csp/documatic/%25CSP.Documatic.c...

This will not pad the string automatically, however, you would need to pad it to the desired length before saving an instance of the class.

To retrieve the MAXLEN, this answer to an earlier question may be helpful : https://community.intersystems.com/post/getting-parameters-cleanly-prope...

So the call might look something like:

$$$defMemberArrayGet(class,$$$cCLASSproperty,prop,$$$cPROPparameter,"MAXLEN")

EDIT: These macros aren't available on recent versions, here's an object-oriented way to do it (that's cleaner anyway):
 

I've confirmed that this works on a 2024.1.1 instance with the following steps:

set cdef = ##class(%Dictionary.ClassDefinition).%OpenId("<class name>")
Set count = cdef.Properties.Count()
for i = 1:1:count {if cdef.Properties.GetAt(i).Name = "<Property Name>" {w cdef.Properties.GetAt(i).Parameters.GetAt("MAXLEN")}}

Thirding this - I have been getting the same error but on a Postgres database in my case. I wasn't able to find a workaround other than selecting my column as an integer, which obviously isn't an option in Don's case.

I don't think you actually need VS Code to do this - you should be able to call the routine using RunLegacyTask, which lets you write code as part of the task instance definition.

One way to accomplish what you are describing is to use a temporary global: https://docs.intersystems.com/iris20231/csp/docbook/Doc.View.cls?KEY=GGB...

This allows you to store data in the IRISTEMP database that you can then retrieve like any other global. For example, if I were debugging a piece of code, I could write the following into the relevant Method:

SET ^CacheTempNP($INCREMENT(^CacheTempNP)) = <some debug value>

Then later ZWRITE ^CacheTempNP to see what I have logged.

(Note: In the documentation, we recommend using a global mapping to set up your own naming convention for temporary globals instead of using the ^CacheTemp prefix. While I still recommend doing that if you plan to use temporary globals in non-testing code, just using ^CacheTemp instead is fine for debugging.)