Sunday, November 4, 2007

Stored Procs performance are better then Dynamic SQL. Is it A myth?

People still thinks Stored Procedures are faster, even if there is much evidence that shows otherwise.

Andres Aguiar's wrote is his blog that: "Fortunately, when they go and ask the LinQ for SQL/Entities team they get the same answer than they get from me. They are not.
"

To add more evidence, a couple of weeks ago he was in Redmond in a meeting with the ADO.NET team, and a Tech Lead from that team wrote the code below and said 'this is the fastest way to execute this SQL sentence with the .NET framework today'.

SqlCommand cmd = sqlConnection.CreateCommand();
cmd.CommandText = @"
SELECT Sales PersonId, FirstName, HireDate
FROM SalesPerson as sp
INNER JOIN Employee AS e ON sp.SalesPersonID = e.EmployeeID
INNER JOIN Contact AS c ON e.EmployeeID = c.ContactID
WHERE e.HireDate < @date";
cmd.Parameters.AddWithValue("@date", date");

DbDataReader r = cmd.ExecuteReader();

Why is this _faster_ than a stored procedure? Because the SQL Server engine could select a better execution plan depending on the value of the @date parameter.

Even in that room there was people that were surprised.

So here are some points why not using stored procedures:
1. Stored Procedures are written in big iron database "languages" like PL/SQL (Oracle) or T-SQL (Microsoft). These so-called languages are archaic, and full of the crazy, incoherent design choices that always result from the torturous evolution of ten years of backwards compatibility. You really don't want to be writing a lot of code in this stuff. For context, JavaScript is a giant step up from PL/SQL or T-SQL.

2. Stored Procedures typically cannot be debugged in the same IDE you write your UI. Every time I isolate an exception in the procs, I have to stop what I am doing, bust out my copy of Toad, and load up the database packages to see what's going wrong. Frequently transitioning between two totally different IDEs, with completely different interfaces and languages, is not exactly productive.

3. Stored Procedures don't provide much feedback when things go wrong. Unless the proc is coded interally with weird T-SQL or PL/SQL exception handling, we get cryptic 'errors' returned based on the particular line inside the proc that failed, such as Table has no rows. Uh, ok?

4. Stored Procedures can't pass objects. So, if you're not careful, you can end up with a zillion parameters. If you have to populate a table row with 20+ fields using a proc, say hello to 20+ parameters. Worst of all, if I pass a bad parameter-- either too many, not enough, or bad datatypes-- I get a generic "bad call" error. Oracle can't tell me which parameters are in error! So I have to pore over 20 parameters, by hand, to figure out which one is the culprit.

5. Stored Procedures hide business logic. I have no idea what a proc is doing, or what kind of cursor (DataSet) or values it will return to me. I can't view the source code to the proc (at least, without resorting to #2 if I have appropriate access) to verify that it is actually doing what I think it is-- or what the designer intended it to do. Inline SQL may not be pretty, but at least I can see it in context, alongside the other business logic.

So why use Stored Procedures at all? Conventional wisdom says we do it because:
1. Stored procedures generally result in improved performance because the database can optimize the data access plan used by the procedure and cache it for subsequent reuse.

2. Stored procedures can be individually secured within the database. A client can be granted permissions to execute a stored procedure without having any permissions on the underlying tables.

3. Stored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component.

4. Stored procedures add an extra level of abstraction from the underlying database schema. The client of the stored procedure is isolated from the implementation details of the stored procedure and from the underlying schema.

5. Stored procedures can reduce network traffic, because SQL statements can be executed in batches rather than sending multiple requests from the client.

one more interesting fact on Stored Procs:
Everyone who thinks stored procedures are pre-compiled, say "Aye!". Whoa, what a noise! For all of you who said "Aye!" a few seconds ago: open SqlServer's Books Online (v7 or v2000, doesn't matter), search for "cache execution plan". You'll find fine articles like "Execution Plan Caching and Reuse" and "SQL Stored Procedures". Let me just quote some lines from the "SQL Stored Procedures" article:
SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.


