Just to make it as simple as possible as I am guessing you are new to Caché

ClassMethod Export(pGlobal, pFile)
{
    set file=##class(%File).%New(pFile)
    do file.Open("WN")
    set key=$order(^Global1(""))
    while key'="" {
        do file.WriteLine(^Global1key))
        set key=$order(^Global1(key))
    }
    do file.%Save()
}

$ORDER() is a command that returns the next defined subscript value.  When you pass in "" it returns the first defined subscript value, 1 in your case.   After writing the value of ^Global1(n) to the file the final $ORDER() will return "" indicating there are no more defined subscript values.

for more details see our docs for $ORDER()

Susobhan

The example you have above should have no problem handling the number of rows assuming the Business Logic is not building some huge thing in memory.

%Library.ResultSet has been replaced by %SQL.Statement.  We would expect %SQL.Statement to give you better performance then %Library.ResultSet.

To increase performance a little more Embedded SQL gives the best performance of all three options.  In older version of Cache the difference was pretty large.  In current version of Cache the difference is not that great.

Brendan

Hello

Are you storing 64365 or 64365,23587?

If you are storing the first then the above answers are correct.  If you are storing the second then you will need to create your own Data Type class based on %Timestamp and write your own LogicalToStorage and StorageToLogical methods.

Cache has system functions that will convert $H to a readable format and back

USER>W $zdatetime($h)
03/23/2017 06:36:38

USER>W $ZDATETIMEH("03/23/2017 06:36:38")
64365,23798

Brendan

Susobhan

Yes you can use any DML statements over ODBC to create new users and GRANT and REVOKE privs.

Create User:  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

Grant :  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

Revoke:  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

We do not recommend making general use of the _SYSTEM username.  For a secure system you should either disable this account, or change the password and limit it's use.  Then create a new Cache User for each person connecting to the application.  You can first create a Role that has all the privs needed for a general user and then just GRANT that one Role to each User.

If you need more detailed examples on this you can contact InterSysetms Support and we will help you out.

Brendan

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