Skip to main content
Best answer by VladimirRosu_116627

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

2 replies

N
nmilleson17-Peridot
17-Peridot
July 21, 2022

@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

    M
    MG_26021-VisitorAuthor
    1-Visitor
    July 25, 2022

    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

    N
    nmilleson17-Peridot
    17-Peridot
    July 25, 2022

    @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

      V
      VladimirRosu_11662719-Tanzanite
      19-Tanzanite
      July 25, 2022

      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.

        M
        MG_26021-VisitorAuthor
        1-Visitor
        July 26, 2022

        HI @VladimirRosu_116627 ,

         

        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

          V
          VladimirRosu_11662719-TanzaniteAnswer
          19-Tanzanite
          July 26, 2022

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

            Terms & ConditionsAccessibility statement