Solved

OutOfMemoryException help optimize code

Posted on 2011-02-11
7
353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

624 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