Saturday, 30 July 2011

Entity Framework, Parameterised Queries and Plan Caches.

Firstly, apologies if you were expecting regular (or frequent) updates here. I've been pretty for the last few months actually building software rather than writing about it. I've been using a lot of tech that I've only really dabbled with in the past, so most of the things I've been learning have been the same noob things you can read about anywhere.

One piece of tech I'm using commercially for the first time is Entity Framework (via Ideablade Devforce on a silverlight client). We started the first week of user training en-masse this week, and immediately noticed unusually high memory usage on the SQL server. Our database is approx 1.7Gb (Data + Indexes), yet the machine shot to 3.8Gb of the 4Gb Memory available (4Gb 64bit SQL 2008 R2 on an ESX VM).... and stayed there.

Investigating the issues showed a significant amount of memory consumed by SQL's plan cache, with very low use counts:


SELECT objtype AS [CacheType]
, count_big(*)AS [Total Plans]
, sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]

Running the above on or server returned the following for the AdHoc cache:

Adhoc 6919 1471.867187 4 930.242187 5565

Wow, almost 1/4 of our memory is being consumed storing cached execution plans that were only used the one time they were generated!

The Cause:



Profiling the SQL being run against the server showed that the queries being genereated by EF were not parameterised. There's a great writeup from Julie Lerman about when EF uses parameters and when it doesn't, but I haven't had a chance to experiment with our codebase to try and get the parameters queries happening. I also don't know whether to blame EF, DevForce or myself. There's always Phase 2 of the project to improve things, but what to do for Phase 1?

The Solution:



It turns out SQL 2008 has an advanced option perfectly suited to this scenario. Kim Tripp (as always) has a great writeup of the 'Optimise for AdHoc Workflows option', and is the source for the SQL shown above. You can read the nitty gritty details of how the setting works elsewhere, but here's the results for the query above after enabling the setting, clearing the ProcCache and letting the system warm back up to the same number of plans in cache as the day before.



Before:

Adhoc 5022 1154.125 4 721.398437 3984


After:

Adhoc 5153 425.205192 2 183.306755 4580

And for the visually oriented:

Friday, 17 September 2010

Tims Law of Software Estimation #34

"An estimate to redevelop an application must be >= the time required for a business user to become competent in that application."

Saturday, 30 January 2010

Traps for young Players

Experience - Getting the test before the lesson sucks.

Note to all (although this may appear obvious as you read), when you have a single copy of a windows licence key to use, and you are trying to run multiple instances of that licence via differencing VHD's, ensure to Activate the master VHD from the physical hardware, ie boot it up, as you will your differenced disks.

Otherwise as the differencing disks are run on different (physical vs virtual) hardware, your previous activation is ignored, and future attempts at activation will fail dismally.

Bugger.

Thursday, 17 December 2009

Quick Fire Tab-Orders in Winforms

Todays productivity tip for Winforms developers. This may work in other UI technologies, but I haven't checked. Here's the 10 second way of resetting the tab order on your control without resorting to manually changing the property on every control.

1. Open your form/control in the Visual Studio designer
2. Go to View->Tab Order
3. The current tab orders are now highlighted for each control.
4. Proceed to click on each control in the order you want them to tab.
5. Done.

If only I could roll back a decade knowing this, I wouldn't drink as much as I do today!!

There's now no excuse for poor tab orders, no matter how tight the schedule.

Thursday, 19 November 2009

Linux 2009

Every year or so I give linux another go. I've spent untold hours over the last decade or so using it, and even had it as my main OS for a while back in the Redhat 6.2 days, but I've never been totally comfortable with it.

Still, variety is the spice of life, and my netbook (Lenovo S10) needed a rebuild, so I thought I'd give the new optimised Ubuntu Netbook Remix a go. This is my story.

Bonus points for being able to find the download link from the official site.

After the included Bootable USB tool on the .iso failed to display my selected ISO, I ended up using the also-referenced Unetbootin tool to copy and make bootable my USB stick. Except it didn't work. No errors reported, but it failed to boot (Command manager not found).

Trial and error led me to format my USB as Fat32 rather than NTFS, when Unetbootin still ran without error, but this time booted correctly and like a dream, up comes UNR.

I have to say the polish and attention to detail has improved leaps and bounds in the last few years, and the default display is very usable.

The bottom row of game icons is only half displayed, but you can't have everything.