So what do you think? Is it a myth? :-)

.NET goodie #2: High Performance C# Code

This article presents helpful tips for writing in-process .NET managed code that performs well. It's assumed that basic programming skills such as factoring control structures, pulling work outside of loops whenever possible, caching variables for reuse, use of the switch statement, and the like are known to the average reader.

Working with Objects and Value Types
Objects: A Double Whammy
Objects are expensive to use, partly because of the overhead involved in allocating memory from the heap (which is actually well-optimized in .NET) and partly because every created object must eventually be destroyed. The destruction of an object may take longer than its creation and initialization, especially if the class contains a custom finalization routine. Also, the garbage collector runs in an indeterministic way; there's no guarantee that an object's memory will be immediately reclaimed when it goes out of scope, and until it's collected, this wasted memory can adversely affect performance

The Garbage Collector in a Nutshell
It's necessary to understand garbage collection to appreciate the full impact of using objects. The single most important fact to know about the garbage collector is that it divides objects into three "generations": 0, 1, and 2. Every object starts out in generation 0; if it survives (if at least one reference is maintained) long enough, it goes to 1; much later, it transitions to 2. The cost of collecting an object increases with each generation. For this reason, it's important to avoid creating unnecessary objects, and to destroy each reference as quickly as possible. The objects that are left will often be long-lived and won't be destroyed until application shutdown.

Lazy Instantiation/Initialization
The Singleton design pattern is often used to provide a single global instance of a class. Sometimes it's the case that a particular singleton won't be needed during an application run. It's generally good practice to delay the creation of any object until it's needed, unless there's a specific need to the contrary - for instance, to pre-cache slow-initializing objects such as database connections. The "double-checked locking" pattern is useful in these situations, as a way to avoid synchronization and still ensure that a needed action is only performed once. Lazy initialization is a technique that can enhance the performance of an entire application through object reduction.

Avoiding Use of Class Destructors
Class destructors (implemented as the Finalize() method in VB.NET) cause extra overhead for the garbage collector, because it must track which objects have been finalized before their memory can be reclaimed. I've never had a need for finalizers in a purely managed application.

Casting and Boxing/Unboxing Overhead
Casting is the dynamic conversion of a type at runtime to another, and boxing is the creation of a reference wrapper for a value type (unboxing being the conversion back to the wrapped value type). The overhead of both is most heavily felt in collections classes, as they all - with the exception of certain specialized ones like StringDictionary - store each value as an Object. For instance, when you store an Int32 in an ArrayList, it is first boxed (wrapped in an object) when it is inserted; each time the value is read, it is unboxed before it is returned to the calling code. You can use generics to avoid Boxing/Unboxing.

Trusting the Garbage Collector
Programmers new to .NET sometimes worry about memory allocation to the point that they explicitly invoke System.GC.Collect(). Garbage collection is a fairly expensive process, and it usually works best when left to its own devices. The .NET garbage collection scheme can intentionally delay reclamation of objects until memory is available, and in particular longer-lived objects (those that make it to generation 1 or 2) may not be reclaimed for an extended period. Even a simple "Hello, world!" console application may allocate 15 MB or more of memory for its "working set." My advice: don't call GC.Collect() unless you really know what you're doing.

Properties, Methods, and Delegates
Avoiding Overuse of Property Getters and Setters
Most people don't realize that property getters and setters are similar to methods when it comes to overhead; it's mainly syntax that differentiates them. A non-virtual property getter or setter that contains no instructions other than the field access will be inlined by the compiler, but in many other cases, this isn't possible. You should carefully consider your use of properties; from inside a class, access fields directly (if possible), and never blindly call properties repeatedly without storing the value in a variable. All that said, this doesn't mean that you should use public fields!

