oracle 处理 Web API 返回的大型 JSON 数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38794799/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dealing with large JSON data returned by Web API
提问by user4912134
We are building an web API that receives the array of strings as input parameter which queries the oracle database and returns the result as a JSON file.
我们正在构建一个 Web API，它接收字符串数组作为输入参数，该参数查询 oracle 数据库并将结果作为 JSON 文件返回。
So the code is like
所以代码就像
namespace PDataController.Controllers
{
public class ProvantisDataController : ApiController
{
public HttpResponseMessage Getdetails([FromUri] string[] id)
{
List<OracleParameter> prms = new List<OracleParameter>();
string connStr = ConfigurationManager.ConnectionStrings["PDataConnection"].ConnectionString;
using (OracleConnection dbconn = new OracleConnection(connStr))
{
var inconditions = id.Distinct().ToArray();
var srtcon = string.Join(",", inconditions);
DataSet userDataset = new DataSet();
var strQuery = @"SELECT
STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY,
STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER,
Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE,
STCD_PRIO_CATEGORY_DESCR.START_DATE AS SESSION_START_TIME ,
Trunc(STCD_PRIO_CATEGORY_DESCR.END_DATE) AS SESSION_END_DATE,
FROM
STCD_PRIO_CATEGORY_DESCR,
WHERE
STCD_PRIO_CATEGORY_DESCR.STD_REF IN(";
StringBuilder sb = new StringBuilder(strQuery);
for(int x = 0; x < inconditions.Length; x++)
{
sb.Append(":p" + x + ",");
OracleParameter p = new OracleParameter(":p" + x,OracleDbType.NVarchar2);
p.Value = inconditions[x];
prms.Add(p);
}
if(sb.Length > 0) sb.Length--;
strQuery = sb.ToString() + ")";
using (OracleCommand selectCommand = new OracleCommand(strQuery, dbconn))
{
selectCommand.Parameters.AddRange(prms.ToArray());
using (OracleDataAdapter adapter = new OracleDataAdapter(selectCommand))
{
DataTable selectResults = new DataTable();
adapter.Fill(selectResults);
var returnObject = new { data = selectResults };
var response = Request.CreateResponse(HttpStatusCode.OK, returnObject, MediaTypeHeaderValue.Parse("application/json"));
ContentDispositionHeaderValue contentDisposition = null;
if (ContentDispositionHeaderValue.TryParse("inline; filename=ProvantisStudyData.json", out contentDisposition))
{
response.Content.Headers.ContentDisposition = contentDisposition;
}
return response;
}
}
}
}
}
}
The data returned for the API is in the below format
为 API 返回的数据采用以下格式
{"data":[{"CATEGORY":"Internal Study","SESSION_NUMBER":7,"SESSION_START_DATE":"2015-02-13T00:00:00","SESSION_START_TIME":"2015-02-13T10:33:59.288394","SESSION_END_DATE":"2015-02-13T00:00:00"}]}
We are sometimes having issue in returning the large amount of data it throws the OutOfMemory Exception.
It was suggested to use the JSON property, parallel to the “data” property: like “next_data”, with a value of the value you need to pass into the SQL OFFSET (which works in MySQL, I am not sure if this works in oracle),if there no data remaining then set the value of “next_data” to 0.I am not sure how to implement this.Not sure if this can be implemented. Any help with this is greatly appreciated.
我们有时在返回大量数据时遇到问题，它引发了 OutOfMemory 异常。
建议使用 JSON 属性，与“data”属性平行：例如“next_data”，带有您需要传递到 SQL OFFSET 的值（在 MySQL 中有效，我不确定这是否适用于oracle），如果没有剩余数据，则将“next_data”的值设置为0。我不确定如何实现。不确定是否可以实现。非常感谢您对此的任何帮助。
回答by dbc
Your problem is that you are running an Oracle query that is returning a very large number of results, and then loading that entire result set into memory before serializing it out to the HttpResponseMessage.
您的问题是您正在运行一个返回大量结果的 Oracle 查询，然后将整个结果集加载到内存中，然后再将其序列化为HttpResponseMessage.
To reduce your memory usage, you should find and eliminate all cases where the entire set of results from the query is loaded into a temporary intermediate representation (e.g. a DataTableor JSON string), and instead stream the data out using a DataReader. This avoids pulling everything into memory at once according to this answer.
为了减少内存使用量，您应该找到并消除所有查询结果集加载到临时中间表示（例如 aDataTable或 JSON 字符串）中的情况，而是使用DataReader. 这可以避免根据此答案将所有内容立即拉入内存。
First, from your traceback, it appears you have Enable Browser Linkchecked. Since this apparently tries to cache the entire response in a MemoryStream, you will want to disable it as explained in FilePathResult thrown an OutOfMemoryException with large file.
首先，从您的回溯来看，您似乎已选中启用浏览器链接。由于这显然试图在 a 中缓存整个响应MemoryStream，因此您将需要禁用它，如FilePathResult 中所解释的抛出 OutOfMemoryException with large file。
Next, you can stream the contents of an IDataReaderdirectly to JSON using Json.NET with following class and converter:
接下来，您可以IDataReader使用带有以下类和转换器的 Json.NET将 的内容直接流式传输到 JSON：
[JsonConverter(typeof(OracleDataTableJsonResponseConverter))]
public sealed class OracleDataTableJsonResponse
{
public string ConnectionString { get; private set; }
public string QueryString { get; private set; }
public OracleParameter[] Parameters { get; private set; }
public OracleDataTableJsonResponse(string connStr, string strQuery, OracleParameter[] prms)
{
this.ConnectionString = connStr;
this.QueryString = strQuery;
this.Parameters = prms;
}
}
class OracleDataTableJsonResponseConverter : JsonConverter
{
public override bool CanConvert(Type objectType)
{
return objectType == typeof(OracleDataTableJsonResponse);
}
public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
{
throw new NotImplementedException("OracleDataTableJsonResponse is only for writing JSON. To read, deserialize into a DataTable");
}
public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
{
var response = (OracleDataTableJsonResponse)value;
using (var dbconn = new OracleConnection(response.ConnectionString))
{
dbconn.Open();
using (var selectCommand = new OracleCommand(response.QueryString, dbconn))
{
if (response.Parameters != null)
selectCommand.Parameters.AddRange(response.Parameters);
using (var reader = selectCommand.ExecuteReader())
{
writer.WriteDataTable(reader, serializer);
}
}
}
}
}
public static class JsonExtensions
{
public static void WriteDataTable(this JsonWriter writer, IDataReader reader, JsonSerializer serializer)
{
if (writer == null || reader == null || serializer == null)
throw new ArgumentNullException();
writer.WriteStartArray();
while (reader.Read())
{
writer.WriteStartObject();
for (int i = 0; i < reader.FieldCount; i++)
{
writer.WritePropertyName(reader.GetName(i));
serializer.Serialize(writer, reader[i]);
}
writer.WriteEndObject();
}
writer.WriteEndArray();
}
}
Then modify your code to look something like:
然后将您的代码修改为如下所示：
public HttpResponseMessage Getdetails([FromUri] string[] id)
{
var prms = new List<OracleParameter>();
var connStr = ConfigurationManager.ConnectionStrings["PDataConnection"].ConnectionString;
var inconditions = id.Distinct().ToArray();
var strQuery = @"SELECT
STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY,
STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER,
Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE,
STCD_PRIO_CATEGORY_DESCR.START_DATE AS SESSION_START_TIME ,
Trunc(STCD_PRIO_CATEGORY_DESCR.END_DATE) AS SESSION_END_DATE,
FROM
STCD_PRIO_CATEGORY_DESCR,
WHERE
STCD_PRIO_CATEGORY_DESCR.STD_REF IN(";
var sb = new StringBuilder(strQuery);
for (int x = 0; x < inconditions.Length; x++)
{
sb.Append(":p" + x + ",");
var p = new OracleParameter(":p" + x, OracleDbType.NVarchar2);
p.Value = inconditions[x];
prms.Add(p);
}
if (sb.Length > 0)// Should this be inconditions.Length > 0 ?
sb.Length--;
strQuery = sb.Append(")").ToString();
var returnObject = new { data = new OracleDataTableJsonResponse(connStr, strQuery, prms.ToArray()) };
var response = Request.CreateResponse(HttpStatusCode.OK, returnObject, MediaTypeHeaderValue.Parse("application/json"));
ContentDispositionHeaderValue contentDisposition = null;
if (ContentDispositionHeaderValue.TryParse("inline; filename=ProvantisStudyData.json", out contentDisposition))
{
response.Content.Headers.ContentDisposition = contentDisposition;
}
return response;
}
This avoids the in-memory DataSetrepresentation of the results.
这避免DataSet了结果的内存表示。
Incidentally, I reckon the line
顺便说一句，我认为这条线
if (sb.Length > 0)
sb.Length--;
instead should be:
相反应该是：
if (inconditions.Length > 0)
sb.Length--;
I believe you're trying to peel off the trailing comma in the query, which will be present if and only if inconditions.Length > 0
我相信您正试图剥离查询中的尾随逗号，当且仅当 inconditions.Length > 0
Please note - I'm not an Oracle developer and I don't have Oracle installed. For testing I mocked up the OracleClientclasses using an underlying OleDbConnectionand it worked fine.
请注意 - 我不是 Oracle 开发人员，也没有安装 Oracle。为了测试，我OracleClient使用底层模型模拟了这些类OleDbConnection，并且效果很好。
回答by Rafael A. M. S.
Are you allowed to change your method to get that data? I mean, if you are using RESTful services its not a good idea to traffic so much data on a single request. Maybe downloading a file for that purpose or maybe getting the data by pagination.
你可以改变你的方法来获取这些数据吗？我的意思是，如果您使用 RESTful 服务，那么在单个请求上传输如此多的数据并不是一个好主意。也许为此目的下载文件或通过分页获取数据。
You can also try to change the max request lenght like this answer: Change max request lenght
您还可以尝试像这个答案一样更改最大请求长度：更改最大请求长度
But again, it's not good for a web application to traffic and/or process so much data at once.
但同样，Web 应用程序一次传输和/或处理如此多的数据并不好。
