Stored Procedure results to JSON response minimizing memory usage in C# MVC

Question

I currently have a MVC C# web application and I am trying to create an action result that acts kind of like an API. This API method runs a stored procedure and returns the results in JSON format as the response.

Problem is, in some instances the stored procedure is returning ~6.5k rows, and all that data is coming into memory of my application and blowing it up. (there's a good bit of columns to this data, all required)

So my question is: is there a way to run a stored procedure and return its results to the client without bringing the whole result set into memory on the server? Maybe I can take the response sql stream and feed it to the response stream?

My current code contains the memory leak and is as follows:

public ActionResult GetOutletsByHierarchyLevel(string SearchTerm, string Level, bool ReturnAll = false)
{
    ...
    var data = db.CollectionFromSql(
        "EXEC [apps].[Rapport_GetOutletsByHierarchyLevel] @SearchTerm,@HierarchyLevel,@ReturnAll",
        new Dictionary<string, object>
            {{"@SearchTerm", SearchTerm}, {"@Level", Level}, {"@ReturnAll", ReturnAll}}
    );


    var jsonResult = Json(new JSONResponse()
    {
        Success = true,
        Data = data.ToList().DynamicSQLToDictionary()
    });

    data = null;

    jsonResult.MaxJsonLength = int.MaxValue;


    return jsonResult;
}

Let me know if there's anything else I can supply you all with. Thanks in advance for any recommendations/articles!

Have you considered getting results directly as JSON using [`FOR JSON`](https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-2017) instead of using various objects as intermediate forms? — madreflection, Aug 15 '19 at 18:43
6.5k row isn't that much. If it's really that big then the application calling it might have trouble also (or bandwidth problem). But you still have access to Response.OutputStream and Response.Flush, you could loop one row at a time and submit the json one by one. — the_lotus, Aug 15 '19 at 18:51
It's fairly new, added in SQL Server 2016. Be careful, though: making your stored procedure return a specific *format* of data ties it to very specific uses. This tightly couples your data access to your presentation layer and may limit its use such that you have to write multiple procedures for similar purposes. — madreflection, Aug 15 '19 at 19:13
I still question whether you have an actual *leak*, though. High memory usage does not necessarily mean a leak. It could be a patient GC. — madreflection, Aug 15 '19 at 19:14
@madreflection definitely. This is a one-use stored procedure though so I think it should work given it's purpose. Thank you! That's true though, I'll have to see the timing of my GC. Another thing though, with this much data, I'd much rather it never be brought into memory to even have to worry about GC. — Darryl Huffman, Aug 15 '19 at 19:15

score 1 · Answer 1 · answered Aug 15 '19 at 18:51

1

Do you have code access to the stored procedure? If yes, then you can to implement pagination on sql level: Implement paging (skip / take) functionality with this query.

answered Aug 15 '19 at 18:51

as94

148
1
9

I think that's a good idea. For example, the youtube API only send x rows and an other request is needed to get the next x rows. – the_lotus Aug 15 '19 at 18:52
I do have access to the code of the stored procedure, however one of the goals of this API is to provide a comprehensive list of all of the data - for spreadsheet creation, etc. – Darryl Huffman Aug 15 '19 at 19:05
Your client should do several requests to your API and after that this client to combine this parts to one place. – as94 Aug 15 '19 at 19:23

victordscott · Answer 2 · 2019-08-15T20:12:31.647

I return JSON directly from my stored procedures and then return a JSON response from the API method (requires SQL Server 2016 or greater). I have written helper methods to call my stored procedures. This one calls a stored procedure with no parameters and returns a JSON array as a string:

public static string GetDataJSONArray(
    string procName,
    string connection = null
)
{
    if (connection == null)
        connection = defaultConnection;

    var sql = procName;
    StringBuilder sb = new StringBuilder();
    using (SqlConnection sqlConnection = new SqlConnection(connection))
    {
        sqlConnection.Open();
        SqlCommand cmd = new SqlCommand(sql, sqlConnection);
        cmd.CommandTimeout = defaultTimeout;
        cmd.CommandType = CommandType.StoredProcedure;

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                sb.Append(reader.GetString(0));
            }
        }
    }

    var json = sb.ToString();
    if (string.IsNullOrWhiteSpace(json))
    {
        json = "[]";
    }

    return json;
}

Example proc:

create procedure GetTableRecordCounts
as
begin
    select t.name TableName, i.rows RecordCount
    from sysobjects t, sysindexes i
    where t.xtype = 'U' and i.id = t.id and i.indid in (0,1)
    order by RecordCount desc
    for json path, INCLUDE_NULL_VALUES
end
go

Example API Method:

[HttpGet]
public HttpResponseMessage GetTableRecordCounts()
{
    var jsonString = ProcWrapper.GetDataJSON("GetTableRecordCounts");

    HttpResponseMessage success = Request.CreateResponse(HttpStatusCode.OK);
    success.Content = new StringContent(jsonString, Encoding.UTF8, "application/json");
    return success;
}

I don't deserialize to objects in the API method unless I need to for some reason. BTW, I currently use the approach of passing a JSON object or array as string into all my procs that need input and I always return a JSON object or array as a string if the proc needs to return a result. Therefore, my procs have a single parameter like "@JSON nvarchar(max)". I like the simplicity.

Stored Procedure results to JSON response minimizing memory usage in C# MVC

2 Answers2