Query for static data on Database

Hi, thanks for response

I was researching after seeing this post https://community.appian.com/discussions/f/data/22641/record-vs-cdt-performance-comparison where finally he said query entity is more fast than "queryRecordType".

I did performance tests with "queryRecordType" and "queryEntity"

My first test was to do a simple query to a table with cases in which I retrieve 100 records.

the test with queryRecordType: in this case I have a synchronize records.

the same test with "queryEntity" 

Query entity has better performance than query record for simple query

My question: Is better  "query entity" than "query record" for static data in database?

Hi, thanks for response

I was researching after seeing this post https://community.appian.com/discussions/f/data/22641/record-vs-cdt-performance-comparison where finally he said query entity is more fast than "queryRecordType".

I did performance tests with "queryRecordType" and "queryEntity"

My first test was to do a simple query to a table with cases in which I retrieve 100 records.

the test with queryRecordType: in this case I have a synchronize records.

the same test with "queryEntity" 

Query entity has better performance than query record for simple query

My question: Is better  "query entity" than "query record" for static data in database?

Sorry, but I need to repeat my first response. Records are the future, but depending on your specific query and Appian version, you might see faster or slower query times. Appian will make sure that Records become faster over time.

But, I would like to know what you mean with static data. Is this data you need to query in most/many situations? If the volume is small, did you consider to use the textCache plugin?

HI,

The data are static/parametric tables like cities, countries, ,directions, ...

I recommend to go with Records and queryRecordType. This way you can define the whole data model using relationships. This makes many thing so much easier.

Same answer as before.

Technically speaking if you create a synced Record for your reference data values, you can create relations from your Record to your reference data Record which allow you to get all the information in one single query instead of multiple queries (1 for the Record and 1 for the Reference Data) and that will be faster.

Furthermore, I'm pretty sure that if you run that query multiple times, you will get similar response times in the end.

Peter Lewis could you please clarify why it takes less time when querying by DB if I understood that a record when synchronized has the data in cached and this made it faster. That understanding Jesus test was made with the same data, only one table, without relationships.
In the other hand I know that records are the future, and with this kind of object I can have easily all my data in one place, the power of actions and all of it, but I need to understand the records' performance in time of response

You can already read Peter's response here: https://community.appian.com/discussions/f/data/22641/record-vs-cdt-performance-comparison

On my part, I just did a bit of testing and it is true that it was slower on 2 occasions:

• If you are getting data from a table with no filters. queryRecordType was 3x slower than queryEntity. While this is signficant, I can't think of many real world scenarios where you would be doing these types of queries.
• If you are querying a table with no related data. Was 2x slower with no related data but as soon as I added a relationship, the RecordType was 2x faster than doing the 2 seperate queries. I would assume that the difference grows in favor of the Record Type as you add related fields to your query.

Thanks for the tag! In general my response on the linked post still stands. Keep in mind that there are a TON of factors that can influence query performance, so it can be hard to do 1:1 comparisons. Things that can influence the performance include things like:

• Volume of data (rows and columns) in the source and in the query
• VPN connections / overhead / latency to access the source
• Complexity of query (filters, related data, custom fields, record-level security, etc)
• Whether the data is aggregated or not
• What the load is on the system at the time of the query

Because of that, I can't give a blanket statement that every query will be faster with a!queryRecordType(). However, in general I think you will see better overall outcomes with a!queryRecordType() for a few reasons:

1. Our query performance optimizations specifically focus on more complex queries, where you're joining data from multiple sources or performing complex filtering / aggregation. So like Mathieu said, you're more likely to see better performance with more complex queries.
2. The data is cached, which eliminates overhead between Appian and the source in scenarios where there is latency to the original data source. Plus, it eliminates issues from querying from multiple types.
3. And of course the other key benefit is that the design experience in Appian is easier than needing to do performance tuning on your source system. An accomplished Database Administrator could likely set up indexes or tune the database to have good performance, but with Appian Records you don't have to!

For what it's worth, we have done performance testing internally across a number of real use cases like reporting dashboards that test queries against synced records and have found better performance in aggregate.

Not only that, as part of our program we plan to continue to release optimizations for performance, so you'll only see the performance continue to get better!