Print Page | Close Window

CancellationToken and SQL LogFile

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2012
Forum Discription: For .NET 4.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=3854
Printed Date: 25-Oct-2025 at 11:00pm


Topic: CancellationToken and SQL LogFile
Posted By: cefernan
Subject: CancellationToken and SQL LogFile
Date Posted: 13-Dec-2012 at 4:10am
Hello,

I don't know how CancellationToken works, so I'm doing some tests and I'm trying to cancel the execution of my queries. Actually, I have a search component that make searches while the user is typing and I wish my component cancel the previous query (if not finished yet) when the user type a new key. For example, if the user type quickly "Anna", the application should search "Anna" and not the partials "A", "An", "Ann". 

In the KeyUp event, I call this method:
private async void Search(string textSearch)
{
try
{
if (!string.IsNullOrWhiteSpace(textSearch))
{
foreach (var item in _cancellationTokenSourceList)
item.Cancel();
_cancellationTokenSourceList.Clear();

CancellationTokenSource cancellationTokenSource = new CancellationTokenSource();
_cancellationTokenSourceList.Add(cancellationTokenSource);

var predicate = PredicateBuilder.Make(typeof(T), _fieldName, FilterOperator.Contains, textSearch);
var result = await _unitOfWork.Entities.FindAsync(f => f.Where(predicate).OrderBySelector(new SortSelector(_fieldName)).Take(PageSize), cancellationTokenSource.Token);
ItemsSource = new BindableCollection<T>(result);

_currentComponent.ShowPopup();
}
else
{
ItemsSource.Clear();
_currentComponent.ClosePopup();
}
}
catch (System.Exception)
{
//throw;
}
}

I don't know if it's working or not, because when I take a look in the log file I can see a bunch of the same query with differents where clauses. 
Sometimes I have exactly the sequence that the user typed:
1. WHERE (LOWER("Extent1"."Name") LIKE 'A%')
2. WHERE (LOWER("Extent1"."Name") LIKE 'An%')
3. WHERE (LOWER("Extent1"."Name") LIKE 'Ann%')
4. WHERE (LOWER("Extent1"."Name") LIKE 'Anna%')

Sometimes a strange sequence and repetead queries:
1. WHERE (LOWER("Extent1"."Name") LIKE 'An%')
2. WHERE (LOWER("Extent1"."Name") LIKE 'Ann%')
3. WHERE (LOWER("Extent1"."Name") LIKE 'Ann%')
4. WHERE (LOWER("Extent1"."Name") LIKE 'Anna%')

I don't know why this behavior happens and I don't know if the cancellation is happening or not because anything is mentioned in the log.

Regards.


Replies:
Posted By: kimj
Date Posted: 13-Dec-2012 at 11:03am
Since the debug log on the server contains these SQL statements, the async queries are not actually being cancelled.
 
There's unfortunately only a small window in which you can truly cancel a query.  We make one check of the cancellation token before the request is sent to the EntityServer, and once the WCF request to the server is made that can't be cancelled.  We also don't make any attempt to contact the server via another request to cancel the query request.  On the client, if cancellation has been requested before the server request completes we will see that and "cancel" client processing, marking the task as cancelled. 
 
I think that cancelling outstanding async queries may still be a good idea, but you probably need some sort of timer so you can also "coalesce" the queries.  This is an interesting thread on StackOverflow - http://stackoverflow.com/questions/6293817/async-search-event-handling - http://stackoverflow.com/questions/6293817/async-search-event-handling  and may give you some ideas.
 
 

Posted By: mgood
Date Posted: 13-Dec-2012 at 11:21am
First off that call to FindAsync looks rather strange. I think I know what you are trying to do, so it should be like this:

var result = await _unitOfWork.Entities.FindAsync(q => q.Take(PageSize), f => f.Where(predicate), q => q.OrderBySelector(new SortSelector(_fieldName)), cancellationTokenSource.Token);

Second, once the server is executing a query, the server operation can't actually be cancelled. You can still cancel, but the query on the server will finish and the client then simply ignores the result and cancels the Task returned by FindAsync. When a Task gets cancelled, await throws a TaskCancelledException, so you can set a breakpoint inside of your catch to see if any of your tasks are actually getting cancelled. Keep in mind if the queries are very fast, the window to cancel is very short. Cancellation makes a lot more sense on long running queries, so the user can continue with something else, but again the server operation can't actually be cancelled. It simply lets the client continue and ignores the response from the server once it comes back.

A better approach here is to have a sequence #, so you know which result is the most recent and ignore any older results if they come in out of order. With the nature of asynchronous calls you have no guarantee that they finish in the same order you invoked them, so a sequence # will help the client figure out which result belongs to which invocation. 

Posted By: cefernan
Date Posted: 13-Dec-2012 at 12:38pm
Got it, thank you both.

I'll take a look in this link Kim.

