Improve query performance in mvvm application

Question

I have a wpf mvvm application that records phone call queries and advice.

I am trying to query the call records for information in related entities, but performance is slow.

Call--1:M--CallQuery--M:M--AnswerDoc--1:M--AnswerDocSection
                    1:M|M:1
                CallQueryAnswerDoc

So, a call will have one or more queries (a query is a question and its answer), and (the answer to) a query will refer to 1 or more (max 2) AnswerDocsSections. Each AnswerDocSection comes from an AnswerDoc. The CallQueryAnswerDoc table is created to deal with the M:M join between the CallQuery and AnswerDoc tables.

To prepare an FAQ, I'm trying to query which calls used certain AnswerDocs and/or AnswerDocSections.

For my first approach, I retrieved an ObservableCollection of Calls, then looped through each call to collect an ObservableCollection of its CallQueries, and for each CallQuery, check if the AnswerDocID matches the searchText's AnswerDocID. If so, the Call is added to the returned list of calls.

In a database of about 5k calls, this takes well over ten minutes and proceeds through Calls at about 100 per 10 seconds.

So, next I tried to do it all in a linq query using a syntax like:

var qCQADS = callList.Where(c => 
               c.CallQueries.Any(cq => 
                 cq.CallQueryAnswerDocs.Any(cqad => 
                   cqad.AnswerDocSectionID == CallSearch.AnswerDocSectionID)));

but surprisingly (to me, anyway) this seems to take as much time as newing up ObservableCollections.

I can do this query in sql and get results in under 1 second.

SQL query

SELECT dbo.AnswerDoc.Code, dbo.AnswerDocSection.Title, dbo.Call.CallID,
       dbo.Call.CallDate, dbo.Call.RegionID, dbo.Call.CallSubject
FROM dbo.AnswerDocSection
INNER JOIN dbo.CallQueryAnswerDocSection ON dbo.AnswerDocSection.AnswerDocSectionID = 
  dbo.CallQueryAnswerDocSection.AnswerDocSectionID
INNER JOIN dbo.CallQuery ON dbo.CallQueryAnswerDocSection.CallQueryID = 
    dbo.CallQuery.CallQueryID
INNER JOIN dbo.AnswerDoc ON dbo.AnswerDocSection.AnswerDocID = dbo.AnswerDoc.AnswerDocID
INNER JOIN dbo.Call ON dbo.CallQuery.CallID = dbo.Call.CallID
WHERE (dbo.AnswerDoc.Code = N'General')
  AND (dbo.AnswerDocSection.Title = N'Invalid Username')

How do I approach this performance using wpf?

5000 rows is *nothing*, are you sure that you have an index on your relevant tables? When dealing with such low numbers of data, I usually prefer loading *all* data into a cache object in my application to avoid roundtrips to the DB. — Rob, Jan 03 '18 at 05:52
In agreeance with @Robert's suggestion, you could use `Include` as described [here](https://stackoverflow.com/questions/24120039/how-to-include-nested-child-entity-in-linq). — Keyur PATEL, Jan 03 '18 at 06:03
@Robert : All IDs are primary keys. As I said, running the sql query in Sql Server takes sub 1 second. An `ObservableCollection` of calls is passed to my method (the callList in the linq example) and my code just loops through the collection. I'm originally back-end and I think that side of things is OK. — mcalex, Jan 03 '18 at 06:36
@KeyurPATEL I think you might be on to something (and shockingly, I think I've made this error before on SO). Do you want to make your comment an answer? — mcalex, Jan 03 '18 at 06:39
In all my `Include()` experience I've never had to go more than 2 levels deep, I'll try my best though :) — Keyur PATEL, Jan 03 '18 at 06:44

score 0 · Accepted Answer · answered Jan 03 '18 at 06:50

0

I'm not 100% sure about the syntax as I'm not near a computer to test it. Also, I've never had to Include() nested entities deeper than 1 level before, but here goes:

You could first load everything into local memory (as Robert suggests in comments, it should be fine for smaller number of rows, e.g. around 5000).

var qCQADS_all = callList.Include(x => x.CallQueries.Select(q => q.CallQueryAnswerDocs)).ToList();

OR

var qCQADS_all = callList.Include("CallQueries.CallQueryAnswerDocs").ToList();

Then you can perform your nested Any() which should take significantly less time than "over ten minutes".

qCQADS = qCQADS_all.Where(c => 
           c.CallQueries.Any(cq => 
             cq.CallQueryAnswerDocs.Any(cqad => 
               cqad.AnswerDocSectionID == CallSearch.AnswerDocSectionID)));

answered Jan 03 '18 at 06:50

Keyur PATEL

2,299
1
15
41

Yes, the original query that retrieves the calls didn't `include` related items. Results are now populating in ~10secs. A little more than I'd expect, but I'm happy to blame this on wpf. Many thanks. – mcalex Jan 03 '18 at 07:06
Ideally I know you'd want to reduce the time even further. If so, you could either load `CallQueries` and `CallQueryAnswerDocs` into memory and then use an old school `foreach` loop, rather than the whole `Include()` block. Personally I use EntityFramework which also allows me to run SQL queries on the database and retrieve records, which would've been very helpful in your case since you already know the SQL takes ~1 second. – Keyur PATEL Jan 03 '18 at 07:09

Improve query performance in mvvm application

1 Answers1