Friday, 26 October 2018

Task-based Asynchronous Pattern (TAP)

Parallelizing with TAP:
However, the biggest benefit of using TAP or async/await in general is the possibility of the creation of different workflows within the same method. What this means is that we can combine multiple Tasks in several ways, and by doing so, we are changing the flow of our method and our application. For example, consider that you have this situation in your application:
This would mean that tasks would be executed sequentially. The second task would run only after the first one is finished, and the third task would run only after the second one is finished.
async Task RunWorkflow()
{
       var stopwatch = Stopwatch.StartNew();

       // Task 1 takes 2 seconds to be done.
       await Task.Delay(2000)
              .ContinueWith(task => Completed("Task 1", stopwatch.Elapsed));

       // Task 2 takes 3 seconds to be done.
       await Task.Delay(3000)
              .ContinueWith(task => Completed("Task 2", stopwatch.Elapsed));

       // Task 3 takes 1 second to be done.
       await Task.Delay(1000)
              .ContinueWith(task => Completed("Task 3", stopwatch.Elapsed));

       // Print the final result.
       Completed("Workflow: ", stopwatch.Elapsed);
       stopwatch.Stop();
}

void Completed(string name, TimeSpan time)
{
       Console.WriteLine($"{name} : {time}");
}
So, in here, we assumed that Task 1 would take two seconds to finish the job, Task 2 would take three seconds and finally Task 3 would take one second to be done. After that, we used the ContinueWith method, which is basically creating another task which will execute asynchronously once the Task is done. Also, keep in mind that I used .NET Core 2.0 for this application. It is important to use .NET Core or .NET Framework 4.7 in order for this example to work. The result looks like something like this:
There it is, we achieved sequential workflow. Now, this is something we don’t usually want to happen in our application. More often we want our application to run multiple parallel tasks and continue with execution only when they are all done, such as seen on example:
In order to make this work, we can exploit the WhenAll method of the Task class, that we had the chance to encounter in the previous blog post. That would look like something like this:
async Task RunWorkflow()
{
       var stopwatch = Stopwatch.StartNew();

       // Task 1 takes 2 seconds to be done.
       var task1 = Task.Delay(2000)
              .ContinueWith(task => Completed("Task 1", stopwatch.Elapsed));

       // Task 2 takes 3 seconds to be done.
       var task2 = Task.Delay(3000)
              .ContinueWith(task => Completed("Task 2", stopwatch.Elapsed));

       // Task 3 takes 1 second to be done.
       var task3 = Task.Delay(1000)
              .ContinueWith(task => Completed("Task 3", stopwatch.Elapsed));

       await Task.WhenAll(task1, task2, task3);

       // Print the final result.
       Completed("Workflow: ", stopwatch.Elapsed);
       stopwatch.Stop();
}

void Completed(string name, TimeSpan time)
{
       Console.WriteLine($"{name} : {time}");
}
Here, instead of awaiting every Task, we used a different tactic. Firstly, we created an instance of every task and then used the WhenAll method of the Task class. This method creates a task that will finish only when all of the defined tasks have finished. Here is how it looks like when we run this code:
Now, this is much better, as you can see Tasks run in parallel and the whole workflow lasted as long as operation Task 2. Yet sometimes we want to finish the workflow as soon as the first Task is done. Imagine if you are pulling data from multiple servers and you want to continue processing as soon as the first one responds. We can do that using the Task class as well, or to be more specific, the WhenAny method of the Task class. The workflow would look like something like this:
In our case Task 3 will first finish the execution so we want to continue processing as soon as that task is done. The code won’t differ too much from the previous example:
async Task RunWorkflow()
{
       var stopwatch = Stopwatch.StartNew();

       // Task 1 takes 2 seconds to be done.
       var task1 = Task.Delay(2000)
              .ContinueWith(task => Completed("Task 1", stopwatch.Elapsed));

       // Task 2 takes 3 seconds to be done.
       var task2 = Task.Delay(3000)
              .ContinueWith(task => Completed("Task 2", stopwatch.Elapsed));

       // Task 3 takes 1 second to be done.
       var task3 = Task.Delay(1000)
              .ContinueWith(task => Completed("Task 3", stopwatch.Elapsed));

       await Task.WhenAny(task1, task2, task3);

       // Print the final result.
       Completed("Workflow: ", stopwatch.Elapsed);
       stopwatch.Stop();
}

