Wednesday, May 30, 2012

Query Execution Plan

First of all: Ignore the DB Modeling, DB Design mistakes and so on. This post relates to a DB made by a company a decade ago and I will not question the design choices.

So, the topic: migrating from SQLServer 2000 to SQLServer 2008. This is NOT a simple and direct upgrade. There are several issues that will arise when doing this process.

   - Is de DB mirrored?
   - Replicated?
   - Indexes?
   - Users?
   - Filegroups?
   - DTS?
   - How big is de DB?
   - Can it be offline while migrating?

On and on and on...
But after all this has been accounted for and after the migration process was successful, there is still a lot of work to do! Focusing on the issue, consider the following table:


And lets populate the table:

(click to enlarge)

Now, the applications were doing something like this:

(click to enlarge)

In SQLServer 2000, this worked because it's granted that when the field is "dateField" then the Value IS a date (or datetime). However, after the migration to SQLServer 2008, these kind of queries stopped working. The exact same query with the exact same "data" in the tables didn't work. Why?

Finding the issue... Well, the SQL Server 2008 query optimizer actually changed the execution plan and it does the second WHERE clause first:

(click to enlarge)

Meaning, it's trying to convert all Values from the table and then filtering with the LIKE clause. So, It's trying to convert non-datetimes (that actually exist in the table) and "bum"! The "fast" solution was to replace the LIKE with an EQUAL clause:

(click to enlarge)

This way, the optimizer will not change the query execution plan:

(click to enlarge)

And now it works:


My advice, however, was for them to review the database. In future versions of the SQLServer, similar problemas can arise.

Wednesday, May 9, 2012

ExpressionServices

Writing code dynamically can be very useful in some scenarios. In a recent WF project I needed to build a workflow in code and save it to XAML, executing this saved workflow definition latter on. But there's a problem: Lambda expressions do not serialize to XAML. This is where the ExpressionServices "utility" comes in. Using the "Person" type for this example:

(click to enlarge)

Just two properties. Now, keeping in mind the above statement about Lambda Expressions, when generating the Workflow we need to use a method from the ExpressionServices type to convert the Lambda Expressions to Operator Activities:

(click to enlarge)

Finally, lets generate the XAML using XamlServices:

(click to enlarge)


That's it!
Final note: According to MSDN, "this conversion is supported for a limited set of lambda expressions only". I couldn't, however, find a reference to what set of Lambda Expressions are supported.

Edit: Yes, I usually don't place comments on the code I share here. That's because it would make the code less obvious. It's a simple demo.