LINQ query to Azure SQL database timing out

Question

I'm querying my sql database which is in Azure (actually my web app is on Azure as well).

Every time I perform this particular query, there are ever changing errors (e.g. sometimes timeout occurs, sometimes it works perfectly, sometimes it takes extremely long to load).

I have noted that I am using the ToList method here to enumerate the query but I suspect that's why it is degrading.

Is there anyway I can fix this or make it better....or maybe just use native SQL to execute my query?.

I should also note in my webconfig my Database connection timeout is set to 30 seconds. Would this have any performance benefit?

I'm putting the suspect code here:

case null:    
lstQueryEvents = db.vwTimelines.Where(s => s.UserID == UserId)
                    .Where(s => s.blnHide == false)
                    .Where(s => s.strEmailAddress.Contains(strSearch) || s.strDisplayName.Contains(strSearch) || s.strSubject.Contains(strSearch))
                    .OrderByDescending(s => s.LatestEventTime)
                    .Take(intNumRecords)
                    .ToList();
                    break;

It's basically querying for the 50 records...I don't understand why it's timing out sometimes.

Try to get hold of the SQL statement that gets executed and run it though the SQL Profiler. I suspect that `.Contains()` call force table scans, which is why the query times out. — Anton Gogolev, Sep 22 '15 at 05:13

score 1 · Accepted Answer · edited May 23 '17 at 11:51

Here are some tips:

Make sure that your SQL data types matches types in your model

Judging by your code, types should be something like this:

UserID should be int (cannot tell for sure by looking at code);
blnHide should be bit;
strEmailAddress should be nvarchar;
strDisplayName should be nvarchar;
strSubject should be nvarchar;

Make use of indexes

You should create Non-Clustered Indexes on columns that you use to filter and order data.

In order of importance:

LatestEventTime as you order ALL data by this column;
UserID as you filter out most of data by this column;
blnHide as you filter out part of data by this column;

Make use of indexes for text lookup

You could make use of indexes for text lookup if you change your filter behaviour slightly and search text only in the start of column value.

To achieve that:

change .Contains() with .StartsWith() as it would allow index to be used.
create Non-Clustered Indexes on strEmailAddress column:
create Non-Clustered Indexes on strDisplayName column:
create Non-Clustered Indexes on strSubject column:

Try out free text search

Microsoft only recently have introduced full text search in Azure SQL. You can use that to find rows matching by partial string. This is a bit complicated to achieve using EF, but it is certainly doable.

Here are some links to get you started:

Entity Framework, Code First and Full Text Search https://azure.microsoft.com/en-us/blog/full-text-search-is-now-available-for-preview-in-azure-sql-database/

I tried adding the indexing and sure enough it sped up the query dramatically. I also used the `.StartsWith()` but it's not achieving what I initially wanted, the `.Contains()`. Is there any way to achieve the same functionality of `.Contains()`? — Rahul Kishore, Sep 23 '15 at 20:36
Unfortunately not. In this case you can think of index like about phone book. You can quickly find things only if you search by start of the persons surname. If you would like to search by middle part of surname, you would have to go through all the surnames in the book one on by one. — Kaspars Ozols, Sep 24 '15 at 05:01

score 0 · Answer 2 · answered Sep 22 '15 at 05:17

0

string.Contains(...) converted to WHERE ... LIKE ... sql-statement. Which is very expensive. Try to reform your query to avoid it. Plus, Azure SQL has it's own limitations (5 sec as far as I remember, but better check SLA) for query run, so it would generally ignore your web.config settings if they are longer.

answered Sep 22 '15 at 05:17

unconnected

991
1
10
21

Is there anyway I can change the query to NOT do a .Contains (i.e. like you said it does perform a LIKE..which I intended)...But it's strange, sometimes it times out sometimes it doesn't. – Rahul Kishore Sep 22 '15 at 05:22
Azure is cloud, so resources are shared between great ammount of apps, so the only thing garantee that you get some level of service. It can be higher when there're some spare resources, and at that time your query runs normally. If you append your table structure with indexes, maybe we can figure out how to query it faster. – unconnected Sep 22 '15 at 05:32

LINQ query to Azure SQL database timing out

2 Answers2