current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

This query was written in the our system some time ago but the performance of this query is getting poor with a little increase in data. My investigation shows (CodeCount) where query firing another subquery causing a massive delay in execution. I need to optimize this Linq query. any help will be highly appreciated

  from batch in Context.VoucherCodeBatch.ToList()
                    join type in Context.VoucherCodeType on batch.VoucherTypeId equals type.VoucherTypeId
                    join voucher in Context.Voucher on batch.VoucherCodeBatchId equals voucher.VoucherCodeBatchId

                    where batchIds.Contains(batch.BatchCode)
                    group new
                    {
                        batch.BatchCode,
                        batch.CreationDate,
                        type.VoucherTypeName,
                        voucher.AllowedCount,
                        voucher.ValidFrom,
                        voucher.ValidTo,
                        batch.VoucherCodeBatchId,
                        voucher.VoucherCode
                    }
                        by new { batch.BatchCode }
                        into uniquebatch
                        select new Batch
                        {
                            BatchCode = uniquebatch.FirstOrDefault().BatchCode,
                            CreationDate = uniquebatch.FirstOrDefault().CreationDate,
                            TimesAllowed = uniquebatch.FirstOrDefault().AllowedCount,
                            ValidFrom = uniquebatch.FirstOrDefault().ValidFrom,
                            CodeCount = ((from c in Context.Voucher.ToList()
                                          where
                                              c.VoucherCodeBatchId ==
                                              uniquebatch.FirstOrDefault().VoucherCodeBatchId
                                          select c).Count()),
                            ValidTo = uniquebatch.FirstOrDefault().ValidTo,
                            CodeType = uniquebatch.FirstOrDefault().VoucherTypeName,
                            VoucherCodeBatchId = uniquebatch.FirstOrDefault().VoucherCodeBatchId
                        });
share|improve this question
3  
That ToList() in there means that this query is requesting all Voucher rows from the database and filters in memory. Try removing it. –  Daniel Hilgarth Jun 4 '13 at 11:43
    
I would recommend doing such queries in SQL on the DB side. –  Ewald Stieger Jun 4 '13 at 11:46
    
I agree with Ewald. Maybe even making it into a View that you can then select on much easier in LINQ. A lot of times, just because you can do it in LINQ doesn't mean it's the best way to do it. –  Garrison Neely Jun 4 '13 at 17:15

1 Answer 1

up vote 1 down vote

The first big problem is the ToList() right in front of an ObjectSet<> (the table collection in EF).

Never do that, the ToList() force EF to bring all data to memory before process the query. (Like @Daniel Hilgarth coments).

Other detail is use a get property in front of FirstOrDefault() like in line:

BatchCode = uniquebatch.FirstOrDefault().BatchCode,

Use First() instead FirstOrDefault in this case. like:

BatchCode = uniquebatch.First().BatchCode,

Your query will be like this:

from batch in Context.VoucherCodeBatch/*.ToList()*/
join type in Context.VoucherCodeType on batch.VoucherTypeId equals type.VoucherTypeId
join voucher in Context.Voucher on batch.VoucherCodeBatchId equals voucher.VoucherCodeBatchId
where batchIds.Contains(batch.BatchCode)
group new
    {
        batch.BatchCode,
        batch.CreationDate,
        type.VoucherTypeName,
        voucher.AllowedCount,
        voucher.ValidFrom,
        voucher.ValidTo,
        batch.VoucherCodeBatchId,
        voucher.VoucherCode
    }
by new { batch.BatchCode }
into uniquebatch
select ( delegate
    {
        // If you put a operation in a query that operation will be
        // processed all times. Bacause that i removed this line from
        // the where statement.
        var vcBatchId = uniquebatch.First().VoucherCodeBatchId;

        return new Batch
            {
                BatchCode = uniquebatch.First().BatchCode,
                CreationDate = uniquebatch.First().CreationDate,
                TimesAllowed = uniquebatch.First().AllowedCount,
                ValidFrom = uniquebatch.First().ValidFrom,
                CodeCount = ((
                    from c in Context.Voucher/*.ToList()*/
                    where c.VoucherCodeBatchId == vcBatchId
                    select c).Count()),
                ValidTo = uniquebatch.First().ValidTo,
                CodeType = uniquebatch.First().VoucherTypeName,
                VoucherCodeBatchId = uniquebatch.First().VoucherCodeBatchId
            }
    });

If this improvement not be good enough, You will need change this query to SQL. But I believe this improvement will perform much better.

share|improve this answer
    
Thanks for looking into this. What does mean by delegate. I exactly copied the above code and compiler is complaining about select(delegate) "type of the expression in the select clause is incorrect. Type inference failed in the call to 'Select' " –  user1071222 Jun 5 '13 at 9:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.