Solved

OutOfMemoryException help optimize code

Posted on 2011-02-11
7
341 Views
Last Modified: 2013-12-17
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
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;
            }

Open in new window

0
Comment
Question by:gbzhhu
  • 4
  • 2
7 Comments
 
LVL 11

Accepted Solution

by:
jasonduan earned 500 total points
Comment Utility
I would try something like this:

StringBuilder sb = new StringBuilder();

using(StringWriter sw = new StringWriter(sb))
using(XmlTextWriter xmlWriter = new XmlTextWriter(sw, System.Text.Encoding.UTF8))
{
    xmlWriter.WriteStartDocument();
    .....
    xmlWriter.WriteEndDocument();
    xmlWriter.Flush();

    promotionsAndProductsList = sb.ToString();
}

0
 
LVL 13

Expert Comment

by:Naman Goel
Comment Utility
There are very few comments on you code :
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.

try
            {
                //Adding MemoryStream, XmlTestWriter, StreamReader in using block and these are using unmanaged objects

                using (MemoryStream stream = new MemoryStream())
                {
                    using (XmlTextWriter xmlWriter = new XmlTextWriter(stream, System.Text.Encoding.UTF8))
                    {
                        using (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)
                                    {
                                        //Doesn't matter but it should be change because we are accessing the same thing 2 times.
                                        string id= row["id"] == DBNull.Value ? "0" : row["id"].ToString();
                                        xmlWriter.WriteStartElement("Promotion");
                                        xmlWriter.WriteAttributeString("Id", id);
                                        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());

                                        //Although DataView is good but DataRow consumes less memory and we can remove the instance on the go
                                        DataRow[] dataRows = ds.Tables[0].Select("promId = " + id);
                                        int rowsCount = dataRows.Length;

                                        xmlWriter.WriteStartElement("Products");

                                        for (int i = 0; i < rowsCount; i++)
                                        {
                                            DataRow match = dataRows[i];
                                            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());

                                            DataRow[] matches2 = null;

                                            if (row["typeCode"] != DBNull.Value && row["typeCode"].ToString() == "6")
                                                matches2 = ds.Tables[2].Select("typeCode = 6");
                                            else
                                                matches2 = ds.Tables[2].Select("promItemId = " + match["promItemId"].ToString());

                                            xmlWriter.WriteStartElement("Offers");

                                            int matches2Count = matches2.Length;

                                            for (int rowIndex = 0; rowIndex < matches2Count; rowIndex++)
                                            {
                                                DataRow match2 = matches2[rowIndex];

                                                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();

                                                match2 = null;

                                                matches2[rowIndex] = null;
                                            }

                                            Array.Clear(matches2, 0, matches2Count);

                                            xmlWriter.WriteEndElement();
                                            xmlWriter.WriteEndElement();

                                            match = null;
                                            dataRows[i] = null;
                                        }

                                        Array.Clear(dataRows, 0, rowsCount);

                                        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;
            }

Open in new window

0
 
LVL 12

Author Comment

by:gbzhhu
Comment Utility
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
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 13

Expert Comment

by:Naman Goel
Comment Utility
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."
0
 
LVL 13

Expert Comment

by:Naman Goel
Comment Utility
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.

0
 
LVL 12

Author Comment

by:gbzhhu
Comment Utility
>>" 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
0
 
LVL 13

Expert Comment

by:Naman Goel
Comment Utility
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now