OutOfMemoryException help optimize code
Hi,
I have code in a webservice. It reads data from SQL database, builds XML from it and returns that from the web service. It worked fine in DEV. When we went live large data started coming and boom OutOfMemoryException. Things I did that didn't work
1. Stop processing on server and return dataset to client then create correct XML at client end. Came exception at client machine (600MB free then goes to 20MB then crash)
2. Rename field names of data returning (i.e from productDescription to desc). Realised thsi wasn't going to reduce memory usage as such
3. Use XPathNavigator and XPathDocument. Started but it was pain in the neck getting the data I wanted out so still an option it someone can help and if it will reduce memory foot print
Not done yet
1. Separate data somehow when reading it. Might not work as our app needs to run offline and so first connection needs to get data (sync)
When I run the stored procedure it said 3500 rows affected so I thought that is too many to process. Well....
Ideas please. I hope some genius points me to something silly that I am doing causing all this memory sucking
Cheers
H
I have code in a webservice. It reads data from SQL database, builds XML from it and returns that from the web service. It worked fine in DEV. When we went live large data started coming and boom OutOfMemoryException. Things I did that didn't work
1. Stop processing on server and return dataset to client then create correct XML at client end. Came exception at client machine (600MB free then goes to 20MB then crash)
2. Rename field names of data returning (i.e from productDescription to desc). Realised thsi wasn't going to reduce memory usage as such
3. Use XPathNavigator and XPathDocument. Started but it was pain in the neck getting the data I wanted out so still an option it someone can help and if it will reduce memory foot print
Not done yet
1. Separate data somehow when reading it. Might not work as our app needs to run offline and so first connection needs to get data (sync)
When I run the stored procedure it said 3500 rows affected so I thought that is too many to process. Well....
Ideas please. I hope some genius points me to something silly that I am doing causing all this memory sucking
Cheers
H
try
{
MemoryStream stream = new MemoryStream();
XmlTextWriter xmlWriter = new XmlTextWriter(stream, System.Text.Encoding.UTF8);
StreamReader streamReader = new StreamReader(stream, System.Text.Encoding.UTF8);
xmlWriter.WriteStartDocument();
xmlWriter.WriteStartElement("Promotions");
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
xmlWriter.WriteStartElement("Promotion");
xmlWriter.WriteAttributeString("Id", row["id"] == DBNull.Value ? "0" : row["id"].ToString());
xmlWriter.WriteAttributeString("Code", row["code"] == DBNull.Value ? "" : row["code"].ToString());
xmlWriter.WriteAttributeString("Name", row["name"] == DBNull.Value ? "" : row["name"].ToString());
xmlWriter.WriteAttributeString("Description", row["desc"] == DBNull.Value ? "" : row["desc"].ToString());
xmlWriter.WriteAttributeString("TypeCode", row["typeCode"] == DBNull.Value ? "0" : row["typeCode"].ToString());
xmlWriter.WriteAttributeString("TypeDescription", row["typeDesc"] == DBNull.Value ? "" : row["typeDesc"].ToString());
xmlWriter.WriteAttributeString("BusinessAreaCode", row["busCode"] == DBNull.Value ? "0" : row["busCode"].ToString());
xmlWriter.WriteAttributeString("BusinessAreaDescription", row["busDesc"] == DBNull.Value ? "" : row["busDesc"].ToString());
xmlWriter.WriteAttributeString("MaxUsage", row["max"] == DBNull.Value ? "0" : row["max"].ToString());
xmlWriter.WriteAttributeString("MaxUsageAmount", row["maxAmount"] == DBNull.Value ? "0" : row["maxAmount"].ToString());
xmlWriter.WriteAttributeString("MinUsageAmount", row["minAmount"] == DBNull.Value ? "0" : row["minAmount"].ToString());
xmlWriter.WriteAttributeString("CanOrder", row["canOrder"] == DBNull.Value ? "0" : row["canOrder"].ToString());
xmlWriter.WriteAttributeString("UsageLeft", row["usage"] == DBNull.Value ? "0" : row["usage"].ToString());
xmlWriter.WriteAttributeString("ShowOffer", row["show"] == DBNull.Value ? "0" : row["show"].ToString());
DataView dv = new DataView(ds.Tables[1]);
dv.RowFilter = "promId = " + row["id"].ToString();
xmlWriter.WriteStartElement("Products");
foreach (DataRowView match in dv)
{
xmlWriter.WriteStartElement("Product");
xmlWriter.WriteAttributeString("PromotionId", match["promItemId"] == DBNull.Value ? "0" : match["promItemId"].ToString());
xmlWriter.WriteAttributeString("Id", match["id"] == DBNull.Value ? "0" : match["id"].ToString());
xmlWriter.WriteAttributeString("Code", match["code"] == DBNull.Value ? "0" : match["code"].ToString());
xmlWriter.WriteAttributeString("Description", match["desc"] == DBNull.Value ? "" : match["desc"].ToString());
xmlWriter.WriteAttributeString("Weight", match["weight"] == DBNull.Value ? "0" : match["weight"].ToString());
xmlWriter.WriteAttributeString("Quantity", match["qty"] == DBNull.Value ? "0" : match["qty"].ToString());
xmlWriter.WriteAttributeString("Price", match["price"] == DBNull.Value ? "0" : match["price"].ToString());
xmlWriter.WriteAttributeString("MustBuy", match["mustBuy"] == DBNull.Value ? "0" : match["mustBuy"].ToString());
DataView dv2 = new DataView(ds.Tables[2]);
if (row["typeCode"] != DBNull.Value && row["typeCode"].ToString() == "6")
dv2.RowFilter = "typeCode = 6";
else
dv2.RowFilter = "promItemId = " + match["promItemId"].ToString();
xmlWriter.WriteStartElement("Offers");
foreach (DataRowView match2 in dv2)
{
xmlWriter.WriteStartElement("Offer");
xmlWriter.WriteAttributeString("PromotionId", match2["promItemId"] == DBNull.Value ? "0" : match2["promItemId"].ToString());
xmlWriter.WriteAttributeString("Id", match2["id"] == DBNull.Value ? "0" : match2["id"].ToString());
xmlWriter.WriteAttributeString("Code", match2["code"] == DBNull.Value ? "0" : match2["code"].ToString());
xmlWriter.WriteAttributeString("Description", match2["desc"] == DBNull.Value ? "" : match2["desc"].ToString());
xmlWriter.WriteAttributeString("Weight", match2["weight"] == DBNull.Value ? "0" : match2["weight"].ToString());
xmlWriter.WriteAttributeString("Quantity", match2["qty"] == DBNull.Value ? "0" : match2["qty"].ToString());
xmlWriter.WriteAttributeString("Price", match2["price"] == DBNull.Value ? "0" : match2["price"].ToString());
xmlWriter.WriteAttributeString("MustBuy", "0");
xmlWriter.WriteEndElement();
}
dv2.Dispose();
xmlWriter.WriteEndElement();
xmlWriter.WriteEndElement();
}
dv.Dispose();
xmlWriter.WriteEndElement();
xmlWriter.WriteEndElement();
}
}
}
xmlWriter.WriteEndElement();
xmlWriter.WriteEndDocument();
xmlWriter.Flush();
stream.Position = 0;
promotionsAndProductsList = streamReader.ReadToEnd();
stream.Close();
streamReader.Close();
xmlWriter.Close();
}
catch (Exception ex)
{
throw;
}ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
jasonduan
Why would you do that? What is the rationale behind your thinking?
naman_goel
Your points all make sense. In terms of code commenting I always comment well but in this case all there is is a Dataset with 3 tables to write out as xml. I sort of dislike overkill comment.
Your point regarding accessing column value via row[colum name] twice is noted however, what is better declaring a set of variables in the loop that go out of scope every loop count or accessing them twice as I did or having variables declared outside the loop? It is a gray area for me.
I didn't know I could filter using the DataRows that will hopefully speed things up.
Things I have done lately == current state of things :-)
1. Changed from writing to memory to writing to file. Found out that I was dealing with nearly a 1GB of data!!! Something is dodgy at the database end I believe. Still this exercise to optimize things is is well worth
Cheers
H
Why would you do that? What is the rationale behind your thinking?
naman_goel
Your points all make sense. In terms of code commenting I always comment well but in this case all there is is a Dataset with 3 tables to write out as xml. I sort of dislike overkill comment.
Your point regarding accessing column value via row[colum name] twice is noted however, what is better declaring a set of variables in the loop that go out of scope every loop count or accessing them twice as I did or having variables declared outside the loop? It is a gray area for me.
I didn't know I could filter using the DataRows that will hopefully speed things up.
Things I have done lately == current state of things :-)
1. Changed from writing to memory to writing to file. Found out that I was dealing with nearly a 1GB of data!!! Something is dodgy at the database end I believe. Still this exercise to optimize things is is well worth
Cheers
H
so if you are dealing with that much data it's better to use Typed dataset inplace of normal UnTyped DataSet, That will optimize more things and can give us more flexibilities.
and I am not able to understand following statement :
" In terms of code commenting I always comment well but in this case all there is is a Dataset with 3 tables to write out as xml. I sort of dislike overkill comment."
and I am not able to understand following statement :
" In terms of code commenting I always comment well but in this case all there is is a Dataset with 3 tables to write out as xml. I sort of dislike overkill comment."
and off-course 1GB data is too much so it this case it actually can grow to 2-2.5 GB as you are caching data in 3 tables processing it over strings and writing to xml files.
ASKER
>>" In terms of code commenting I always comment well but in this case all there is is a Dataset with 3 tables to write out as xml. I sort of dislike overkill comment."
I meant that I normally comment code BUT only if it is needed. Processing the data in those 3 tables is obvious and does not need comment in my opinion.
I'll think of a typed dataset. I have not used it before.
Thanks
I meant that I normally comment code BUT only if it is needed. Processing the data in those 3 tables is obvious and does not need comment in my opinion.
I'll think of a typed dataset. I have not used it before.
Thanks
following article is sort of walkthrough for typed dataset:
http://weblogs.asp.net/scottgu/archive/2006/01/15/Building-a-DAL-using-Strongly-Typed-TableAdapters-and-DataTables-in-VS-2005-and-ASP.NET-2.0.aspx
but its pretty easy just the question of drag and drop.
http://weblogs.asp.net/scottgu/archive/2006/01/15/Building-a-DAL-using-Strongly-Typed-TableAdapters-and-DataTables-in-VS-2005-and-ASP.NET-2.0.aspx
but its pretty easy just the question of drag and drop.
1. As MemoryStream, XmlTestWriter, StreamReader in using block and these are using unmanaged objects we should make sure that these objects will be disposed after their use
2. DataView is very good object to filter DataTable but it can consume huge memory and in your code you are using this inside your foreach loop, so it's better to use lightweight DataRows[] instead of DataView as DataView is retaining memory until the call of Dispose() method of DataView.
3. Make sure you are disposing the DataSet(ds) object after its use. Not sure if you are calling it.
Please refer to modified code for more details.
Open in new window