Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

OutOfMemoryException help optimize code

Posted on 2011-02-11
7
Medium Priority
?
355 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 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

718 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