Solved

OutOfMemoryException help optimize code

Posted on 2011-02-11
7
348 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
ID: 34874211
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
ID: 34878855
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
ID: 34886707
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 13

Expert Comment

by:Naman Goel
ID: 34886733
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
ID: 34886755
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
ID: 34886788
>>" 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
ID: 34886845
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

679 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