working with SQL "FOR JSON AUTO

[NoCoolHandle]

Hi,

I have a query that returns a large amount of JSON data via SQLs FOR JSON clause.
The dataset is around 6 million rows being squeezed into a single column/row of JSON data.

My problem is trying to read that efficiently in a C# based Restful webservice.

Currently I am returning it into a data reader and then calling Read until the datareader is empty (remember this gets returned as a single row/column in Management Studio.

In Management Studio the query takes around 35 seconds.

In my webservice using a data adapter it "times out" and crashes. (500 error)
If I use a DataReader and use the above methodology it will also time out unless I reduce the number of rows to only 500k (from the 5,700,000 rows without any filters/paging.) - even 500k takes around 60 seconds.. The full dataset will NEVER return.

Any ideas as to how to get all the data quickly?

Thanks in advance!!

Rob

 

[docotor]

I would suggest to do following:
1. Create stored procedure, which taks as input parameter page size and page number.
2. Stored procedure should return subset of data
3. Create one more stored procedure which will tell whole dataset size.
4. It may be worthy to analyze SQL query execution plan, and see if you can optimize either indexes, or apply some denormalization techniques to dataset.

 

[NoCoolHandle]

Hi docotor
thanks for the response and thoughts.. I did figure out what the issue was. Also i did do exactly as you suggested with a rows and id parameters to allow the users to specify exactly how many rows they wanted, and which record to restart with
The big issue was i was trying to return the json to a browser window so i could view it. (Our company had locked my laptop down and i didnt have access to tools like Postman.
What i did to speed up the return was build the json into a file and then return the file. THe rendering time in the browser was causing issues when the json was over 20megs.

the JSON includes (at the end.. how may rows were returned, the next start position for the ID and also how many rows were left to process.

overall i am astounded with exactly how well the for JSON clause works.

One or json dataasets was over 75megs which caused timeouts in all sorts of areas (especially rendering)