Code efficiency/optimization

Question

Not really sure if this is a proper question or not, but I figure that I'll give it a go and see what kind of answers pop up.

We're at the point in our development that we are going on to User Acceptance Testing, and one of the things the users have found to be a little lacking was the speed in which tabs are loading after a search result is selected. I've implemented logging methods and have come up with a few culprits as to the methods and data retrieval/manipulation that are causing the perceived slowness. The below is the biggest issue. The purpose of the method is to select all payments received towards a policy or any sub-policies, group them together by both due date and paid date, and then return a GroupedClass that will sum the amounts paid towards the whole policy. I'm wondering if there's any way this can be made more efficient. I've noticed that working with this old UniVerse data that things tend to break if they aren't cast .AsEnumerable() before being utilized:

var mc = new ModelContext();
var policy = mc.Polmasts.Find("N345348");
        var payments =
            mc.Paymnts.Where(p => p.POLICY.Contains(policy.ID)).GroupBy(p => new { p.PAYDUE_, p.PAYPD_ }).Select(
                    grp =>
                    new GroupedPayments
                        {
                            PAYPD_ = grp.Key.PAYPD_,
                            PAYDUE_ = grp.Key.PAYDUE_,
                            AMOUNT = grp.Sum(a => a.AMOUNT),
                            SUSP = grp.Sum(a => a.SUSP)
                        }).AsEnumerable().OrderByDescending(g => g.PAYDUE_).Take(3);

I apologize, I'm actually not familiar with the term? Self taught, first job and all. — Allison Steranko, Sep 17 '12 at 16:19
If you want a more accurate view of where the cpu time is going try this tool out, you can download a free trail, it is excellent - http://www.red-gate.com/products/dotnet-development/ants-performance-profiler/?utm_source=google&utm_medium=cpc&utm_content=unmet_need&utm_campaign=antsperformanceprofiler&gclid=CJ3564mCvbICFeTHtAodeT0Ajg — Eamonn McEvoy, Sep 17 '12 at 16:19
try not using Find but mc.Polmast.Where(x => x.FK == "N345348").First(); It fixes performance issue for me. — tschmit007, Sep 17 '12 at 16:22
@tschmit007 in the actual applictaion it is a .Where that takes a textbox input, I just used a Find for my own troubleshooting app — Allison Steranko, Sep 17 '12 at 16:25

score 2 · Accepted Answer · answered Sep 18 '12 at 08:16

I've noticed that working with this old UniVerse data that things tend to break if they aren't cast .AsEnumerable() before being utilized

This goes to the root of your problems. By saying AsEnumerable, you are forcing all records in the sequence at that point to be brought down, before you sort and take the first three. Obviously, this will get slower and slower for more data.

Fixing this could be difficult, given what you say. In general, LINQ providers provide varying amounts of functionality in terms of what can be evaluated on the server and what can't. From your above comment, it sounds like LINQ-to-UniVerse doesn't do particularly well at doing things on the server.

For example, I would expect any good database LINQ provider to be able to do (using made-up definitions)

context.Products.Where(p => p.Type == 4).OrderBy(p => p.Name)

on the server; however, your code above is more taxing. Try splitting it into smaller pieces and establishing if it's possible to get the server to do the sort and Take(3). It might be that the best thing to do is to one query (which can be done on the server) to get the bottom three PAYDUE_ values, then another to actually get the amounts for those dates, pulling all relevant records down to the client.

Going back to this months later. We decided to do daily extracts of the UV data to a SQL backing store and we've eliminated 99% of our speed issues, as well as cleaning up a lot of the unneeded AsEnumerable and the like — Allison Steranko, Nov 26 '12 at 18:40

score 0 · Answer 2 · answered Sep 17 '12 at 16:49

0

Assuming you're running against SQL Server I would enable profiling, Linq has a habit of not producing the SQL you'd like it to. It's much more likely that the slowdown is from bad SQL than from in memory operations.

answered Sep 17 '12 at 16:49

Fiacc

1,324
1
15
24

Running against IBM UniVerse file system, actually – Allison Steranko Sep 17 '12 at 17:27
Are all the records in memory? Or is there some sort of disk IO going on. Maybe try splitting it up into different statements, so do the where first and time it and then the group by and then the orderby, at least you'll know where the slowdown is – Fiacc Sep 17 '12 at 17:43

Code efficiency/optimization

2 Answers2