Sql Queries

[mtahir1]

The max rows returned for an sql query inside Thingworx is just 500. Is there any way to increase it for all services from the system settings rather than changing every service individually?

[CarlesColl]

Don't thing so :(

[zyuan1]

The SQL query or all the other query services in TWX has a default of 500, since the result of the script: infotable, will use some extra memory resources. Especially when you want to present the infotable data in the mashup through Grid or Chart, it will cost a lot of cache space, and the data will load slow if the data is higher than 500 rows. (The Grid usually don't need even 500 rows of data).  So to make the system running more efficient, it's better to keep the MaxItem 500, and only increase the number when really needed.

PS: the Join/merge service in TWX is very costly, it's highly suggested that the tables are joined in SQL side first.

[CarlesColl]

There's no reason at all to limit SQL Queries to 500 rows, let the developer decide Speed and Performance of queries and results. The hard coded limit on SQL Queries Services ( which can't be changed at runtime ) it's a pain in the ass.

Not all TW Service calls will end up on a Mashup, there's lots of internal Services calls which never will see a Broswer neither a public network...

I think this was a naif design that should be changed.

[zyuan1]

Hi Carles,

500 is just a default number, and it's not hardcoded for the runtime. The maxItems property is an editable Input for the Queryservices. In the SQL query command you can Select * and maxItems you can change it in a mashup input field or an input for the backend Rest call.

[CarlesColl]

How do you change that at runtime?

[mtahir1]

I dont think you can change at runtime. But I want to change the default maybe from subsystems or other place if possible 

[CarlesColl]

Yeah I know, that can't be changed as per my first answer. 

[zyuan1]

Like this:

var info = me.exportall() //exportall()  is a SQL query service SELECT * FROM TABLE

var params = {
	maxItems: maxitem /* NUMBER */,  // row count input
	t: info /* INFOTABLE */
};

// result: INFOTABLE dataShape: "undefined"
var result = Resources["InfoTableFunctions"].TopN(params);

[CarlesColl]

Yea, but before that you had to hard code on exportAll SQL Service a "big enought" value, and anyway you en up with a super inneficient service as you are querying for all and then just recovering the desired one. Becouse of the TW Hard Coded limitation to improve performance you ended up on a super-inefficient code...

[zyuan1]

Sure, but you don't need to give every SQL query a giant number based on your real use case, and by now this is the only way which allow customer to choose the Maxitems value for the infotable result.