About Delegates
Delegates are slower to execute than interface methods. Delegates are often used to introduce a level of indirection in code, but in almost all cases interfaces allow a cleaner design. Of course, it's impossible to completely shun delegates; the entire event-handling paradigm in .NET is based on them. Example 2 compares the performance of delegates and direct method calls.

Minimizing Method Calls
The .NET compiler is capable of performing many optimizations for release builds. One of them is called "method inlining." If method A calls method B and certain other conditions are met, such as the code in method B being small enough, the code from B will be copied into A during compilation. However, .NET won't or can't inline certain types of methods, such as virtual methods or methods over a certain size. Each method invocation/property access entails significant overhead, such as the allocation of a stack frame, etc. Of course, you should never repeatedly call a method for the same result on purpose, but you should also be mindful of the impact of method calls in general.

Using the 'Sealed' Keyword
Wherever extensibility is not required, you should use the sealed keyword. This makes your design easier to understand, as someone can tell at a glance if a certain class or method isn't meant to be extended or overridden. It also increases the chances that the compiler will inline code.

Working with Collections
Avoid Overuse of Collections
This might sound strange, but I'm not advocating working without data structures. The fact is, I've seen collections used many times when they don't actually simplify the code or provide any benefit at all. The single biggest avoidable use of collections is the use of an ArrayList when a simple array would suffice. It should be obvious that there's no way that calling a collection method - which may do significant work under the covers - can compare to something as simple as an array access for performance.

for vs foreach
The rumor abounds that the foreach loop is bad for performance. The truth is actually a little more complicated. Basically, foreach involves no performance penalty when used against arrays. However, when used against lists it involves the same overhead as creating an enumerator and using it within a try/catch block! Ildasm.exe may come in handy here to see what's going on. This isn't a complete killer, but if you do enough list access you may want to avoid it.

Enumerators: Don't Go Overboard
Just because the possibility exists for the enumerating of a collection doesn't mean you have to do it. For instance, the ArrayList class is useful as an array replacement; one of its best features is indexed element access. By using an enumerator with ArrayList, you hide its most useful features and introduce needless overhead.

Avoid Overuse of Collection Wrappers
A peek at the code in classes such as ArrayList shows that, to implement synchronized, fixed-size, and read-only versions of these, methods such as Synchronized() actually create a new wrapper list around the original one (this scheme was copied from Java). While this is nice from certain design standpoints, it degrades performance; the method-call overhead for each operation is multiplied. For a fixed-size, synchronized ArrayList, this overhead is tripled! Chances are, if you're working with a collection and need synchronization, the code using the collection is already synchronized. In any case, simply locking on the collection itself around every access turns out to be faster than using a synchronized wrapper.

Working With Strings
Don't Use String.Format() to Concatenate Strings
While string-formatting routines built into .NET are very useful for globalization and other tasks, they're not meant to be used for appending strings to each other.

Use StringBuilder to Build Strings Inside Loops
The StringBuilder class is basically an array list for string fragments; the StringBuilder takes care of expanding its internal char array, hiding this from the user. The use of this class is also specially optimized by the .NET compiler, making it impossible to duplicate this functionality with equivalent performance (for instance, by manipulating char arrays directly and converting to a string afterwards).

Don't Use StringBuilder to Concatenate Small Numbers of Strings
Many .NET developers who consider themselves well-versed in performance matters advocate the use of the StringBuilder class whenever possible. However, it's not the fastest approach for concatenating small numbers of strings. Actually, any number can be combined in a single statement, although the performance benefit tends to dwindle above five or six substrings. This is due to instantiation and destruction overhead for the StringBuilder instance, as well as method-call overhead involved in calling Append() once for every added substring and ToString() once the string is built. What are the alternatives? Plus-sign concatenation and the String.Concat() method are equivalent; I prefer plus signs for readability. Note that this cannot be used across loops because the entire concatenation must occur within a single statement.

