Best practices on DataTable Query Services

[Sathishkumar_C]

We are having a following services to get/search/query the data table data. Which service is more efficient?

1. GetDataTableEntries
2. SearchDataTableEntries
3. QueryDataTableEntries

[kazunari]

Hi, here is my understanding.

GetDataTableEntries : Just retrieve rows based on maxItems which you set on input. It should be the fastest because no query or expression are defined.

SearchDataTableEntries : You can set a searchExpression and this parameter will work on partial match search against Key columns. This should be the second fastest.

QueryDataTableEntries : This service can set a query and a maxItems in input parameters. Query will work after retrieving the rows so it should be slow than the others when query is defined.

However, I cannot decide which is more efficient because it depends on your use case.

I hope this helps you.

[JT_OU812]

Generally, when doing database queries, adding where conditions will improve performance. In ThingWorx you are claiming that returning all rows is better performance than adding search conditions, has this been verified?

[VladimirRosu]

The trick is to remember that a pure "QueryX" kind of service in order to query the data source in a database-agnostic fashion, will retrieve first all rows (depending on maxItems) then search the infotable in-memory (ThingWorx memory) based on the query object.

This approach simplifies the work we must do whenever we support a new PersistenceProvider (like H2, PostgreSQL, Azure, MSSQL etc.) because the form in which we do the query is just one, no longer depending on the DB-specific SQL syntax.

However, the disadvantage is that we can no longer rely on the optimizations that each DB engine does to make such queries fast (so TW memory consumption might spike in such a scenario).

Overall a good tradeoff, considering DataTables are not supposed to be used for large quantities of data. In these cases anyway we suggest working directly with SQL tables, via the Database Thing (or its more recent cousin, the SQL Thing) that provides the capability to directly send SQL statements to the database.