It will generate a GUID but....

We may need to wrap it into a function and force the call with row dependency as Robert mentioned above.

Thanks.

This also do the job, thanks.

I forgot to mention the client is using a core based license and this method returned zero. Is there other utilities we can use? Thanks.
 

Thanks, I'll see that can be done through it.

Thanks a lot.

Thanks, yes that's what they are doing now

Yep, that will help them. Thanks Eduard.

Hi Stefan,

If I change 

select distinct by (VISIT_SERIAL_NO,HQ_ORG_CODE) VISIT_SERIAL_NO,HQ_ORG_CODE

into select distinct VISIT_SERIAL_NO,HQ_ORG_CODE,

The sum number is quite different

actually the second sql is equal to 

it seems to be caused by the group by clause on a bitmap index, if I remove it, the result is then consistent

I tuned the table and rebuild the indices but the result is the same.

The followings are the query plans

Hi Stefan.

It is from a random search page with 20+ possible conditions to combine. This case is one of the slowest SQL we find. In other cases, with more conditions to act as filters, the performance is OK.

I'll try whether Bitslice index works for this case.

Thanks.

Hi Vitaliy,

Yes Benjamin also find out that the exported DDL is not consistent to the class definition, but users source code is so far controlled by the end user thus we'll need to wait until they can share it with us.

But I'm not sure what happened in your test with the Fill() method as mentioned above.

In the clients env, there are more than 7 million records and the result of count distinct end is 40,000 less than the total number, which means only a very small portion of VISIT_SERIAL_NO,HQ_ORG_CODE is not unique. Thus I used populate facility to generate 6 million data and the result of count distinct is more than 5.99 million. Then the query is slow.

while the distinct by version is much better

Both was run after tune table and rebuild indecies

You can find the class file I used in the test as below, so far I only used too index to handle this case, if any other index is helpful, we may add it back

