SETUP:
- asp.net MVC3
- C#
- SQL Server 2008 R2
- jQgrid
I have a stored procedure that is pulling from a large database. Depending on the parameter values the user selects, this may return a result very quickly, or it can take several minutes.
If the conditions are right, I often time out waiting for SQL, or get a MAXJSONLENGTH error when rendering the results.
I would prefer that SQL return the results in batches of 500 records, C# serialize and pass the batch, and the jQgrid to load the results as they become available. This would make it feel more responsive and avoid both the timeout and max length errors.
It is simple enough to make an ajax call for more results until no more results are found, but I have to be sure I don't get any of the previously provided results in the subsequent requests.
In another area, I returned the record ids back to the stored procedure in order to exclude the results. In that case, there weren't too many records in the results, but the search would often take a long time. I don't think that approach is a good idea when working with this many records, but I am not sure how else to accomplish this.
The results aren't ordered, so running the procedure again isn't guaranteed to get the same 500 records at the start.
What would you recommend? Or, am I over-thinking this? (I am not asking for you to code it - though wouldn't complain if you had an example. I just need new ideas in how to tackle the problem).
From your question I deduced there are some requests that returns a significant number of rows that leads to max length json error or timeout error.
I suggest you to use pagination and make your query to return only a limited set of rows. You can achieve this by using ROW_NUMBER() function in SQL Server and then add a filter condition in WHERE clause:
For ordering, you can add a ORDER BY clause and you might consider to add a clustered index in case you expect this order in almost all the queries on that table.