Sunday, June 28, 2009

Codeplex changes it’s policy based on my suggestion

This is pretty cool. This week Codeplex changed it’s Start a Project page and added a section which I had suggested. I guess they are listening to me. :) You can read my suggestion I made back in Dec 2008 in the discussion forums to . You can also watch a Channel 9 video describing the feature starting at 5min. The suggestion was simply to allow donation links on Codeplex project sites.

My Codeplex project SharePoint SUSHI has been on Codeplex since November 2007 and was one of the early projects posted on Codeplex. It has been downloaded 17,000 times. I love the comments on the latest version, they are awesome:

Probably the most useful free SharePoint tool out there! Saved me hours of work many many times. Some really innovative features (e.g. copy list view). Great stuff! Please keep it up. Thanks for sharing the results of your hard work!. Greg
by
Greg_O on Mar 9 at 7:22 AM

Some great functions. Definitely going to use that tool often in the future.
by
Dublette on Mar 2 at 2:19 AM

God bless you, Joseph. You may just get that Wikipedia page after all. :)
by
panoone on Feb 4 at 8:24 PM

 

It is good to see all those hours spent building SUSHI having some positive benefit for the community. I know that I have benefited enormously from blog posts and free utilities so it is good to give a little something back.

Thursday, June 25, 2009

Float vs. Decimal data types in Sql Server

 

This is an excellent article describing when to use float and decimal. Float stores an approximate value and decimal stores an exact value.

In summary, exact values like money should use decimal, and approximate values like scientific measurements should use float.

Here is an interesting example that shows that both float and decimal are capable of losing precision. When adding a number that is not an integer and then subtracting that same number  float results in losing precision while decimal does not:

DECLARE @Float1 float, @Float2 float, @Float3 float, @Float4 float;
SET @Float1 = 54;
SET @Float2 = 3.1;
SET @Float3 = 0 + @Float1 + @Float2;
SELECT @Float3 - @Float1 - @Float2 AS "Should be 0";

Should be 0
----------------------
1.13797860024079E-15

 

When multiplying a non integer and dividing by that same number, decimals lose precision while floats do not.

DECLARE @Fixed1 decimal(8,4), @Fixed2 decimal(8,4), @Fixed3 decimal(8,4);
SET @Fixed1 = 54;
SET @Fixed2 = 0.03;
SET @Fixed3 = 1 * @Fixed1 / @Fixed2;
SELECT @Fixed3 / @Fixed1 * @Fixed2 AS "Should be 1";

Should be 1
---------------------------------------
0.99999999999999900

Tuesday, June 16, 2009

Trancender vs. Measureup – Tips for Microsoft Certification Tests

 

  • I've been going certification crazy this year as I've passed 5 certification tests so far. Along the way I've picked up a few tips that might be of interest to someone looking to study for and take a Microsoft Certification exam.
  • Trancender is a win forms app installed locally, so don't need to be connected to internet.
  • Measure up is a web app.
  • Both Measureup and Trancender are seriously out-of-date applications. It is embarrassing that they test your .NET skills and yet the skills of the programmers who wrote these tools is seriously lacking/out-of date.
  • No cut and paste available in Trancender. This makes it hard to try out source code contained in the practice questions and descriptions are more generic than Measureup. For each question Measureup tells you why the right answer is right and the wrong answer is wrong, which helps you understand the way the question asker is thinking and what they are trying to teach or the point they are trying to assess.
  • Both Measureup and Trancender tests are a little harder than the real tests. On a measure up test all the answers usually at least are not nonsensical, they represent valid .NET names, classes etc. On the actual test, you can often eliminate several wrong answers just by being familiar with correct syntax.
  • Measureup allows you to take a short test, and see the answer after each question. This is my favorite way to study for a test: in small chunks. I take a short 10 question test whenever I get a chance to study and dig into any questions I didn't get right. With Trancender you have to take a 30 question test, and can't see the answers until the end.
  • Note that In my experience there are not very many interactive format questions. These are questions that have drag drop functionality. All the questions are multiple choice in which you choose 1 answer. A few questions have two or three right answers.
  • Overall I like Measureup better. It is easier to study on-the-fly, and can be used from any machine without going through an install program most likely written in 1994 using VB6. :)

MCP Testing Tips:

  • I didn't use any practice tests that are actually just copies of the real tests posted on the Internet (brain dumps), that is just plain cheating and Microsoft has clearly stated this. Measureup and Trancender tests however are sanctioned by Microsoft and are linked from their certification website. Measureup and Trancender use similar questions without using the actual questions from the real test. They are good preparation materials because they are a fast way to give you a feel for which concepts will be tested and the types of questions that will be asked. They also get you used to the testing format. I think that in addition to testing your .NET skills, the MCP exams test your pattern recognition and reading compression skills and so practice tests help you sharpen these skills as well. The other thing I like about these practice tests is that there is just a huge amount of features to a given technology (WCF, ADO.NET, Win Forms, etc) and a practice test is a good way to get a feel for what the most important features of that technology are. You may not know every detail about that feature after the practice test but at least down the road when you have a business need for that feature you know where to start looking. I would borrow a phrase from a favorite childhood cartoon GI-JOE.

    If GI-JOE were a .NET programmer he would have said: "Knowing where to look in the .NET Framework is half the battle.” MCP Certification tests are a great way to quickly learn where to look.

Friday, June 05, 2009

Stored Procedures versus Ad-hoc SQL

Have you ever worked on a system where someone decreed that all database calls must be Stored Procedures, and ad-hoc SQL is strictly forbidden? I have and it leads to incredible development pain.

 

