StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POVery Slow Linq to SQL Select Performance on WP7
text
Body
copied!<p>I'm having a problem with Linq to SQL performance on Windows Phone 7, but I'm really not sure what I'm doing wrong (I have almost no experience with Linq to SQL, and the more I read, the more confused I'm getting <em>sigh</em>).</p> <p><strong>Background</strong></p> <p>I have a local SQL CE database with five tables, two columns in each (<code>int</code> primary key & <code>nvarchar</code> value, plus indexes), and about a 100,000 entries in each table. The DB is around 20MB in size and is implemented following the guidelines in Microsoft's own MVVM local database sample.</p> <p><strong>Problem</strong></p> <p>After simplifying as much as I can, I have a query in my view model that looks like this:</p> <pre><code>var query = ( from t1 in db.table1 join t2 in db.table2 on t1.id equals t2.id join t3 in db.table3 on t1.id equals t3.id join t4 in db.table4 on t1.id equals t4.id join t5 in db.table5 on t1.id equals t5.id where SqlMethods.Like(t5.value, "%"+searchTerm+"%") select new Results { Field1 = t1.value, Field2 = t2.value, Field3 = t3.value, Field4 = t4.value, Field5 = t5.value, } ).Take(100); SearchResults = new ObservableCollection<Results>(query); </code></pre> <p>This products the following SQL:</p> <pre><code>SELECT TOP (100) [t0].[value] AS [Field1], [t1].[value] AS [Field2], [t2].[value] AS [Field3], [t3].[value] AS [Field4], [t4].[value] AS [Field5] FROM [table1] AS [t0], [table2] AS [t1], [table3] AS [t2], [table4] AS [t3], [table5] AS [t4] WHERE ([t4].[value] LIKE @p0) AND ([t0].[id] = [t4].[id]) AND ([t0].[id] = [t3].[id]) AND ([t0].[id] = [t2].[id]) AND ([t0].[id] = [t1].[id]) </code></pre> <p>The problem is, when the search term is very specific (only one result), it averages around <strong>5 seconds</strong> to execute. This is before I add any of the other requirements, like multiple where clauses, ranking, ordering etc. Even if I search for what I know to be the first row in the database, it still takes around 5 seconds.</p> <p>If I change approach and search for something very common (like 'the'), it only takes around <strong>100ms</strong> to execute. I know <code>Like</code> with wildcards is more complicated than a straight <code>==</code> comparison, but I don't know why the performance is so different.</p> <p>(I know it's a useless comparison, as they're apples and oranges, but I previously executed similar queries on the same database written in MySQL, and they consistently got results in around 0.3-0.4s regardless of what I searched for).</p> <p>Am I missing something really obvious? I've followed Microsoft's examples and read many tutorials online, but I can't find a reason why this query is so slow. Many thanks in advance for any advice you can offer.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload