QueryDataTableEntries is not working expectedly Thingworx 9.3.7

[Jamal8548]

it has to match with the serial Number and return the entry where it matches the serial number but it  returns all the entries of datatable. sometimes it works fine but again after sometime if i run the service again it shows me all the entries. it works fine until i give the serialnumber to get the entries and then when i add wrong serialnumber then it keeps showing me the old output with all the entries. I have never seen such a wrong behaviour before. Anyone can please help me in this regard

const SERVICENAME = "evaluateExistingSerialNumber";
logger.info(me.name +" :: "+ SERVICENAME +" :: Start Service");


//let result;
let maxEntriesToFilter 	= 40;

let latestEntriesInfoTable  = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
                        infoTableName: "InfoTable",
                        dataShapeName: "ReUse.LatestEntries.DS"
                    });
let singleEntryInfoTable  = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
                        infoTableName: "InfoTable",
                        dataShapeName: "ReUse.LatestEntries.DS"
   
});


//try {
    
latestEntriesInfoTable =  Things["ReUse." + lineName + "-" +Location +".DT"].QueryDataTableEntries({
	maxItems: maxEntriesToFilter /* NUMBER */,
	query: {
    "type": "And",
    "filters": [{
        	"type": "IN",
        	"fieldName": "serialNumber",
            "values": [serialNumber]
    	},
               
              
              
              
              ],
    
}
});

//DEBUG STARTS
// For debugging (select OUTPUT as an infotable latestentriesDS- first 3 lines for latest entry and last line for all the entries)
//let resultRow = latestEntriesInfoTable.getRow(0);
//singleEntryInfoTable.AddRow(resultRow);
//let result = singleEntryInfoTable;

let result = latestEntriesInfoTable;

[DanZ]

That is strange, I can reproduce the issue. But it seems to be related to the (in this case) unnecessary filter composition with the "AND" type. Try this:

{
  "filters": {
    "type": "IN",
    "fieldName": "serialNumber",
    "values": ["serialNumber"]
  }            
}

[Jamal8548]

i have removed it but thats not an issue actually. If i add in fitler another query with fieldName then the behviour gets weired.

My question: if i add two fields in filter to filter and i want only that entry where the both fields present with the value i give. is it possible to get only that entry? OR QueryDatatableentries gives even though one field filter will be fullfilled.??

[Rocko · Accepted solution]

The syntax needs another "filters" on top level, that is missing in your example. On your other question, yes, that is what the "And" filter is for.

Check lines 3 and 5 in this example and compare with yours.

var query =
{
    "filters": {
        "type": "And",
        "filters": [
            {
                "type": "GT",
                "fieldName": "Duration",
                "value": "12"
            },
            {
                "type": "Tagged",
                "fieldName": "tags",
                "tags": "MaintenanceIssues:PowerOutage"
            }
        ]
    }
}; 

[Jamal8548]

@Rocko I want the entry where the serialnumber and productcategory have the values which i will give and i want to match it as it is. Can you please see the code what mistake i am doing? Because it is showing me NO DATA. but if i execute the commented query for serialNumber only then it shows the entries only with that serialnumber which is good but i want only those entries which will match with both the serialNumber and productCategory.

const SERVICENAME = "evaluateExistingSerialNumber";
logger.info(me.name +" :: "+ SERVICENAME +" :: Start Service");



let maxEntriesToFilter 	= 30;

let latestEntriesInfoTable  = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
                        infoTableName: "InfoTable",
                        dataShapeName: "ReUse.QualityData.DS"
                    });



try {
    logger.info("ReUse." + lineName + "-" +Location +".DT");
latestEntriesInfoTable = Things["ReUse." + lineName + "-" +Location +".DT"].QueryDataTableEntries({
    
	maxItems: maxEntriesToFilter /* NUMBER */,
//	query: {
  
//   "filters": {
//        	"type": "IN",
//        	"fieldName": "serialNumber",
//            "values": [serialNumber]
//    	},
   query: {
    "filters": {
        "type": "And",
        "filters": [
            {
                "type": "IN",
                "fieldName": "serialNumber",
                "value": [serialNumber]
            },
            {
                "type": "IN",
                "fieldName": "productCategory",
                "value": [productName]
            }
        ]
    }
   }

 //   }
});

} catch(e){
logger.info("the error is here"+e);
}

let result = latestEntriesInfoTable;

[Rocko]

I can check later, but when you compare to only one value and not multiple, is there a reason why you use IN and not EQUALS in your filters?

[Jamal8548]

@Rocko Thank you so much. It is working now. I have just removed array brackets and instead of IN i have used EQ and now i got actually what i want. now my second question is that have you ever used FindDataTableEntries service because i have read that you can add index in Datatable configuration and then with index this service works really fast to get the entry from big data is it true? what do you think in this regard.

[Rocko]

You will find some threads here about FindDataTableEntries in the community. You can use it but I personally think it's not worth the hassle with the indexes. Don't optimize if you don't need to. If QueryDataTableEntries is fast enough, stick with it.

If it is not fast enough, I wouldn't go and optimize DataTable access, but use the right storage for your data. Scaling options are limited with DataTables.

In DataTables, it's not that your columns are database column. All properties will be written into one JSON column. That's comfortable because you can add to and remove columns from DataTables easily at runtime without the need to change the DB schema. But it's not optimal when it comes to performance. Therefore the guideline says not to use DataTables for more than 100k rows, but how performant it is for you depends on your data.

