There have been some conflicting info in the answers to this question so I am going to try and put all the correct info into one place.

Pluses for Process Private Globals:

  1) no size limit

  2) cleaned up by the system when the process exits

  3) explicit KILL will clean up PPG

  4) can be used in a class with Cache SQL Storage

Pluses for % Array:

  1) faster than PPG

  2)  cleaned up by the system when the process exits

  3) explicit KILL will clean up % array

  4) argumentless KILL will clean up % array

  5) Can be used in the NEW command

  6) can be used in a class with Cache SQL Storage

Negatives for Process Private Globals

  1) slower than % Arrays

Negatives for % Arrays:

  1) limited in size by the process memory

I hope this list is close to complete and accurate.

Brendan

the format of an Object Id is $LISTBUILD(id,class) so when I delete a row from Sample.Person I get:

SAMPLES>zw oid
oid=$lb("4","Sample.Person")

The Id is the first $List element:

SAMPLES>w $LISTGET(oid,1)
4

The ORDER BY needs to go with the TOP, so your query should be

SELECT Name,%VID,ID,Age FROM
   (SELECT TOP 10 * FROM Sample.Person ORDER BY Name) 

then it does what you want.

Scott

This is a bug that ISC Development needs to look at.  I spoke to the developer about it, he understands what is going wrong and will look at options to fix this.

The problem is the format of the data in the index does not match the format of %TimeStamp.  This Property is defined as a %TimeStamp but we are only storing the date part in the index.   If you modify you query to something like this then it should return the correct answer:

SELECT MAX(IndexedDate)
FROM HS_IHE_ATNA_Repository.Aggregation
WHERE DATEPART(sqltimestamp,IndexedDate) < '2016-11-28'

If you would like to track the Prodlog you can open an WRC issue and tell the advisor to come and talk to me about this.

Mark

Running TuneTable is safe to do at any time.  It will be CPU intensive so you might not want to run it at peak workload times, other than that it is fine to run at any time.

It is important that all your tables have this info so it is great that you are running it on all your tables.

The first question for any SQL performance issue is:  Have you run TuneTable?

It is very important to have correct values for ExtentSize, Selectivity and Block Count, whether the info comes from a developer manually entering it or by running TuneTable.  With out these values the Query Optimizer can only guess what the right plan might be.

The next step is to provide proper indices to support the queries you are going to write.  Without have a lot more info it is impossible to tell you what those might be or what type of index would be best (bitmap or standard).

Jiri

You have a couple of options:  I created an index in Sample.Person that is the same as yours and then look at this query

SELECT Name,FavoriteColors FROM Sample.Person
    WHERE FOR SOME %ELEMENT(FavoriteColors) (%Value = 'Red')

It uses the index and should give very good performance.

• Read index map Sample.Person.FavColors, using the given %SQLUPPER(Subvalue(FavoriteColors)), and looping on ID.


• For each row:
•  Read master map Sample.Person.IDKEY, using the given idkey value.
   Output the row.

Have a look at the docs for info on Indexing Collections

The above is a little different as it is a List instead of an Array, but it should still work.

If you want better performance out of the Child Table created for the array you need to define the index to be on both the Element and the Key.  I think if you look in the Management Portal at the maps /  indices for your table you will see the one you defined is not projected in the child table.

I added the following Property and index:

Property Kids As array Of %String;

Index KidIndex On (Kids(ELEMENTS), Kids(KEYS));

and for this query:

SELECT id FROM Sample.Person_Kids WHERE Kids = 'Kieran'

I get a plan that uses the index.

• Read index map Sample.Person_Kids.KidIndex, using the given %SQLUPPER(Kids), and looping on element_key and Person.


• For each row:
•  Output the row.

Either way should give you good performance.

hope this helps.

Brendan

In 2012.2 and up you can use SQL Runtime Stats to gather the above info plus Global Refs and Lines of Code for every query run.  This has more overhead and take up more disk space so please remember to turn this off after you gather the needed info.

If you have questions after reviewing the docs I would be happy to answer them:  Brendan@interSystems.com

Matt is correct Bock Count has been part of the SQL  Optimizer from the very beginning when we had 2K database blocks.  

In the past this number was an estimate.  Now it is calculated as part of TuneTable.  We did not want to change all our internal structures so we just count the number of 8K blocks on disk and divide by 4.

I am not a fan of this behavior but the LogicalToOdbc method does not convert the ValueList to DisplayList like the LogicalToDisplay method does.

You have 2 Options:

1) you can use %External(FieldName) in the  query, that will give you the DislayList value

2) you can write your own LogicalToOdbc method that does the same as the LogicalToDisplay does

Man I need to learn how to read :(

So you are asking about COLLATE, not COLLATION.  Let me try again.

In Cache you can change the way data is sorted and other regional things (date and number formats for example) by using the National Language Support (NLS).  By selecting one of the available locales you can control the sort order of your data.

You can configure NLS setting via classes:  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

or from the portal:  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

There is also an article that give some examples of working with NLS:  https://community.intersystems.com/post/collations-cach%C3%A9

I hope this post is a little more in line with what you were looking for.

OK one more try.

When you use DISTINCT or GROUP BY we are going to build a temp global using the Field values as subscripts.  So say we have a query with GROUP BY Name, Title

the table has data that looks like this:

Scott     Customer

Brendan Support

Eduard Customer

Brendan Trouble Maker

To figure out the GROUP BY we will build a global that looks like this:

^||sql.temp(1,"Brendan","Support")=""

^||sql.temp(1,"Brendan","Trouble Maker")=""

^||sql.temp(1,"Eduard","Customer")=""

^sql.temp(1,"Scott","Customer")=""

With this data the longest global node is for that Trouble Maker, 39 total charaters.  This is well under 511 so no subscript error.  Now if I have a Title that is 500 characters long then the total length of the global would be over 511 and we would get a <SUBSCRIPT> error.

The more fields you put into a GROUP BY the easier it will be to get over the 511 charater limit as each field will be a different subscript in a global node.