CODEWITHSUNDEEP
c#sqllinq-to-sqlwindows-phone-7
Superangel
Superangel

Reputation: 1349

Very Slow Linq to SQL Select Performance on WP7

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 sigh).

Background

I have a local SQL CE database with five tables, two columns in each (int primary key & nvarchar 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.

Problem

After simplifying as much as I can, I have a query in my view model that looks like this:

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);

This products the following SQL:

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])

The problem is, when the search term is very specific (only one result), it averages around 5 seconds 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.

If I change approach and search for something very common (like 'the'), it only takes around 100ms to execute. I know Like with wildcards is more complicated than a straight == comparison, but I don't know why the performance is so different.

(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).

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.

Upvotes: 2

Views: 1199

Answers (1)

TomTom
TomTom

Reputation: 62129

SIMPLE COMMON SENSE.

  • You run that on a low power phone and make a table scan query.

SqlMethods.Like(t5.value, "%"+searchTerm+"%")

means no index grabs, this is a table scan.

).Take(100);

Means: stop after 100 items are found.

Now, witha very common word ("the") this may mean only 100 items are processed at all. With a more uncommon word it may have to run half the table to get 100 items. Table scanning half that database will take time. Simlpe.

In general, sql is badly prepared for word parsing in texts- this is why real sql server ahs full text indexing. Running that (%word%) on a low pwoer hardware (wp7= naturally is slow.

There is absolutely nothing in here that points to LINQ being a problem. LINQ transaltes that likely into a quite efficient SQL query within the perforamnce boundaries you define in the query - which is the worst thing you can do to a database, sadly.

Upvotes: 3

Related Questions