If you need faster access, you can use SQL tables directly with the DatabaseThing.

[Jamal8548]

These insights are so useful Thank you! but have you ever used SQL tables directly with the DatabaseThing? If you have done that can you share the information about that. I have seen already some documentation about it but somethings were not that clearer for me. 

[Rocko]

This should be pretty exhaustive on the topic: https://community.ptc.com/t5/IoT-Tips/Connecting-External-Databases-and-Model-Part-1/ta-p/832811

[Jamal8548]

@Rocko  I have one project where we have some datatables and the entries are more then 400K. At the moment for every datatable i have another datatable which store the 100 latest entries and with that datatable i use for every entry GetDataTableentrywithKey service and just fetch the latest entries for everydatatable and it loads very fast in the mashup. each entry has 7 rows in it. Now the question is: We have implemented a Search function and user can search anything and it returns the entry with matching words but now the search function matched the search with every field( thats user REQUIREMENT) of every single entry and it takes around 10 to 15 seconds to get the entry which matches with the given query. 

What do you think in the regard the best solution? Should i consider the external SQL database for this usecase or how do you see this usecase? 

I with limited knowledge was thinking that may be we would have the externaldatabase and i use pagination to get the for example 100 entries and for search i am still thinking what can be the solution. how do you see this usecase and what are your recommendations in this regard?

[Rocko]

I don't know your skillset so I can't tell what's best for you, but with those requirements, I would move away from DataTables. They are only recommended for up to 100k rows as per TWX Help. As you see they don't scale well and you already maxed them out. For your use case it's a  dead end road. You need search for all fields, so I would move to an external SQL DB (technically could be the same as for Thingworx, just using a different schema). Move your data there. Do the indexing on the most searched fields.

Then depending on which fields the users are querying build the where clause of the SQL string dynamically and send it to the DB.

As an alternative, the DBConnection Building Block can do this for you, but it has a learning curve and needs TWX 9.3.4+. Here's a tutorial: https://community.ptc.com/t5/IoT-Tips/Using-the-DBConnection-Building-Block-to-create-and-access/ta-p/865411

[Jamal8548]

@Rocko I am using ThingWorx 9.3.7-b1432. I am thinking to go with MS SQL as you said to first connect it with thingworx and then i will do further process but for showing entries on the mashup I am thinking to only show 100 entries on the mashup from each production line. Actually i have 6 production lines with names L1,L2...L6 and that means i have to create six tables in the SQL first and then from it i would just show the latest 100 entries from each line but i have already done the visibility permissions in thingworx with datatable as from drop down selection the linenames can be shown with selection and only the linename can be shown with whom the user has right to who would be login and then the further mashup loads and displays the data from the respected linename selection. Currenlty my model looks like that i have a datashape with 8 fields with one field is of Infotable which then contains entries in it. So i have one dataGrid widget in which main entry appears and then when you click on it then entries will be loaded from that Infotable into second dataGrid which are around 8 or 9. so that is my usecase.

[Rocko]

If we're looking at machine data here, that should go to a Value Stream in the first place, they are made for machine data. Of course you can use your own tables, but Value Streams have much better integration than the DIY approach. I would benchmark this as well. With your size of data I would say one value stream per line.

On this requirement of making every column searchable, you might be tempted to index each column. Avoid doing so, it's a general RDBMS anti-pattern which will make your inserts slower and has other side-effects. Users must learn they can't have a cake and eat it. If they are searching through a large body of data, some waiting time is to be expected. Try to find out which fields they filter on often, and which are less searched-on fields. For the latter, higher waiting times would be acceptable.

[Jamal8548]

@Rocko  Its not a machine data. Its just like the for eg the engine is coming on the production line and user has to take pics of it from 7 sides thats it and with each pic we choose it right or wrong means its defected or not. that means each entry has 7 sub entries and there is no critical data there. Its just user taking pic and selected OK or NOT OK with each step. I have attached a screenshot from mashup you can see. so i have one datashape called main entry and in the datashape one of the field is also infotable which contains all the 7 steps in it and when u click on the main entry then u get the 7 for eg entries on the right grid from its infotable. 

Now my REQUIREMENT: how to show the data quickly on the mashup may be only latest 100 entries from each line from DATABASE and search feature which will search the fields. i think now you understand my use case better. what do you think? should i use MS SQL database is it good way?

[Rocko]

@Jamal8548 Ok, just wanted to make sure. MS SQL is fine for the job.

[smainente]

Note that the DataTable "custom index" implementation is DB specific. PG can index Json directly, and the datatable json playload is indexed by default. The TWX MSSQL persistence provider will clone the data into a separate db table to be able to index them (with a cost).

I don't know if FindDataTableEntries will be very fast (need to benchmark with your use case and it will differt between Persistence Providers), but at least it will not fetch the entire DataTable in the platform memory to apply the filter - see Data Table and Stream Query APIs Pitfalls

[Jamal8548]

Thanks @smainente i have read the pitfalls and in the end of the doc it is written that More to come :

• Use of Data Table configurable indexes .

but i have not found it. Can you please give me link to read about this topic. I would like to perform a benchmark testing with it.

Currently i am just using the Datatables in the platform and not using any database with it.

[smainente]

I ended up not making the post due to the complicated nature of the feature, which involves specific TWX versions, datecodes, and database vendor. I’ve also opened a few JIRA along the way. This was all in 8.4, and I’m not sure about the status of this feature in the newer versions.