Record vs CDT. Performance comparison

Question

Hi, 
 I've have a use case where the customer load some data through uploading to Appian an excel sheet. The requirement is implement the load process with two main steps: 
 1) Load excel data to temporary tables, validate data and inform user about the data read, number records to be updated/inserted, errors found if any. 
 2) User gets these information on a form where he can confirm or cancel data load. 
 
 In order to implement step 1, I've written a rule expression that loop on the data saved on temporary tables and make some validations. One of this validatiions is check for the existence of every loaded Id in the current data, in order to know if we have a create a new record or we have to update an existing one. Lets call this rule checkRule, full code follows,: 
 a!localVariables(
 local!cargaUFGs: rule!TGSS_GetDatosCargaByDSE(dse: cons!TGSS_ENT_CARGA_UFG),
 local!idsUFGsActuales: tointeger(index(rule!TGSS_GetUFGS(pagingInfo: null),"idCliente",{})),
 cast(
 'type!{urn:com:appian:types:TGSS}TGSS_Mensaje?list',
 a!forEach(
 local!cargaUFGs,
 {
 if(
 or(isnull(fv!item.descripcion),isnull(fv!item.funcion)),
 'type!{urn:com:appian:types:TGSS}TGSS_Mensaje'(
 tipo: "error",
 entidad: "ufg",
 accion: "",
 texto: "El registro " & fv!item.identificador & " contiene datos nulos"
 ),
 {}
 ),
 if(
 contains(local!idsUFGsActuales,tointeger(fv!item.identificador)),
 'type!{urn:com:appian:types:TGSS}TGSS_Mensaje'(
 tipo: "info",
 entidad: "ufg",
 accion: "update",
 texto: fv!item.identificador 
 ),
 'type!{urn:com:appian:types:TGSS}TGSS_Mensaje'(
 tipo: "info",
 entidad: "ufg",
 accion: "insert",
 texto: fv!item.identificador 
 ) 
 )
 }
 )
 )
) 
 My doubt is about the rule used in the third line above: rule!TGSS_GetUFGs. 
 This rule gets a list of current records of UFG data entity, and I made two versions: one that use queryRecordType to gets the data from UFG record type (the record is based on database table), one that use queryEntity for gets the data from the corresponding UFG data type (the one that is UFG record based on). 
 So, I compare performance of checkRule using queryRecordType and using queryEntity, and what I get is not what I expect. 
 The response time using queryEntity es five times shorter than using queryRecordType. 
 Is that a normal outcome? I was expecting that record performance were faster than CDT, because they are based on a memory database, which is supposed to be faster when your are querying. 
 I appreciate some explanation of that outcome, it is likely that I'm getting something wrong hear, but defintely I do not understand this difference of performance. 
 Regards

Chris · Answer

I would expect a!queryEntity() to perform better here as both this and a!queryRecordType() are retrieving data from your data store, but likely more layers of logic are involved in Appian retrieving from the record type and then retrieving from the data entity, vs straight to the data with a!queryEntity. AFAIK no queries other than process reports and task reports are pulling from "in-memory" objects. 
 Another thought would be to create a view on the DB side that pulls from the temporary/upload table but incudes a "id" column which is a search from the main table, which will be populated for duplicates/updates and empty for new/writes. Then in Appian, you can simply query from the view directly without any other logic.