Skip to main content
Sathishkumar_C17-Peridot
17-Peridot
Apr 8, 2022 3:35 AM UTC
Question

Best practices on DataTable Query Services

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

 

3 replies

kazunari12-Amethyst
12-Amethyst
Apr 12, 2022 2:26 AM UTC

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.

Translate
    JT_OU81211-Garnet
    11-Garnet
    Sep 28, 2023 2:52 PM UTC

    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?

    Translate
    VladimirRosu_11662719-Tanzanite
    19-Tanzanite
    Sep 29, 2023 12:20 PM UTC

    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.

    Translate
      Terms & ConditionsAccessibility statement