Learn how the new web service streaming query option works by the hand of this guest post from Adisa Sinani (thanks!)
Increasingly, businesses need to make data-driven decisions – regardless of where data lives, and what is more important immediately. In this post, we’re going to take a look at how query streaming works on coreBOS and its implications.
coreBOS has a query return limit of 100 records, which is due to timeout and resource restrictions.
If you want to obtain more records you must use the limit modifier.
So, if we have an Accounts table with 150 records, we could easily query it as following:
SELECT * FROM vtiger_account LIMIT 150;
Any select statement with a limit modifier will try to return all the records indicated in the limit. If the limit is set too high, most probably the query will fail or run out of memory before finishing.
To be able to query a larger amount of data we implemented a streaming connection.
curl --location --request GET \ 'http://corebos_url/webservice.php?operation=query&query=select * from Accounts;&format=stream&sessionName=58d8c2f5612e931b1b601' \ -H 'Connection: keep-alive' -H 'Cache-Control: max-age=0' --output IDetailsRaw
Stream Raw Format
After launching the SQL query we loop over all the records and apply formatting to the fields.
We convert uitype 10 fields adding the module webservice id(70 to 15x70), dates, currency, etc.
Considering that a good part of the time is spent on that, we added another format type:
StreamRaw retrieves the fields as we would get them from the database.
Let's have a look at the difference!
When querying for 40K records on Accounts:
curl --location --request GET \ 'http://corebos_url/webservice.php?operation=query&query=select * from Accounts;&format=streamraw&sessionName=58d8c2f5612e931b1b601' \ -H 'Connection: keep-alive' -H 'Cache-Control: max-age=0' --output IDetails
As mentioned, the result when using the streamraw format type is different. StreamRaw:
When querying on coreBOS, we suggest you go with around 30K records, just to be safe! For streaming, obviously, you can go higher.
Cover photo by Franki Chamaki on Unsplash