Marcel, your FindAsync didn't work on that way, but likewise I'll review my code. About que cancellation itself, the concepts about server can't cancel queries, and sequence # approach, I'll study the best solution to my case and try implement it without losing performance.

Posted By: mgood
Date Posted: 13-Dec-2012 at 7:07pm
Oh, yeah, sorry. Should have typed it out in Visual Studio. Not sure what I was thinking. Here you go:

var result = await _unitOfWork.Entities.FindAsync(q => q.Take(PageSize), cancellationTokenSource.Token, predicate.ToPredicate<T>(), q => q.OrderBySelector(new SortSelector(_fieldName)));

Posted By: cefernan
Date Posted: 14-Dec-2012 at 2:49am
Hey Marcel,

I'd appreciate your advice, but in that way doesn't solve my problem. Because, it will generate a sql code doing the where, rownum and order by clause in the same "level". I need first a query responsible to do my where and order by clause, and after the restriction of number of rows (rownum). The result needs to be a query with a subquery.

E.G.:
SELECT ...
  FROM (SELECT ...
          FROM CRMALL_SILVERLIGHT."Filter" "Extent1"
         WHERE LOWER("Extent1"."Name") LIKE 'test%'
         ORDER BY "Extent1"."Name" ASC
    )  "top"
 WHERE ROWNUM <= 10

That why my FindAsync call is not so strange to me.

Regards.

Posted By: mgood
Date Posted: 14-Dec-2012 at 10:24am
I must be missing something. Based on your code, you are filtering, then sorting and then you want to return the top x rows. Is that correct? So, a simple example based on my suggestion would look like this. I want to find customers in London, order them by the company name and return the top 25. 

var unitOfWork = new UnitOfWork<Customer>(new EntityManagerProvider<NorthwindIBEntities>());
var customers = await unitOfWork.Entities.FindAsync(
    q => q.Take(25), x => x.City == "London", q => q.OrderBy(x => x.CompanyName));

The above example generates the following T-SQL. That's the actual T-SQL EF 5 generates, just leaving out all the column names.

SELECT TOP (25) ...
FROM [dbo].[Customer] AS [Extent1] 
WHERE N'London' = [Extent1].[City] 
ORDER BY [Extent1].[CompanyName] ASC

This query does exactly what I described above in the most optimal fashion. The WHERE clause comes first, then the ORDER BY and finally the TOP (25). Why exactly do you need a subquery?

Posted By: mgood
Date Posted: 14-Dec-2012 at 10:55am
Oh, I just realized what I'm missing. I skipped over your SQL. I'm just now realizing that you are using Oracle. Looks like the fact that the repository puts the take before the order by causes issues with Oracle. I wasn't aware of that. With SQL Server it doesn't matter if the Take comes before the OrderBy, but apparently it matters if you use Oracle. That's a bit concerning. 

Posted By: cefernan
Date Posted: 14-Dec-2012 at 11:10am
Based on your code, you are filtering, then sorting and then you want to return the top x rows. Is that correct?
Yes, but I work with Oracle Database and ROWNUM doesn't work like TOP clause in SQLServer.

SELECT ...
  FROM (SELECT ...
          FROM CRMALL_SILVERLIGHT."Filter" "Extent1"
         WHERE LOWER("Extent1"."Name") LIKE 'test%'
         ORDER BY "Extent1"."Name" ASC
    )  "top"
 WHERE ROWNUM <= 10

This query will sort "Filter" by "Name" ascending and then return the first ten records it encounters (the top-ten records). 

If I had put your code, the sql would be:
SELECT ...
  FROM CRMALL_SILVERLIGHT."Filter" "Extent1"
 WHERE LOWER("Extent1"."Name") LIKE 'test%'
   AND ROWNUM <= 10
 ORDER BY "Extent1"."Name" ASC

For Oracle it means, return the first ten records it encounters and then sort them by "Name".

So, the results will be different, let me try to explain. ROWNUM will be assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. So, this why we need a subquery in Oracle, first we need the sortered results and after return the first N records. 

That's it. I don't know if I was clear.

Posted By: cefernan
Date Posted: 14-Dec-2012 at 11:13am
Originally posted by mgood

Oh, I just realized what I'm missing. I skipped over your SQL. I'm just now realizing that you are using Oracle. Looks like the fact that the repository puts the take before the order by causes issues with Oracle. I wasn't aware of that. With SQL Server it doesn't matter if the Take comes before the OrderBy, but apparently it matters if you use Oracle. That's a bit concerning. 
Exactly!

Posted By: mgood
Date Posted: 15-Dec-2012 at 11:42am
Just a heads up. The upcoming v2.2 release of Cocktail will have proper support for Skip and Take. Should be available on NuGet around Christmas.

Posted By: cefernan
Date Posted: 17-Dec-2012 at 2:23am
Nice, I'll update as soon as you release.


Print Page | Close Window