DevForce Community Forum : CancellationToken and SQL LogFile http://www.ideablade.com/forum/ This is an XML content feed of; DevForce Community Forum : DevForce 2012 : CancellationToken and SQL LogFile Thu, 14 May 2026 06:55:45 -700 Mon, 17 Dec 2012 02:23:35 -700 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 9.69 360 www.ideablade.com/forum/RSS_post_feed.asp?TID=3854 DevForce Community Forum http://www.ideablade.com/forum/forum_images/IdeaBlade_logo_tm.png http://www.ideablade.com/forum/ CancellationToken and SQL LogFile : Nice, I'll update as soon... http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15420#15420 Author: cefernan
Subject: 3854
Posted: 17-Dec-2012 at 2:23am

Nice, I'll update as soon as you release.]]> Mon, 17 Dec 2012 02:23:35 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15420#15420 CancellationToken and SQL LogFile : Just a heads up. The upcoming... http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15414#15414 Author: mgood
Subject: 3854
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.]]> Sat, 15 Dec 2012 11:42:08 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15414#15414 CancellationToken and SQL LogFile : Originally posted by mgoodOh,... http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15409#15409 Author: cefernan
Subject: 3854
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!
]]> Fri, 14 Dec 2012 11:13:12 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15409#15409 CancellationToken and SQL LogFile : Based on your code, you are... http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15408#15408 Author: cefernan
Subject: 3854
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.
]]> Fri, 14 Dec 2012 11:10:44 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15408#15408 CancellationToken and SQL LogFile : Oh, I just realized what I&#039;m... http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15407#15407 Author: mgood
Subject: 3854
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. ]]> Fri, 14 Dec 2012 10:55:43 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15407#15407 CancellationToken and SQL LogFile : I must be missing something. Based... http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15406#15406 Author: mgood
Subject: 3854
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?


]]> Fri, 14 Dec 2012 10:24:56 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15406#15406 CancellationToken and SQL LogFile : Hey Marcel,I&#039;d appreciate... http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15405#15405 Author: cefernan
Subject: 3854
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.
]]> Fri, 14 Dec 2012 02:49:08 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15405#15405 CancellationToken and SQL LogFile : Oh, yeah, sorry. Should have typed... http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15404#15404 Author: mgood
Subject: 3854
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)));
]]> Thu, 13 Dec 2012 19:07:34 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15404#15404 CancellationToken and SQL LogFile : Got it, thank you both.I&#039;ll... http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15403#15403 Author: cefernan
Subject: 3854
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.
]]> Thu, 13 Dec 2012 12:38:53 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15403#15403 CancellationToken and SQL LogFile : First off that call to FindAsync... http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15402#15402 Author: mgood
Subject: 3854
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. 
]]> Thu, 13 Dec 2012 11:21:57 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15402#15402 CancellationToken and SQL LogFile : Since the debug log on the... http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15401#15401 Author: kimj
Subject: 3854
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 and may give you some ideas.
 
 
]]> Thu, 13 Dec 2012 11:03:11 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15401#15401 CancellationToken and SQL LogFile : Hello,I don&#039;t know how CancellationToken... http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15400#15400 Author: cefernan
Subject: 3854
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.
]]> Thu, 13 Dec 2012 04:10:40 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=3854&PID=15400#15400