Skip to main content
1-Visitor
October 13, 2016
Question

QueryDataTableEntries with with maxItems undefined returns only 500 records

Hi Team,

In many places of our code, we use services like QueryDataTableEntries,GetDataTableEntries . we expected that query parameters with maxItems undefined, we would fetch all the records. but it will return only 500 records(May be first 500 based on the primary key). so if have a logic like as below,


var params = {

maxItems: undefined /* NUMBER */

};

// result: INFOTABLE

var RKTableEntries= Things["RkDataTable"].GetDataTableEntries(params);


var query = {

  "filters": {

    "fieldName": "isDeleted",

    "type": "EQ",

    "value": false

  }

};

var params = {

    t: resultOfCombinedAsset /* INFOTABLE */,

    query: query /* QUERY */

};

// result: INFOTABLE

var resultofSubQuery= Resources["InfoTableFunctions"].Query(params);

So the resultofSubQuery is done on the 500 records instead of entire table. Please let me know if there is any number or term that we can use similar to undefined, to avoid this problem.?

Thanks in advance.

11 replies

1-Visitor
October 13, 2016

Hi,

There's a hardcoded 500 behind the scenes on any query, if you want to get more or the entire table/stream the only option you have it's to set maxItems to a big value ( 1000000000 ), nasty but it's the only way.

Best Regards,

Translate
1-Visitor
May 11, 2017

I have similar issue where my output infotable is only returning 500 records. Let me know where and how I can set a maxitems limit for "return InfoTable".

var myRS = Things["Solar.Windchill.Services.PSECycleTime.FM.HRTest"].get_WC_ECR_Weekly_Input_Exit_Data();

Regards,

Hemant

Translate
5-Regular Member
May 12, 2017

Please refer to my comment on Thread Service output InfoTable only returning maxItems 500 records

Translate
1-Visitor
October 13, 2016

If your data tables are that large (over 10,0000 rows) or you expect them to be that large, you should not use data tables.  Performance will be terrible.  Hopefully you're using the Postgres version.  If so, simply create a new database and build out a relational schema.  Then create a new DB thing and add SQL queries to get your data.  You can look at your default persistence provide parameters to set up your Thing. 

Your app and users will thank you.

Translate
1-Visitor
February 6, 2017

My understanding is that the ceiling was closer to 100,000 (especially in PostGres version). We're accessing 60K records with good success.  Are you seeing issues with less than that?

Translate
1-Visitor
February 7, 2017

Hi Crhistopher,

If you fine tune with indexes I think you can go further than the 100k limit, also it would depend on hardware configuration.

Carles.

Translate