Don't Be Afraid to Use String Literals
Many developers incorrectly assume that a new object is created for every string literal, and, therefore, avoid their use. In some cases, using string literals directly in your code is a better approach than using string constants! It can make the code easier to understand and has no adverse impact on performance. This is due to the use of the .NET interned string table; this table maintains a String instance for every known string and reuses this instance whenever the same character sequence is used as a literal. See the documentation of the String.Intern() method for more details.

Threading
In itself, this is a huge topic. Most easy-to-apply, synchronization-related optimizations focus on minimizing the amount of code executing inside synchronized blocks, which may involve moving some code from inside to outside such blocks. In some cases, thoughtful programming may allow elimination of synchronization entirely from a class (see "Immutable Objects").

Performing a multithreaded micro-benchmark is more complicated than running a simple loop. In the simplest method, multiple threads are spawned, each looping over the benchmarked code; the reading is not started until all threads are successfully executing the code, and is terminated before the threads are shut down.

Thread Reuse
Newbie programmers make the common mistake of spawning a new thread for every request or other action. This can result in worse performance than using a single-threaded approach; the relative performance degradation is worse the quicker the individual requests can be serviced.

Writing Your Own Threading Code
Even better than the .NET thread pool, with its dependence on delegates, is to write your own threading code. Instead of using QueueUserWorkItem(), you typically write your own queueing code to coordinate work items. This also allows other benefits such as priority-based queueing.

Immutable Objects
Immutable objects are objects in which the data cannot be changed. In most cases, this is achieved by setting all fields in constructor methods and providing only property getters and/or methods that retrieve data from the object, without any mutator logic whatsoever. Many classes in the .NET Common Type System are immutable: System.String, System. Drawing.Font, etc. In addition, care should be taken that any values returned from property accessors, etc. are immutable as well. Otherwise, this data may be copied to insure the integrity of the object itself.

Data Copying
This flies in the face of the advice given earlier, to minimize the use of objects. However, it's really the other side of the coin from immutable objects. Object copying allows you to use the data in a non-immutable object, but in a way that still completely avoids synchronization. The more highly multithreaded the environment, the more strategies like this make sense.

Read-Write Locks
Synchronization issues in managed code mirror those in databases. In some situations, optimistic concurrency strategies can be used; in some dirty reads are acceptable, etc. For situations in which a structure is seldom updated and often read, the ReaderWriterLock class can give significant performance benefits over simple synchronization. It allows either multiple read access or single write access at once.

Minimizing Synchronized Blocks
The use of the [MethodImpl Attribute(MethodImplOptions.Synchronized)]attribute should be avoided, as it always locks an entire method and is also non-standard C# usage. Instead, the lock keyword or one of the System.Threading classes should be used. Wherever possible, adjust the start of a synchronized section forward and the end backward. Do whatever you can to decrease the number of synchronized operations.

Hope you find this article interesting. I think it really important to understand and know how to right efficient C# code.

Yours,
Rock Rotem Bloom

Thursday, November 1, 2007

.NET goodie #1: Declaring variables inside or outside of a loop impact performance?

Did you ever wonder......
Is there any difference in terms of performance between declaring a variable outside of a loop like this...

string str = "";

for (int i = 0; i < 10; i++)

{

str = "hello";
}


...versus declaring the variable within a loop and using it like this:


for (int i = 0; i < 10; i++)

{

string str = "hello";
}

So what do you think???!!! The asnwer in this case will be:

The first example is actually slower, because it causes one more string allocation to occur. In c# the "creation" of variables is always moved to the top of methods in the compiled IL. Therefore your second example is exactly the same performace as:

string str;


for (int i = 0; i < 10; i++)

{

str = "hello";
}


Notice this does not assign an initial value to str of "".

SQL Formatter, beautified SQL statements

Hi,
There is a free SQL formatter that supports several databases like:
oracle, mssql, db2, mysql etc...

You can take a large SQL statement with parameters and the "SQL Formatter" will beautified the SQL statement for you. It will add line breaks and more.

You can found the cool SQL Formatter here.

Great tool for SQL debugging.