Indexing of Infotable stored in DataTable

[MG_2602]

Hi,

We are storing info table data in datatable. Is there any way to add indexing in info table columns storing in Datatable?

Referring to below article:

https://support.ptc.com/help/thingworx/platform/r9/en/index.html#page/ThingWorx/Help/Composer/Things/ThingProperties/indexed_properties.html#wwID0ER5EV

Thanks,

Meghna

[nmilleson]

@MG_2602 ,

Unfortunately, there's not a way to index the actual info table columns due to the way that data is stored.  In the background, all of your datatable data goes into one database table.  There are some metadata columns which can be indexed, but the actual data is lumped into a JSON string in another column.  If you really need to index some of those data columns, I would suggest moving that data to a traditional database table and then using a DataBaseThing template to access it.  

Nick

[MG_2602]

Hi @nmilleson ,

I tried to find out metadata column. Can you please give me some links referred to them?
Also, Can we use configuration tables in datatable? Does they provide indexes?

https://support.ptc.com/help/thingworx_hc/thingworx_8_hc/en/index.html#page/ThingWorx/Help/Composer/ConfigurationTables.html

Thanks,

Meghna

[nmilleson]

@MG_2602 ,

I'm not sure there's a support article that details out the ThingWorx model/data schema.  To see the metadata (and data) columns, open up an instance of pgAdmin (or SSMS depending on your database server) and connect to the thingworx database.  Scroll to the 'datatable' table and view the properties.  You'll be able to see all the metadata columns as well as how the data is stored.

In regards to your second question about Configuration Tables, those are meant to store instance-specific configuration properties for an entity.  I'm not sure what you'd need to index on those.

Nick

[VladimirRosu]

Hi @MG_2602,

I see your use-case is to store Infotable data in datatables, practically a table in each row if I get you right.

I'm not sure however, could you confirm this is the use-case? It's a bit strange and haven't heard requirements for indexing of infotable cells effectively until now.

I want to clarify that you can add Indexes for columns used in a DataTable, and they will be used by the service FindDataTableEntries (check this Help Center page for more details). However, I doubt adding indexes for tables themselves will be very performant.

However, at the end of the day, due to the massive improvements available in a normal SQL table, if you require good SQL-like performance for table data, I also suggest as Nick storing data in an SQL table.

Configuration Tables are not index-able and they are meant for static information.

Also, database-level ThingWorx schema is not documented due to the fact it's only used by the platform internally. We do not guarantee the same internal database structure throughout ThingWorx versions and the official way to retrieve such data should be through the existing platform Services.

[MG_2602]

HI @VladimirRosu ,

We are storing CSV files (File1.csv and File2.csv) from mashup to datatable with combine data nearby 1,00,000 rows having primary key as ID(increases by 1) and Timestamp. When we try to upload new data for File1.csv, we are deleting the previous data and uploads new data, which results us in indexing issue in DataTable.

Thanks,

Meghna

[VladimirRosu · Accepted solution]

Yeah, for 1 mil rows I definitely suggest having a normal SQL table (and managing the delete/add via SQL commands).

[MG_2602]

Hi @VladimirRosu ,

Sorry, I was talking about 100,000 rows.

Also, is there anyway other than databaseThing that we can store this data in thingworx.

[pshashipreetham]

Hi @VladimirRosu ,

Had a discussion with @MG_2602 , they are storing a 0.1 Million of data in the DataTable, when trying to fetch it, it takes around 4 mins to get the data, since client is unhappy on that,  therefore they are trying to index somehow in TWX to fetch faster. 

But @VladimirRosu , as you suggested they tried through the SQL also, but it gave again same 4 mins to fetch the data, does this mean the Ram of DB should be higher or something like that ? 

Thanks,
Shashi.

[VladimirRosu]

Hi @pshashipreetham 

Always, and really always when you diagnose things like "an action takes too much time", you need to go in detail. and understand what takes too much time.

In your case, you said that fetching 100.000 rows via SQL takes 4 minutes, but then I will ask, what exactly takes 4 minutes?

1. The transfer of data between SQL and ThingWorx server? (you can diagnose this time directly in a service by calculating the time between the start and the service end)
2. The transfer of data between ThingWorx and an User Mashup? (this can be diagnosed with the browser Developer Tools, Network tab, looking at the service which gets data back to mashup)

[nmilleson]

@MG_2602 ,

One thing you could do is use the "source" column to indicate which CSV file your data is coming through.  Then you could index the "source_id" column on your database.

me.AddDataTableEntries({
	tags: undefined /* TAGS */,
	location: undefined /* LOCATION */,
	source: myFileName /* STRING */,
	sourceType: undefined /* STRING */,
	values: values /* INFOTABLE */
});

In your database, you would index:

Then in your query you can specify the source that you want to query on:

me.QueryDataTableEntries({
	values: undefined /* INFOTABLE */,
	maxItems: undefined /* NUMBER */,
	tags: undefined /* TAGS */,
	source:  myFileName /* STRING */,
	query: query /* QUERY */
});

Nick