Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

working with SQL "FOR JSON AUTO

working with SQL "FOR JSON AUTO

working with SQL "FOR JSON AUTO


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!!


RE: working with SQL "FOR JSON AUTO

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.

RE: working with SQL "FOR JSON AUTO

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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close