Tuesday, April 17, 2012

SQL Temporary Tables vs. Table Variables

If you ever encountered the need to perform some calculations in a stored procedure, this one’s for you J
Today I am going to talk about the options we have to perform such calculations within side tables in the database.

Basically, we have 3 alternative ways to store such data-
Temporary Tables (#Table), Table Variables (@Table) and Permanent Tables (Table).

Usually, the most intuitive thing to do is to use a temporary table, without giving it much thought in terms of performance. I must admit that I do it myself from time to time. Perhaps the reason for that is that temporary tables have been around longer. But, are they really the best thing to use?
As you’ve probably already guessed, the answer here is not unequivocally. All 3 alternatives have their advantages and disadvantages. So let’s look into it…

Some Basic Facts:
Table variables are pretty similar to temporary tables in concept, but in spite of these similarities, there are also some notable differences between them.
Let’s have a look:

·         Temp tables are essential mostly for data manipulation, whereas table variables were designed primarily for the use of table-valued functions.
·         Temp tables can be altered with DDL statements but table variables can't (for example: You can’t create a Nonclustered Index on a table variable).
·         Transactions on table variables are usually shorter than transactions involving temp tables and require less locking.
·         SQL Server creates statistics for temp tables, which eventually helps the query optimizer to choose the relevant execution plan, as opposed to table variables. This causes less recompiles of stored procedures involving table variables.

Great! So let’s always use table variables. Seems the least expensive, doesn’t it?
Mmm… Not exactly. I once heard that when something sounds too good to be true, it probably is. As a "rule of thumb" I can say that in simple queries we would rather use table variables, whereas in long/complex ones we prefer temp tables.
In the following example I will show you why:

Example:
I’ve performed 2 tests, which count the amount of even numbers in every table and calculates the execution time.
I did this for 2 types of calculations – Simple and Joined.
These tests will be executed first on small amounts of data and then on large amounts of data, so we could see the difference more clearly.

1.  First we create our test data (1 million random values):
CREATE TABLE TestData (Id INT IDENTITY PRIMARY KEY, VALUE INT)

-- Filling TestData with 1 million random rows to work with
INSERT INTO TestData (VALUE)
SELECT TOP (1000000) ABS(CHECKSUM(NEWID()))/1000
FROM sys.all_columns c CROSS JOIN sys.all_columns c1

--Creating also our permanent table on which we will be performing calculations
CREATE TABLE dbo.Test (VALUE INT)
                                          

2.  We will also create a table that will hold the final test results:
CREATE TABLE TestResults
(TestType VARCHAR(10), TableType VARCHAR(20), NumOfRows INT, ExecTime_ms INT)

3.  Now we create the “Simple” stored procedures.
All they do is calculate the amount of even numbers within the tables:
CREATE PROCEDURE usp_SimplePermanent
   @NumOfRows INT
AS
BEGIN
   TRUNCATE TABLE dbo.Test
   INSERT INTO dbo.Test SELECT TOP (@NumOfRows) VALUE FROM TestData
   --Calculate amount of even numbers
   SELECT COUNT(1) FROM dbo.Test WHERE (VALUE % 2 = 0)
END
-------------------------------------------------------------------------
CREATE PROCEDURE usp_SimpleTemporary
   @NumOfRows INT
AS
BEGIN
     CREATE TABLE #Test (VALUE INT)
     INSERT INTO #Test SELECT TOP (@NumOfRows) VALUE FROM TestData
     --Calculate amount of even numbers
     SELECT COUNT(1) FROM #Test WHERE (VALUE % 2 = 0)
END
-------------------------------------------------------------------------
CREATE PROCEDURE usp_SimpleTableVar
   @NumOfRows INT
AS
BEGIN
     DECLARE @Test TABLE (VALUE INT)
     INSERT INTO @Test SELECT TOP (@NumOfRows) VALUE FROM TestData
     --Calculate amount of even numbers
     SELECT COUNT(1) FROM @Test WHERE (VALUE % 2 = 0)
END

4.  In the same matter, we create the “Joined” stored procedures (same as above, but with a more complex query – This one joins between our table type and the full 1M table):
CREATE PROCEDURE usp_JoinedPermanent
   @NumOfRows INT
AS
BEGIN
     TRUNCATE TABLE dbo.Test
     INSERT INTO dbo.Test SELECT TOP (@NumOfRows) VALUE FROM TestData
     --Calculate amount of even numbers
     SELECT COUNT(1)
     FROM dbo.Test t
       INNER JOIN dbo.TestData td ON t.VALUE = td.Id --forcing a table scan
     WHERE (t.VALUE % 2 = 0)
END
-------------------------------------------------------------------------
CREATE PROCEDURE usp_JoinedTemporary   
   @NumOfRows INT
AS
BEGIN
     CREATE TABLE #Test (VALUE INT)
     INSERT INTO #Test SELECT TOP (@NumOfRows) VALUE FROM TestData
     --Calculate amount of even numbers
     SELECT COUNT(1)
     FROM #Test t
       INNER JOIN dbo.TestData td ON t.VALUE = td.Id --forcing a table scan
     WHERE (t.VALUE % 2 = 0)
END
-------------------------------------------------------------------------
CREATE PROCEDURE usp_JoinedTableVar
   @NumOfRows INT
AS
BEGIN
     DECLARE @Test TABLE (VALUE INT)
     INSERT INTO @Test SELECT TOP (@NumOfRows) VALUE FROM TestData
     --Calculate amount of even numbers
     SELECT COUNT(1)
     FROM @Test t
       INNER JOIN dbo.TestData td ON t.VALUE = td.Id --forcing a table scan
     WHERE (t.VALUE % 2 = 0)
END

5.  Now we will create the procedures that will activate our tests:
CREATE PROC usp_ExecuteSimpleTests
(
     @NumOfRows INT, -- 10 / 1000000
     @TableType VARCHAR(20) -- 'Temporary' / 'TableVar' / 'Permanent'
)     
AS
BEGIN
   DECLARE @sql NVARCHAR(2000)

   SET @sql =
   'DECLARE
          @i INT = 1,
          @n INT = 10, --run 10 iterations on every table type and use AVG results
          @Begin DATETIME2 ,
          @End DATETIME2 ,
          @TotalTime_ms  INT = 0

   WHILE (@i <= @n)
   BEGIN
          DBCC FREEPROCCACHE;
          SET @Begin = SYSDATETIME()

          EXEC usp_Simple' + @TableType + ' @NumOfRows = '+CAST(@NumOfRows AS VARCHAR)

          SET @End = SYSDATETIME()
          SET @TotalTime_ms += DATEDIFF(ms, @Begin, @End)
          SET @i = @i + 1
   END

   INSERT INTO TestResults (TestType, TableType, NumOfRows, ExecTime_ms)
   SELECT ''Simple'', '''+@TableType+''', '+CAST(@NumOfRows AS VARCHAR)+
          ', @TotalTime_ms/@n'

   EXEC sp_executesql @sql
END
-------------------------------------------------------------------------
CREATE PROC usp_ExecuteJoinedTests
(
     @NumOfRows INT, -- 10 / 1000000
     @TableType VARCHAR(20) -- 'Temporary' / 'TableVar' / 'Permanent'
)     
AS
BEGIN
   DECLARE @sql NVARCHAR(2000)

   SET @sql =
   'DECLARE
          @i INT = 1,
          @n INT = 10, --run 10 iterations on every table type and use AVG results
          @Begin DATETIME2 ,
          @End DATETIME2 ,
          @TotalTime_ms  INT = 0

   WHILE (@i <= @n)
   BEGIN
          SET @Begin = SYSDATETIME()

          EXEC usp_Joined' + @TableType + ' @NumOfRows = '+CAST(@NumOfRows AS VARCHAR)

          SET @End = SYSDATETIME()
          SET @TotalTime_ms += DATEDIFF(ms, @Begin, @End)
          SET @i = @i + 1
   END

   INSERT INTO TestResults (TestType, TableType, NumOfRows, ExecTime_ms)
   SELECT ''Joined'', '''+@TableType+''', '+CAST(@NumOfRows AS VARCHAR)+
          ', @TotalTime_ms/@n'

   EXEC sp_executesql @sql
END



6. Let’s execute the tests…
NOTE: For the first test we don't want to use the cache (this will affect our results). We want a "clean" execution plan for every run. Therefore, we will clean the cache before every run.
The second, obviously, will perform differently when relying on the cache (it will use the pre-calculated statistics). Therefore, we will not clean our cache here:
TRUNCATE TABLE TestResults
DBCC FREEPROCCACHE;

EXEC usp_ExecuteSimpleTests @NumOfRows = 10, @TableType = 'Permanent'
DBCC FREEPROCCACHE;
EXEC usp_ExecuteSimpleTests @NumOfRows = 10, @TableType = 'Temporary'
DBCC FREEPROCCACHE;
EXEC usp_ExecuteSimpleTests @NumOfRows = 10, @TableType = 'TableVar'
DBCC FREEPROCCACHE;
EXEC usp_ExecuteSimpleTests @NumOfRows = 1000000, @TableType = 'Permanent'
DBCC FREEPROCCACHE;
EXEC usp_ExecuteSimpleTests @NumOfRows = 1000000, @TableType = 'Temporary'
DBCC FREEPROCCACHE;
EXEC usp_ExecuteSimpleTests @NumOfRows = 1000000, @TableType = 'TableVar'
GO

EXEC usp_ExecuteJoinedTests @NumOfRows = 10, @TableType = 'Permanent'
EXEC usp_ExecuteJoinedTests @NumOfRows = 10, @TableType = 'Temporary'
EXEC usp_ExecuteJoinedTests @NumOfRows = 10, @TableType = 'TableVar'
EXEC usp_ExecuteJoinedTests @NumOfRows = 1000000, @TableType = 'Permanent'
EXEC usp_ExecuteJoinedTests @NumOfRows = 1000000, @TableType = 'Temporary'
EXEC usp_ExecuteJoinedTests @NumOfRows = 1000000, @TableType = 'TableVar'
GO

SELECT * FROM TestResults


  Results:
Simple query:

TableType
NumOfRows
ExecTime (ms)
Permanent
10
215
Temporary
10
21
TableVar
10
18
Permanent
1,000,000
6,447
Temporary
1,000,000
2,188
TableVar
1,000,000
573

It is pretty clear that the table variable has much better performance than the others.

Joined Query:

TableType
NumOfRows
ExecTime(MS)
Permanent
10
11
Temporary
10
0
TableVar
10
0
Permanent
1,000,000
6,182
Temporary
1,000,000
3,024
TableVar
1,000,000
5,644

In this case, as you can see, the temporary table is the winner.

So what is going on?
Simple Query:
Since the execution plan in this case is always the same, statistics have no meaning here and updating them creates an overhead. This is where the table variable has an advantage over the other tables.
Joined Query:
Now statistics have a huge influence on the execution plan. Since we joined our table types with the large table (1M rows), the optimizer needs statistics to figure out which is the best plan to use.

Conclusion:
According to the tests above, it is pretty clear that the permanent table is not good in any case for such calculations. It has too much of an overhead.
As stated before, the table variable seems to be a better choice for simple calculations when there is only one way to execute the procedure, and the temp table would be the best choice when the procedure can be executed in more than one way.

To conclude, there is no universal rule of when and where to use temp tables or table variables. Try them both and experiment in both sides of the spectrum – small and large amounts of records.
I, from my personal experience, would rather go for temp tables. Usually we already have the plan in cache, so we don’t have to worry about recompilation. Moreover, the advantage the table variable has over the temp table in simple calculations is minor compared to the advantage the temporary table has for complex ones.

Good Luck! J

Thursday, April 5, 2012

Code Retreat at PicScout

At the end of January we've reserved some time from our busy schedule to conduct a code retreat at PicScout.
For that propose we invited a very special guest, who was visiting Israel as a guest of the Software Craftsmanship community: Corey Haines.

Corey introduced himself to the team and then outlined the code retreat day.
We were supposed to implement a short program, called Conway's Game of Life in continuous series of sessions, each introducing and emphasizing a different idea/angle in the coding craft.

According to Corey, the idea was to perfect our knowledge and coding techniques by concentrated practice. 



Just with the introduction, Corey mentioned very important thing: It is somehow a wrong perception that experienced/mature developers write perfect code. 
Actually their code isn't beautiful (nor perfect), but the difference between the un-experienced and highly experienced developers is that their code will be perfect enough. 
My own take on that is that only when you are an expert, only then you will be able to decide when this enough is enough (there are no rules, just experience and practice)
According to Corey, the purpose of the code retreat (to some degree) is to teach us how to seek a perfect enough code.

After a short introduction the fun began.




We coded in pairs for 45 min. 
At the end of each session we deleted the code and started over from scratch.
And yes, you're not mistaken; We deleted the code.
Somehow, during the history of our profession we learned the unwritten rule: 
Thee shell not delete any code.

Here is again my take on this: Deleting actually makes a lot of sense, since typing code isn't the real problem.
The real problem is reading (and maintaining) our code.

Therefore, the same writing 101 techniques apply when composing code. 
Write, Re-read then Re-read again. If it doesn't make sense Delete it. Start over. 

After deleting the code, we've reflected on what happened during the session. A few questions popped up and Corey provided short answers and directions.
In my opinion, the main idea was to let us to "find out" the right approach instead of providing a solid answer. 
Swapping pairs contributed to the diversity of the solutions. 
Each pair provided a unique solution and a unique approach that revealed different ways of solving a problem.

So what we did?

I. We started by exploring the problem. 
Conway's game of life is a nice problem that cannot be solved in 45 min. 
The first session was just to become familiar with the problem.
Needless to say that almost nobody (except the algorithm's team :) ) produced something coherent enough during this session.

II. Next we touched a sensitive topic, called a "better design". 
You know what it is when you call somebody else and tell him that this isn't how he should build his software (guess what happens a few weeks later when somebody else review your code). 
The idea in this session was that it is quite hard to find what is a "better" design. Therefore we discussed the 4 rules of simple design:

1. Tests Pass
2. Reveals Intent (good names)
3. No Duplication - repeat information and knowledge only once
4. Small (remove redundant, be lean and minimalist)

An interesting remark (with regards to 2) was that WE CAN use verbs to name classes.
Also here, some archaic (and not written rule) taught us that we should use only nouns as the class names. 
Why is that? If it makes sense, if it reveals the content then we shouldn't limit ourselves to those rules.

III. On our third session we touched Test First.
We discussed where from we should start (obviously, from an empty, null or default case). 
We discussed what we want to test or specifically what is the interface/action we would like to reveal during our test (a hint here will be: a tick() action that happens in the Conway's world)
We discussed how our test slowly and gradually reveal the interfaces and the behaviors of our objects (specifically we discussed why a cell needs to have a state)

IV. The fourth session introduced two pairing techniques.
a. A driver (who writes the code) and a navigator (who watches the driver)
b. A driver and a navigator are constantly exchanging their roles (like in a ping pong game)

The problem with the first technique is that it is done in a very statical way: The driver always drives and the navigator almost never touches a keyboard. 
This is obviously quite boring!

The Ping-Pong technique is a better implementation of the Driver/Navigator. There are two forms of a Ping-Pong pairing:
a. Driver: Writes a test. Navigator: Passes the test
b. Driver: Passes a test and writes the next test. Navigator. Passes the test and then writes the next test

Eventually in this session we used a MUTE ping pong style when a driver writes a test and a navigator passes the test.
In addition, Corey introduced the "find the loophole" - the navigator will strive to write a wrong implementation (though in a clean manner) and the driver will strive his best to write the failing tests.
IMO, it was the best session, since there in silence you can really discover the power of the test first and pair programming techniques.

V. In that  session Corey talked about the TDD and Test First techniques. 
Corey explained the difference between the two and how our design should be changed due to the use of the TDD.

In addition, we were introduced with the following constrains on our code:
Contraints:
1. If statements are disallowed (unless these are guard clauses). If statement is a form of procedural programming and usually can be seen as the simplest form of polymorphism.
2. Explicit looping (for, foreach) are disallowed.
3. No primitives across method passing
4. No method with more than 3 LOC (lines of code) 

I assume you guessed correctly, such constrains are really helping in creating more readable and maintainable code. 

VI.
We wrapped the day by asking 3 Questions. 

1. What if anything did you learn?
2. What if anything surprised you?
3. What if anything will you do differently moving forward.

I can definitely say that it was one of the most enjoyable days we had at PicScout and we surely learnt a great deal!






Monday, March 26, 2012

Javascript from within .NET


Target:
In this post we’ll discuss how to out of curiosity run easily complex Javascript functionality in C# using WebBrowser control (however without using WinForms).

Background:
We can achieve our target via building Web application where one of the web pages will contain JavaScript code. JavaScript method can then be invoked and its results can be returned to server (for instance with simple JQuery).
However, we don’t want to build Web application just to execute some single JavaScript function (but with important output). 

So is it possible to run JavaScript in C# without heavy artillery?

After quick googling, we’ve found couples of tools, most prominent and free are Jint and Javascript.Net.
Both open sources are pretty easy to use in case we want to run simple JavaScript code.
But what happens when JavaScript resides at some 3rd party website and obfuscated?
JavaScript interpreter is not enough in such case, we need browser capabilities in order to load 3rd party obfuscated JavaScript module and run its methods.
With a little help from Google and combining suggestions and tricks from different sources, we compiled a nice solution.

1.Creation of static HTML page
First of all, we created a static HTMLpage HTMLPage1.html with all the relevant scripts definitions. In following example, we’ve created a sample page with 2 references:
1. Reference to obfuscated online 3rd party JavaScript Api which contain class ApiClass with asynchronous function DoSomething().
2. Reference to custom JavaScript file that we created and it is situated at our output directory
In the body of the page, we wrapped calls to both our and external JavaScript API methods with simple functions that receives a list of parameters (in the examples below only one parameter, but it can be freely extended to more) and a callback (will be discussed later)
<html>
  <head>                          
    <script type="text/javascript" src="myJsClass.js"></script>
    <script type="text/javascript" src="http://someObfuscatedApi"></script>               
    <title>Hi</title>
  </head>

  <body> 
    <script type="text/javascript">
        function CallJsMethod(jsParameter, callback) {
            someClass = new someObfuscatedAPI.ApiClass();
            return someClass.DoSomething(jsParameter,function(result){
                callback(result);           
            });
        }

        function CallMyJsMethod(jsParameter, callback) {
            someOtherClass = new myJsClass.MyApiClass();       
            var result = someOtherClass.DoSomethingElse(jsParameter);           
            callback(result);           
        }          
    </script>
   </body>
</html>

2.Running WebBrowser control
Next step is to create and to execute the WebBrowser control in our module.
WebBrowser is a Windows.Forms control, as such it should be executed as Single-Threaded Apartment (STA) process.
In order to start using JavaScript it should be fully loaded in a browser (and we know that from a DocumentCompleted event of the browser).
The following code will do the trick:
public class JsExecuter
{               
    private string m_JsMethod;
    private object[] m_JsParameters;

    public void ExecuteJavaScript(string jsMethod, object[] jsParameters)
    {
       m_JsMethod = jsMethod;                
       m_JsParameters = jsParameters;              
       runBrowserThread();
    }

    private void runBrowserThread()
    {                
       var th = new Thread(() =>
       {
          using (WebBrowser webBrowser = new WebBrowser()
          {
             InitBrowser(webBrowser);
             Application.Run();
          }
        });
        th.SetApartmentState(ApartmentState.STA);
        th.Start();               
    } 

    //load our html page and so all the referenced scripts inside
    private void InitBrowser(WebBrowser webBrowser)
    {
       webBrowser.DocumentCompleted += browser_DocumentCompleted;                
       string curDir = Directory.GetCurrentDirectory();
       webBrowser.Url = new Uri(String.Format("file:///{0}/HTMLPage1.html",curDir));
    }

    private void browser_DocumentCompleted (object sender, WebBrowserDocumentCompletedEventArgs e)
    {
       WebBrowser br = sender as WebBrowser;
       br.Document.InvokeScript(m_JsMethod,m_JsParameters);                     
     }             
}

3.Calling the module and using the callback
Now we know how to create a WebBrowser control and make the call to JavaScript methods from it.The last thing that is left to do is to explain how to use the callbacks.
 
public delegate void ResultArrivedHandler(string results);

// in order to allow communication from JavaScript 
//[ComVisible(true)] should be defined
[ComVisible(true)]
public class CallBack
{
      public ResultArrivedHandler ResultArrived { get; set; }    

      //indicates default member for the object. It allows us to pass  
      //CallBack instance directly to JavaScript, and so JavaScript will
      //invoke its default member
      [DispId(0)]
      public void Call(string result)
      {
         Application.ExitThread();

         if (ResultArrived != null)
         {
            ResultArrived(result);
         }
      }
}

In the code above we defined a class named CallBack. An instance of this class will be passed as a callback to our JavaScript wrapper methods (see Creation of static Html page section).

When a result arrives from JavaScript, the ResultArrived event will be raised to notify our module’s clients.

4.Sample client code:

class Program
{
  static void Main(string[] args)
  { 
    CallBack cb = new CallBack(); 
    cb.ResultArrived += new ResultArrivedHandler(x =>Console.WriteLine(x));
    JsExecuter gs = new JsExecuter();

    // last parameter in parameter list will be callback object
    gs.ExecuteJavaScript("CallJsMethod", new object[] { "some parameter", cb }); 
  }
}
Here we created a CallBack object and subscribed to its ResultArrived event (just prints the results to Output).
Then, we just called to our module. Notice how the parameters and especially CallBack instance were passed (the CallBack instance is the last parameter !).

Epilogue:
That’s all! Simple and elegant solution for pretty tricky problem.
But there is still one big con for this solution and it’s of course the use of the WebBrowser control.
Yes, memory leak issues are still there and weren’t solved (online discussion thread: http://social.msdn.microsoft.com/Forums/en-US/ieextensiondevelopment/thread/88c21427-e765-46e8-833d-6021ef79e0c8).
It’s not a big deal if your application doesn’t run a lot of JavaScript methods.
However if it runs for hours and performs thousands calls to a module above then depending on your machine it can hang or stuck.

Wednesday, March 7, 2012

Best Practices - JavaScript Programming Part 2


Best Practices -
JavaScript Programming
Part 2


Object Oriented Programming

The advantages of OO Programming vs. Procedural Programming are well known but exceed the scope of this post. While JavaScript is not a classic OO language such as Java/C++/C#, it holds many OO capabilities.
Although the syntax of OO programming in JS can be confusing at first, it becomes a very handy and easy to use feature once you get used to it.


Objects(Classes):

As a prototype based language JavaScript does not have classes, therefore it does not contain a ‘class’ statement. Instead, classes can be simulated through functions.

Let’s observe the following example:

function ListItem() { }

We’ve just simulated a class called ListItem. Cool hah?


Objects and Constructors:

Instantiating is done using the ‘new’ keyword. The function itself is used as a constructor.

var li = new ListItem();

Pay Attention: calling the constructor function without the 'new' keyword will result in a regular execution of the function instead of creating a new instance.


Members/Properties:

function ListItem(headline) {
    this.Headline = headline;
}

var headLineItem = new ListItem("HeadLine");

Here the constructor receives one parameter. The HeadLine property is initialized.


Methods:

function ListItem(headline) {
    this.Headline = headline;
    this.ToString = function () {
        return "ListItem:" + this.Headline;
    }
}

var headlineItem = new ListItem("HeadLine");
var stringRepresentation = headlineItem.ToString();

I think we got the idea…


Prototype:

Much like the extension methods mechanism in C#, JavaScript allows the programmer to extend the functionality of an object outside its constructor's scope. This is done using prototype.
For instance, let’s take the object from above and add to it the ability to alert its headline to the screen:

ListItem.prototype.AlertHeadline = function () {
    alert(this.Headline);
}

What we did here was to add the method AlertHeadline() to the object ListItem.
We can now run it from any ListItem instance:

headLineItem.AlertHeadLine();

*Note: prototyping should be used wisely. There’s no sense in extending a local object that exists in your own code with a method written in a different place – just add the method to the constructor function's scope.  In case we choose to extend an external object, we should keep all of our prototype extension methods in a single place (Extensions.js file for example). Otherwise, it can be a major headache trying to track down a prototype method, especially in complex environments that contain a lot of code.
A great example for the use of prototype is the String.format extension:

String.prototype.format = function () {
    var str = this;
    var i = arguments.length;
    while (i--) {
        str = str.replace(new RegExp('\\{' + i + '\\}', 'gm'), 
            arguments[i]);
    }
    return str;
};


JQuery

JavaScript is executed by the built in interpreters of browsers. Though there are standards, different browsers may execute the same JS code slightly different from one another. To ensure the correct execution of your JS code it is advised to use JQuery wherever possible, especially for DOM traversal and AJAX.


Final Words

As I said before, all that is written here comes from my own personal experience. These practices work for me and will hopefully work for some of you.
Enjoy.

Back to: Part 1