Oh, and support for my wireless card isn't enabled by default, but fortunately the drivers are included, and you're prompted that they may be of use. I haven't yet been able to get them installed. The machine has either hung while restarting after applying the driver (I thought only windows had to restart after driver installs), or hung prior to the drivers being activated.

But generally, it ran well enough off the USB stick for me to blow everything away and install it on the HDD. The install went flawlessly, and is certainly a nicer experience than older distros I've tried.

I moved back to my main desk to use the wired internet connection in an attempt to get wireless drivers downloaded. USB keyboard/mouse worked perfectly. 2nd monitor was detected and configured correctly, but rendered ugly white stripes outside the inbuilt monitor res of 1024*600, and failed to respond to any mouse clicks other than a restart. After a restart the menus render perfectly, and everything is fine and dandy in monitor land.

Except apps only open on the leftmost monitor (my large one) which is good, but can't seem to be moved to the smaller monitor (note, just found unmaximise tucked away in a context menu), which also has the taskbar etc on it, so swapping windows involves a mouse trip to tiny font land on the netbook. Admittedly you could argue a netbook focussed distro may not have the nicest support for dual monitors, but then I'd think most people with a little netbook like this would plug into proper peripherals as often as possible.

Firefox runs fine, albeit it pretty slowly compared to windows. Gmail works fine, visit Youtube and get prompted to install Flash. Fair enough.

Adobe site suggests four ways of getting flash, one (.deb) marked for Ubuntu 8+, download, open with default application... apparently i don't have libnspr4-dev, whatever that is. Oh well, at least I won't get spammed with flash ads.... or youtube vids.

There's very nice, easy access to a stack of software via the Ubuntu Software Centre. Everything I've looked at so far says it's "not available in the current data", and can't be installed.

I dunno, I keep giving linux it's chance, to the point where a fairly pro-linux flatmate of mine throw up his hands trying to get a distro to work on my old cobbled together PC. But I'd really hoped that on stock hardware, with a a targeted release things would be better than this by now. I want linux to be fantastic, I want to have another option than Windows, but I can't see this install lasting any longer than a week.

It's just not ready yet.

Thursday, 30 July 2009

Perspective

From the Supersite blog:

In a survey conducted last fall, IDC's Kevorkian said only 4.8% of those with a portable media player reported having a Zune, while 61% had some sort of iPod.

So, in late 2008, the Zune actually had 50 percent more usage share in the MP3 player market than the Mac did in the worldwide PC market.

Given I've never met anyone with a Zune, that really puts the number of Mac fan boys (I jest) into perspective!

Wednesday, 15 July 2009

.NET 4 Data Annotations

I'm confined to quarters for a week or so, so I figured it's about time I had a play with the new RIA services stuff for Silverlight, and in a lot of ways it feels like it's really close to something really awesome, but unfortunately not quite there yet. Bear in mind I'm looking at it as a replacement to building ASP.Net web apps for internal line-of-business users.

Some Good Things:
End-user feel is pretty nice out of the box
Isn't tied to any underlying data/object technology... I demo'd against an Entity Framework, and also POCO + sprocs

