Skip to main content
VladimirRosu_116627Answer
19-Tanzanite
Jul 26, 2022 10:01 AM UTC

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

View in thread

10 replies

nmilleson17-Peridot
17-Peridot
Jul 21, 2022 1:40 PM UTC

@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

Translate
    MG_26021-VisitorAuthor
    1-Visitor
    Jul 25, 2022 6:42 AM UTC

    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

    Translate
    nmilleson17-Peridot
    17-Peridot
    Jul 25, 2022 1:22 PM UTC

    @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

    Translate
      VladimirRosu_11662719-Tanzanite
      19-Tanzanite
      Jul 25, 2022 7:12 PM UTC

      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.

      Translate
        MG_26021-VisitorAuthor
        1-Visitor
        Jul 26, 2022 9:33 AM UTC

        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

        Translate
          VladimirRosu_11662719-TanzaniteAnswer
          19-Tanzanite
          Jul 26, 2022 10:01 AM UTC

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

          Translate
            Terms & ConditionsAccessibility statement