Linq to SQL is taking more memory

indian143

Hi All, I have created few Linq Queries using Lambda expressions in my application, and I got feedback that my Linq Queries and Lambda expressions are taking more memory. I dont know how to see that, but he told me. Can anybody please advice me how to create Linq and Lambda expressions by consuming very less memory. I am new to the Linq and Lambda. I am giving my code below please advice me any kind of help is greatly appreciated. Either give me idea or links to write efficient linq and lambda's or give me any alternate solution if we have other that linq and lambda. And one more thing is that we are using Linq to SQL to connect to the database. Below is the Code:

    public string GetQuarterlyUpdateTUCDataXml( int quarterlyUpdateJobId, int quarterlyUpdateSubscriptionId)
    {
        string strQuarterlyUpdateDeltaReport = null;            

        var quarterlyUpdateSubscription = parallelDataContext.teQUTUDatas.Where(c => c.QuarterlyUpdateJobID ==
 quarterlyUpdateJobId && c.QuarterlyUpdateSubscriptionID == quarterlyUpdateSubscriptionId).ToList();

        if ((quarterlyUpdateSubscription != null) &&

(quarterlyUpdateSubscription.Count > 0))
{
byte[] encryptedBytes = (from a in quarterlyUpdateSubscription
join
b in parallelDataContext.teBinaryDatas on

a.DeltaReportBinaryDataID equals b.BinaryDataID
select b.Data).FirstOrDefault().ToArray();

            strQuarterlyUpdateDeltaReport = (encryptedBytes != null) ?
              objCommon.GetDecryptedReportString(encryptedBytes) : ""; //objCommon.GetDecryptedReport(encryptedBytes); //                       
        }
        
        return strQuarterlyUpdateDeltaReport;
    }

Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA

Not Active

indian143 wrote:

I dont know how to see that, but he told me.

Who told you there were using too much memory? How did they know? Did you ask? More memory compared to what? Is it the query or the data that is taking up too much memory? Linq does use more memory than traditional ADO.NET but there isn't much you can do about it and the trade-off is more beneficial in ease of use, maintenance, extensibility and in some cases performance.

from a in quarterlyUpdateSubscription
join b in parallelDataContext.teBinaryDatas on
a.DeltaReportBinaryDataID equals b.BinaryDataID
select b.Data

Since we have no idea what data is behind this an accurate assessment can't be made. However, if there are large sets involved here it will of course use more memory to store and perform the query and join. If you can reduce the size of the set it may help.

I know the language. I've read a book. - _Madmatt

modified on Friday, July 1, 2011 12:19 AM

indian143 · modified on Friday, July 1, 2011 12:19 AM

Hi, Thanks for the reply, its not compared to any thing. But its taking 1 GB memory my architect said. And one more thing it could be data only that is taking more memory. Now, I will put the question in different way Can I tune my Linq and Lambda query to select less data and use less memory so that I can improve my functions performance? Below is the code. Thanks for any kind of help, thanks in advance.

public string GetQuarterlyUpdateTUCDataXml( int quarterlyUpdateJobId, int quarterlyUpdateSubscriptionId)
{
string strQuarterlyUpdateDeltaReport = null;

        var quarterlyUpdateSubscription = parallelDataContext.teQUTUDatas.Where(c => c.QuarterlyUpdateJobID ==
 quarterlyUpdateJobId && c.QuarterlyUpdateSubscriptionID == quarterlyUpdateSubscriptionId).ToList();

        if ((quarterlyUpdateSubscription != null) &&

(quarterlyUpdateSubscription.Count > 0))
{
byte[] encryptedBytes = (from a in quarterlyUpdateSubscription
join
b in parallelDataContext.teBinaryDatas on

a.DeltaReportBinaryDataID equals b.BinaryDataID
select b.Data).FirstOrDefault().ToArray();

            strQuarterlyUpdateDeltaReport = (encryptedBytes != null) ?
              objCommon.GetDecryptedReportString(encryptedBytes) : ""; //objCommon.GetDecryptedReport(encryptedBytes); //                       
        }
        
        return strQuarterlyUpdateDeltaReport;
    }

Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA

Not Active

There was no need to post the code again. As I stated, there may be a way to reduce the size but I have no idea of the data structure or your requirements. Maybe your architect would be the one to ask

I know the language. I've read a book. - _Madmatt

dasblinkenlight

For a method returning a single string, this is rather wasteful: you bring in a list of items in memory (with a call of ToList()), then run a join, but then you throw away all but the first item of the result! You don't need a list of teQUTUData objects in memory: all you use is their DeltaReportBinaryDataID column, so select only that column in your first LINQ query (add .Select(a => a.DeltaReportBinaryDataID) to the first query). Next, you don't need a join in your second query: it's an in-list lookup. Rewrite your query to run a select from parallelDataContext.teBinaryDatas, where quarterlyUpdateSubscription contains b.BinaryDataID. This should do the trick. Good luck!

indian143

Hi, Your post has helped me greatly. I really appreciate your help. I am really greatfull for this help. You not only helped me in solving the problem but also helped me in understanding the usage of select in the linq. Thats where real puzzle there I think so. Once select is understood then Linqs become easy I think. In all the ways this help is really appreciable. Thanks a lot again.

Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA

dasblinkenlight

You are welcome!

Philippe Mori

Download LINQPad a free tool that will allows you to test your queries. This tool is really helpfull to optimize your query because it will display the time it takes to executed code. Also, it is possible to see the generated SQL. Anytime the SQL seems way too complex or the number of queries is large, you should try to see it it can be improved. http://www.linqpad.net/[^] Also since the tool nicely display the result, it is possible to validate visually that your queries make senses. For example if hte number of returned rows is bigger than expected, a WHERE clause might be missing. This tool is a must have. It may it easy to compare alternative queries. Also a lot of sample come with the tool and show different thing like how to do grouping, how to test NULL value in a database and more. Sometime a simple difference like exchanging SingleOrDefault() with FirstOrDefault() can make a huge difference in the query even though the result would be the same (apart that in one case it would have been check that a single item exists. Also it will help to spot iterative requests. That is when a query would cause a first request then repeat another sub-request for each element. Sometimes, it might be preferable to explicitly make 2 simpler requests that LINQ to SQL would be able to translate in 1 SQL request each instead of a big request that would result in mlutiple request.

Philippe Mori

Lost User

LINQ queries are executed only when results are required to be used in the code.

ToList()

and

ToArray()

force the queries to execute since the results are to be stored in the respective list and array. If the query is large enough, then it sure will consume huge memory. As dasblinkenlight already suggested, rewrite your query to select only the rows and columns that you actually require instead of loading the entire table and reading the required row/column.