void Completed(string name, TimeSpan time)
{
       Console.WriteLine($"{name} : {time}");
}
In a nutshell, we are just calling the WhenAny method instead of the WhenAll method, which essentially creates a task that will finish when any of the supplied tasks have completed. Running this code will print this on the output:
We can see that the output looks a tad weird. Still, it makes much sense. What happened is that Task 3 has finished, which caused our WhenAll task to complete. This, on the other hand, caused our Workflow to be completed and with that our two remaining tasks as well.
You might ask yourself “Well, is there a way to create a workflow that will not cut out those two last tasks?” and that would be a good question. What we would like to see is something like this:
This scenario is possible in the cases when we want to do a small amount of processing after each of the different tasks has been finished. This can be achieved again by using the WhenAll method of the Task class and a simple loop:
async Task RunWorkflow()
{
       var stopwatch = Stopwatch.StartNew();

       // Task 1 takes 2 seconds to be done.
       var task1 = Task.Delay(2000)
              .ContinueWith(task => Completed("Task 1", stopwatch.Elapsed));

       // Task 2 takes 3 seconds to be done.
       var task2 = Task.Delay(3000)
              .ContinueWith(task => Completed("Task 2", stopwatch.Elapsed));

       // Task 3 takes 1 second to be done.
       var task3 = Task.Delay(1000)
              .ContinueWith(task => Completed("Task 3", stopwatch.Elapsed));

       var tasks = new List<Task>() { task1, task2, task3 };

       while (tasks.Count > 0)
       {
              var finishedTask = await Task.WhenAny(tasks);
              tasks.Remove(finishedTask);
              Console.WriteLine($"Additional processing after finished Task! {tasks.Count} more tasks remaining!");
       }

       // Print the final result.
       Completed("Workflow: ", stopwatch.Elapsed);
       stopwatch.Stop();
}

void Completed(string name, TimeSpan time)
{
       Console.WriteLine($"{name} : {time}");
}
So when we run this code we will get this output:







Thursday, 25 October 2018

Difference Between Temporary Table and Table Variable in Sql Server

Both Temporary Tables (a.k.a # Tables) and Table Variables (a.k.a @ Tables) in SQL Server provide a mechanism for Temporary holding/storage of the result-set for further processing.
Below table lists out some of the major difference between Temporary Table and Table Variable. Each of these differences are explained in-detail with extensive list of examples in the next articles in this series which are listed above.
1. SYNTAX
Below is the sample example of Creating a Temporary Table, inserting records into it, retrieving the rows from it and then finally dropping the created Temporary Table.
-- Create Temporary Table
CREATE TABLE #Customer
(Id INT, Name VARCHAR(50))

--Insert Two records
INSERT INTO #Customer
VALUES(1,'Basavaraj')
INSERT INTO #Customer
VALUES(2,'Kalpana')

--Retrieve the records
SELECT * FROM #Customer

--DROP Temporary Table
DROP TABLE #Customer
Below is the sample example of Declaring a Table Variable, inserting records into it and retrieving the rows from it.
-- Create Table Variable
DECLARE @Customer TABLE
(
 Id INT,
 Name VARCHAR(50)  
)

--Insert Two records
INSERT INTO @Customer
VALUES(1,'Basavaraj')
INSERT INTO @Customer
VALUES(2,'Kalpana')

--Retrieve the records
SELECT * FROM @Customer
2. MODIFYING STRUCTURE
Temporary Table structure can be changed after it’s creation it implies we can use DDL statements ALTER, CREATE, DROP.
Below script creates a Temporary Table #Customer, adds Address column to it and finally the Temporary Table is dropped.

--Create Temporary Table
CREATE TABLE #Customer
(Id INT, Name VARCHAR(50))
GO
--Add Address Column
ALTER TABLE #Customer
ADD Address VARCHAR(400)
GO
--DROP Temporary Table
DROP TABLE #Customer
GO
Table Variables doesn’t support DDL statements like ALTER, CREATE, DROP etc, implies we can’t modify the structure of Table variable nor we can drop it explicitly.
3. STORAGE LOCATION
One of the most common MYTH about Temporary Table & Table Variable is that: Temporary Tables are created in TempDB and Table Variables are created In-Memory. Fact is that both are created in TempDB, below Demos prove this reality.
4. TRANSACTIONS
Temporary Tables honor the explicit transactions defined by the user.
Table variables doesn’t participate in the explicit transactions defined by the user.
5. USER DEFINED FUNCTION
Temporary Tables are not allowed in User Defined Functions.
Table Variables can be used in User Defined Functions.
6. INDEXES
Temporary table supports adding Indexes explicitly after Temporary Table creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint.
Table Variables doesn’t allow the explicit addition of Indexes after it’s declaration, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined during Table Variable declaration.
7. SCOPE
There are two types of Temporary Tables, one Local Temporary Tables whose name starts with single # sign and other one is Global Temporary Tables whose name starts with two # signs. Scope of the Local Temporary Table is the session in which it is created and they are dropped automatically once the session ends and we can also drop them explicitly. If a Temporary Table is created within a batch, then it can be accessed within the next batch of the same session. Whereas if a Local Temporary Table is created within a stored procedure then it can be accessed in it’s child stored procedures, but it can’t be accessed outside the stored procedure. Scope of Global Temporary Table is not only to the session which created, but they will visible to all other sessions. They can be dropped explicitly or they will get dropped automatically when the session which created it terminates and none of the other sessions are using it.
Scope of the Table variable is the Batch or Stored Procedure in which it is declared. And they can’t be dropped explicitly, they are dropped automatically when batch execution completes, or the Stored Procedure execution completes.