Skip to main content
M
mtahir11-Visitor
1-Visitor
June 15, 2018
Question

Sql Queries

  • June 15, 2018
  • 2 replies
  • 3899 views

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?

2 replies

C
CarlesColl1-Visitor
1-Visitor
June 15, 2018

Don't thing so 😞

Z
zyuan15-Regular Member
5-Regular Member
June 18, 2018

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.

C
CarlesColl1-Visitor
1-Visitor
June 18, 2018

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.

    Z
    zyuan15-Regular Member
    5-Regular Member
    June 18, 2018

    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.

    Terms & ConditionsAccessibility statement