I have a data table with 17 fields and 296 records - figures 1 and 2. To show the records for this data table I have a mashup as shown in figure 3 containing a textbox, two buttonbars and a collection. The service that loads the collection is in figure 4. Basically what this service does is to filter and / or sort my data table.
My questions are:
# 1 - Since the data table has additional indexes in all fields where the collection can be filtered, why does the service take so long to load the collection when I don't use any filters?
# 2 - I can click on only one cell in the collection at a time. If after that I use any filter or change the sort option, how can I make the previously selected cell be selected again, since every time I filter or sort the data table, it redoes the collection?
My version of Thingworx is 8.5.6 and the database is postgresql.
Thanks in advance.
If you run QueryDataTableEntries without any input, how many rows are returned and how long does it take? You can also run GetDataTableEntryCount as well. This will help us confirm if the row count you provided initially was for the whole table or for a specific user.
- If you run QueryDataTableEntries without any input, how many rows are returned and how long does it take?
- QueryDataTableEntries returns 296 rows. Time: 10 - 12 secs;
- GetDataTableEntries same as above;
- You can also run GetDataTableEntryCount as well. This will help us confirm if the row count you provided initially was for the whole table or for a specific user.
- GetDataTableEntryCount returns 296 rows. There is no other user using the data table.
As additional information, the service used to create the data table always deletes the rows that have been created previously, by the same user.
The delay seems to be occurring on the DB querying side rather than the data rendering (mashup) side. The 'indexes' added are going to be in-memory only (not DB-side indexes), and hence they are not as effective as a DB-side service.
There are a few performance-related checks we need to perform. First, ensure a missing index is added to the database itself per instructions available here:
Second, please check the raw number of rows in the data_table itself using a query such as the following:
select entity_id,count(*) from data_table group by entity_id;
Depending on the output returned above, there may be some additional cleanup required.
Thank you for your attention to my case.
As I reported in the previous email, the problem occurs after the creation of the "data table". Once the records are created in the "data table" the "mashup" is only classifying and ordering these records according to the user's choice and showing in a collection.
The first two classifications are very slow - 10 to 12 seconds - compared to the time of the others - 2 seconds.
I don't know if it's a service problem - the "infotable" generated has 17 fields or if it's a "collection widget" problem - when I change the View property of the collection from "flow" to "table" I notice a small performance improvement.
As for your instructions, both "missing index" and "raw number of rows in the data_table" are ok for me.
I still don't know how to resolve this issue.
Thank you again.
Thank you for the response, Marcus. There are three things we can attempt to isolate the issue:
1. In the service code, add debug timing around the data table lookup such as:
var currentTime = new Date();
// Data Table Query code
var endTime = new Date();
var diff = (currentTime.getTime() - endTime.getTime()) / 1000 / 60;
logger.error("+++ delay: " + diff + " seconds ");
2. In the Mashup, load developer tools, go to the network tab, and capture a .har file per https://www.ptc.com/en/support/article/CS224691
3. In the Mashup, repeat the test above, but record the performance in the performance tab
This will help us determine if the delay is in fetching the data, processing the service, or rendering.