Whilst it can be hard trying to work out how it all hangs together (because of the code gen involved there's a bit of magic to wade through) I don't think it'll take too long to get a feel for it.

Unfortunately it seems to fall down as soon as you step beyond the trivial samples done as online tutorials, and into the real-world of writing enterprise apps. The rest of this post is going to revolve around complex validation. And not crazy 'I need to call a web service to determine if this is valid at this point in time' type complex validation... something really pretty simple in business apps - a conditionally mandatory field.

Imagine a text field, which is required to be entered if and only if a flag is set on another property... it can be optionally populated whenever you want, but if the flag is set, it better be there.

public bool IsFunkyRequired{get;set;}

public string Funky {get;set;}



As the new DataAnnotations suite coming in .Net 4 is attribute based, it suffers the same problems as the Entlib Validation block (and the original Avanade library): because the validation is based on a single property via the attribute, any sort of cross-property validation is surprisingly difficult.

Funky can't get marked [Required()], because it's not required all the time.

Adding logic of the form
if (String.IsNullOrEmpty(Funky) && IsFunkyRequired) { throw new ValidationException(blah blah); }

into the property setters causes issues when initially loading data from the database (eg, when the flag is set but the text field hasn't yet been loaded), and doesn't really fit in with the whole attribute based way of doing things.

The next logical step is to write your own validator attribute, by (funnily enough) inheriting from ValidationAttribute. After realising that the obvious IsValid(object value) method is not sufficient (as it is only useful if you care about the value set in the property, which isn't enough in this case), and fighting with the wonder that is the ValidationResult class, you eventually reach the point where you have a reasonably concise validation method, as follows.



Public Class FunkyRequiredAttribute
Inherits ValidationAttribute

Protected Overrides Function IsValid(ByVal value As Object, ByVal validationContext As System.ComponentModel.DataAnnotations.ValidationContext) As System.ComponentModel.DataAnnotations.ValidationResult
If (TypeOf (validationContext.ObjectInstance) Is TestClass) Then
Dim obj As Class1 = DirectCast(validationContext.ObjectInstance, TestClass)
If (String.IsNullOrEmpty(obj.Funky) AndAlso obj.IsFunkyRequired) Then
Return New ValidationResult("Funky must be populated when flag is set.")
End If
End If

Return ValidationResult.Success
End Function
End Class
Forgive the jump to VB, I'm trying to be equal opportunist here.

And this surprisingly works really well, no data is committed to the database if the object is in an invalid state.... of course you don't get any error messages raised either.

Spelunking into the genned code shows that our custom attribute hasn't been applied to the genned Entity class for the client side, hence no client-side validation when tabbing off fields.

Luckily Guy Burtstein has some older info showing some ways around this. Note that the [Shared] attribute doesn't seem to exist any more, you just rely on a well named .shared.vb or .shared.cs file name.

At this point, we have a custom client+server validator that runs quite happily.

Except it doesn't work.

You see validation is applied prior to the property being set, so if we clear the Funky field on an entity the validator returns success, as the property is still set... this applies when adding data to a previously invalid item: the error can't be cleared.

Changing the validator to operate on the passed value (ie, the new value we're attempting to set) only works if we handle each property type it's applied to, for example the following works:


Protected Overrides Function IsValid(ByVal value As Object, ByVal validationContext As System.ComponentModel.DataAnnotations.ValidationContext) As System.ComponentModel.DataAnnotations.ValidationResult
If (TypeOf (validationContext.ObjectInstance) Is TestClass) Then
Dim obj As TestClass = DirectCast(validationContext.ObjectInstance, TestClass)


If (TypeOf (value) Is String) Then
'changed the Funky field
Dim s As String = DirectCast(value, String)
If (String.IsNullOrEmpty(s) AndAlso obj.IsFunkyRequired) Then
Return New ValidationResult("Funky must be populated when flag is set.")
End If

ElseIf (TypeOf (value) Is Boolean) Then
'changed the flag
Dim b As Boolean = DirectCast(value, Boolean)

If (String.IsNullOrEmpty(obj.Funky) AndAlso b = True) Then
Return New ValidationResult("Funky must be populated when flag is set.")
End If

End If

End If

Return ValidationResult.Success
End Function
Except it doesn't, really. Because this is considered two separate validations by the system (because it applies to two different properties), you have piss-poor usability.

Starting with the flag and field both populated, you clear the field, adding an error to the Funky control. You then clear the check box, placing the object in a valid state, but because the error is on a separate field, the old error remains in place on the screen. There isn't a tester known to man that would let me slip that mickey to the users.

Better yet, because the change to clear the textbox was considered an invalid move, it never really took place. The Validator.ValidateProperty method called by the genned Entity throws a ValidationException if any validation on that property fails, meaning the property setter is never called (validation occurs before the setter is called remember).

This all means when you submit changes despite there being an error on the screen, the checkbox is cleared, but the textbox still has it's original value in it.

[Side Rant]
Why or why are we throwing exceptions for what is obviously a very common path through the system? It fails both of Rico's almost rules regarding exceptions.
[/Side Rant]

I've only been playing with this for a day or so, so it's entirely possible I'm missing some really obvious stuff that would make all these go away, and to be fair it's not at a go-live stage yet, but I'm really worried this is taking us down a path we don't want to be on.

These issues all seem to stem from two basic premises (and I'm not sure which started the other, or if they were unrelated):
1. Validation is a per-property behaviour, as opposed to per-object behaviour
2. Entities are not allowed to enter an invalid state, rather than not being allowed to be saved in an invalid state

I don't have any solution to this yet, but I suspect the CSLA Light stuff might be able to be jury-rigged in here somehow.