Problems with Data Table and Collection

[marcusbarrozo]

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.

[slangley]

Hi @marcusbarrozo.

Some questions:

• How is the server configured?  Ram, CPU, etc.
• Is it consistently slow?  Or was it only slow on the initial query run?Ra
• When you say it takes "so long," how long is it actually taking?
• When you run the service outside of the mashup, how long is it taking?

Regards.

--Sharon

[marcusbarrozo]

Your answers:

• How is the server configured?  Ram, CPU, etc.
  • About this, what I know is it has 6 cores and 16 Gb of RAM, reaching 22 Gb. The performance in other mashups is quite satisfactory.
• Is it consistently slow?  Or was it only slow on the initial query run?Ra
  • Especially in the first two queries. Then it stabilizes.
• When you say it takes "so long," how long is it actually taking?
  • 10 to 12 seconds.
• When you run the service outside of the mashup, how long is it taking?
  • The same time as above.

Regards.

Marcus

[slangley]

Hi @marcusbarrozo.

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.

Regards.

--Sharon

[marcusbarrozo]

Hi Sharon,

Your questions:

- 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. 

Tks,

Marcus

[Tudor]

Hello Marcus,

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:

https://www.ptc.com/en/support/article/CS261063

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.

[marcusbarrozo]

Hi Tudor,

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.

[Tudor · Accepted solution]

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.

[slangley]

Hi @marcusbarrozo.

If one of the previous responses allowed you to resolve your issue, please mark the appropriate one as the Accepted Solution for the benefit of others with similar issues.

Thank you for your participation in our community!

Regards.

--Sharon

[slangley]

Hi @marcusbarrozo.

I have marked the Accepted Solution for this post.  If you disagree that it is the solution, please let me know.

Regards.

--Sharon