Class User.STSEARCHVISIT Extends (%Persistent, %Populate) [ ClassType = persistent, DdlAllowed, Final, Owner = {SuperUser}, ProcedureBlock, SqlRowIdPrivate, SqlTableName = ST_SEARCH_VISIT ]
{

Property HQORGCODE As%Library.String(MAXLEN = 32) [ SqlColumnNumber = 2, SqlFieldName = HQ_ORG_CODE ];Property HQORGNAME As%Library.String(MAXLEN = 32) [ SqlColumnNumber = 3, SqlFieldName = HQ_ORG_NAME ];Property TENANTID As%Library.String(MAXLEN = 32) [ SqlColumnNumber = 4, SqlFieldName = TENANT_ID ];Property GROUPPATIENTSN As%Library.String(MAXLEN = 32) [ SqlColumnNumber = 5, SqlFieldName = GROUP_PATIENT_SN ];Property ORGPATIENTSN As%Library.String(MAXLEN = 32) [ SqlColumnNumber = 6, SqlFieldName = ORG_PATIENT_SN ];Property NAME As%Library.String(MAXLEN = 64) [ SqlColumnNumber = 7 ];Property SEXCODE As%Library.String(MAXLEN = 2) [ SqlColumnNumber = 8, SqlFieldName = SEX_CODE ];Property SEXNAME As%Library.String(MAXLEN = 16) [ SqlColumnNumber = 9, SqlFieldName = SEX_NAME ];Property BIRTHDATE As%Library.Date [ SqlColumnNumber = 10, SqlFieldName = BIRTH_DATE ];Property MARRYCODE As%Library.String(MAXLEN = 2) [ SqlColumnNumber = 11, SqlFieldName = MARRY_CODE ];Property MARRYNAME As%Library.String(MAXLEN = 16) [ SqlColumnNumber = 12, SqlFieldName = MARRY_NAME ];Property IDENTIFYTYPECODE As%Library.String(MAXLEN = 5) [ SqlColumnNumber = 13, SqlFieldName = IDENTIFY_TYPE_CODE ];Property IDENTIFYTYPENAME As%Library.String(MAXLEN = 32) [ SqlColumnNumber = 14, SqlFieldName = IDENTIFY_TYPE_NAME ];Property IDENTIFYNUMBER As%Library.String(MAXLEN = 50) [ SqlColumnNumber = 15, SqlFieldName = IDENTIFY_NUMBER ];Property BLOODTYPECODE As%Library.String(MAXLEN = 2) [ SqlColumnNumber = 16, SqlFieldName = BLOOD_TYPE_CODE ];Property BLOODTYPENAME As%Library.String(MAXLEN = 12) [ SqlColumnNumber = 17, SqlFieldName = BLOOD_TYPE_NAME ];Property MOBILE As%Library.String(MAXLEN = 50) [ SqlColumnNumber = 18 ];Property MAILINGADDRESS As%Library.String(MAXLEN = 127) [ SqlColumnNumber = 19, SqlFieldName = MAILING_ADDRESS ];Property VISITSERIALNO As%Library.String(MAXLEN = 64) [ SqlColumnNumber = 20, SqlFieldName = VISIT_SERIAL_NO ];Property TABLEFLAG As%Library.String(MAXLEN = 1) [ SqlColumnNumber = 21, SqlFieldName = TABLE_FLAG ];Property VISITTYPECODE As%Library.String(MAXLEN = 24) [ SqlColumnNumber = 22, SqlFieldName = VISIT_TYPE_CODE ];Property VISITTYPENAME As%Library.String(MAXLEN = 64) [ SqlColumnNumber = 23, SqlFieldName = VISIT_TYPE_NAME ];Property VISITDEPTCODE As%Library.String(MAXLEN = 32) [ SqlColumnNumber = 24, SqlFieldName = VISIT_DEPT_CODE ];Property VISITDEPTNAME As%Library.String(MAXLEN = 64) [ SqlColumnNumber = 25, SqlFieldName = VISIT_DEPT_NAME ];Property INOUTVISITNO As%Library.String(MAXLEN = 32) [ SqlColumnNumber = 26, SqlFieldName = IN_OUT_VISIT_NO ];Property VISITTIME As%Library.TimeStamp [ SqlColumnNumber = 27, SqlFieldName = VISIT_TIME ];Property DISCHARGETIME As%Library.TimeStamp [ SqlColumnNumber = 28, SqlFieldName = DISCHARGE_TIME ];Property INHOSPITALTIME As%Library.TimeStamp [ SqlColumnNumber = 29, SqlFieldName = IN_HOSPITAL_TIME ];Property ICDCODE As%Library.String(MAXLEN = 20) [ SqlColumnNumber = 30, SqlFieldName = ICD_CODE ];Property ICDNAME As%Library.String(MAXLEN = 64) [ SqlColumnNumber = 31, SqlFieldName = ICD_NAME ];Property HEALTHCARDNO As%Library.String(MAXLEN = 32) [ SqlColumnNumber = 32, SqlFieldName = HEALTH_CARD_NO ];Property HEALTHCARDTYPE As%Library.String(MAXLEN = 64) [ SqlColumnNumber = 33, SqlFieldName = HEALTH_CARD_TYPE ];Property SURGCODE As%Library.String(MAXLEN = 20) [ SqlColumnNumber = 34, SqlFieldName = SURG_CODE ];Property SURGNAME As%Library.String(MAXLEN = 64) [ SqlColumnNumber = 35, SqlFieldName = SURG_NAME ];Property AGE As%Library.String(MAXLEN = 10) [ SqlColumnNumber = 36 ];Property DISEASECODE As%Library.Integer(MAXVAL = 2147483647, MINVAL = -2147483648) [ SqlColumnNumber = 37, SqlFieldName = DISEASE_CODE ];Property DISEASENAME As%Library.String(MAXLEN = 64) [ SqlColumnNumber = 38, SqlFieldName = DISEASE_NAME ];Property LastChangedTime As%Library.TimeStamp [ SqlColumnNumber = 39 ];Property LastCreateTime As%Library.TimeStamp [ SqlColumnNumber = 40 ];Property GROUPORG As%Library.String(MAXLEN = 50) [ SqlColumnNumber = 41 ];Parameter USEEXTENTSET = 1;/// Bitmap Extent Index auto-generated by DDL CREATE TABLE statement.  Do not edit the SqlName of this index.
Index DDLBEIndex [ Extent, SqlName = "%%DDLBEIndex", Type = bitmap ];
Index IdxMapOrg On HQORGCODE [ SqlName = IdxMapOrg, Type = index ];
Index IdxVisitorg On (VISITSERIALNO, HQORGCODE) [ SqlName = IdxVisitorg, Type = index ];
}

This can also solve the problem. Thanks.

Thanks Eduard.

It works in this case indeed. While there are still cases for some of our partners to work on XMLs without schema. Is there someway to do it?

Yes I'm using virtual document thus the xml was loaded as an EnsLib.EDI.XML.Document and saved into BPL context.

While the path about seems not working.