Let me first say that I have written many awesome 100+ line stored procedures. Stored procedures are definitely a good thing when writing complex queries. TSQL and Stored Procedures are fantastic. However, I think that the optimal decision is to use a mix of both ad-hoc sql and stored procedures rather than just stored procedures.

 

Reasons that are given for the above decree and why they are no longer true:

  • Security:
    • SQL injection: Resolved by using parameterized SQL which eliminate SQL injection possibility.
    • Granular security: If the app pool account is dbowner, then there is no additional security from applying security to each stored procedure (execute only privileges).

 

  • Performance:  (see performance testing section below for test results)
    • Execution plan Caching: In SQL Server 2005 and later, SQL server caches the execution plan based on tokenized versions of queries so performance of parameterized ad-hoc SQL is close to stored procedures.(see John Lam's comment below)

 

  • Maintenance
    • Changing Stored procedures doesn’t require recompiling code: With agile development and continuous integration, code is easy to change and deploy. Advantage of ad-hoc SQL: fewer lines of code overall because you don’t have to declare stored procedure signature. Fewer lines of code means risk of errors goes down, and maintenance cost goes down. Easier to refactor code with inline SQL because able to use "find and replace" in Visual Studio when renaming a column or changing a data type. Also, using a mix of stored procedures and ad-hoc SQL keeps the database cleaner. When using all stored procedures you quickly have 100s to keep track of and inevitably stored procedures get lost and a significant effort must be devoted to maintaining and auditing them all.
    • Faster coding, don't have to switch between sql server and Visual Studio. (Jeff Atwood also makes this point).
    • Better documentation: When TSQL code is in data layer, able to look at it in one place. (Jeff Atwood also makes this point)
    • Debugging faster, easier. Cumbersome to set breakpoints in stored procedures.

 

  • Transactions
    • It used to be possible to do transactions only in stored procedures and not in .NET. But now with the System.Transactions namespace it is easy. This is really great stuff. If you haven't used it, it is so awesome and clean.

 

Comments:

I am not anti-stored procedure. I have used them quite a bit and they are incredibly powerful. TSQL is just gorgeous when it comes to performance. When you have complex queries, they become good candidates for stored procedures because you can leverage temporary tables, common table expressions, etc.

Note ORMs like Entity Framework (the future of data access) uses parameterized sql, so if developers are willing to use those frameworks they are already using parameterized sql. For some reason developers are willing to use those frameworks but not inline SQL, even though they talk to the database in the same way.

 

 

What others are saying:

Jeff Atwood puts in eloquently in his blog post: who needs stored procedures anyway?.

 

And who can argue with the great John Lam? He says:

As a guy who dabbles in low-level bit twiddling stuff from time-to-time, the performance claims are quite interesting to me. The new (as of SQL Server 7.0) cached execution plan optimization in SQL Server looks to me a lot like JIT compilation. If this is, in fact, the case it seems to me that the only overhead that would be associated with dynamic SQL would be:

  1. The amount of bandwidth + time it takes to transmit the dynamic SQL text to the database.
  2. The amount of time it takes to calculate the hash of the dynamic SQL text to look up the cached execution plan.

I can imagine quite a few scenarios where the above overhead would disappear into the noise of the network roundtrip. What upsets me are the folks who spout forth anecdotal arguments that claim stored procedures have "much better" performance than dynamic SQL.

 

Conclusion

I cringe when I see an architectural decision which tries to make it easy for the server which is a $4000 resource over two years at the cost of making it harder for the developer which is a $400,000 resource (over two years).

A question from personal experience: I recently saw a 10 million dollar software project go over-budget and lose a ton of money. Why did the project fail? Using ad-hoc sql may not have saved the project but making similar productivity enhancing decisions like allowing ad-hoc SQL when it makes sense and is not a security or performance compromise, might just have tipped the project from red to black.

 

Performance Testing

I always have to test performance claims for myself. So I wrote a small console application to put the execution caching plan to the test. A method using a stored procedure was called 1000 times. A method using the same sql as paramaterized, ad-hoc SQL was called 1000 times. The times were about equal, actually they where slightly faster for the ad-hoc sql :).

Results (milliseconds):

clip_image001

clip_image002

clip_image003

static void Main(string[] args)

{

//--warm up

SelectContactStoredProc();

SelectContactParamaterizedSql();

//--

Stopwatch sw = new Stopwatch();

sw.Start();

for (int i = 1; i <= 1000; i++)

SelectContactStoredProc();

sw.Stop();

Console.WriteLine("Elapsed StoredProc:" + sw.ElapsedMilliseconds);

//--

sw.Reset();

sw.Start();

for (int i = 1; i <= 1000; i++)

SelectContactParamaterizedSql();

sw.Stop();

Console.WriteLine("Elapsed ParmSQL:" + sw.ElapsedMilliseconds);

Console.ReadLine();

}

public static void SelectContactParamaterizedSql()

{

DataGeneral.ParamBuilder pb = new DataGeneral.ParamBuilder();

pb.AddParam(SqlDbType.Int, "@contactGroupId", 11);

string sql = @"select ContactID,ContactGroupID,ContactTypeRefCode,FirstName,LastName,Prefix,Suffix,Email,Phone,PhoneExt,AltPhone,AltPhoneExt,MobilePhone,FaxPhone

from Contact where contactGroupId = @contactGroupId";

object ret = DataGeneral.GetScalar(sql, pb.Parameters, false);

}

public static void SelectContactStoredProc()

{

DataGeneral.ParamBuilder pb = new DataGeneral.ParamBuilder();

pb.AddParam(SqlDbType.Int, "@contactGroupId", 11);

object ret = DataGeneral.GetScalar("General_Contact_GetByContactGroupID", pb.Parameters, true);

}