Better locking behaviour from Table variables

pauliomLeave a comment
SQL 2000 introduced a variable type called TABLE. This for me was one of the most interesting changes in the product since I’ve had a long a bitter battle with temp-tables (perhaps a subject for another blog) and I use table vars in a number of places. I’ve already discovered that you can use table vars to get better performance from queries that use involve OPENXML. I’ve never got the bottom of the exact reason but I certainly think that since it’s so easy to reproduce it’s more Law than Theory. The premise seems to be that the query optimizer really has no clue what OPENXML will produce, so any query requiring a optimizer plan (i.e. involving decent amounts of joined data). However, if you simply insert all the data from OPENXML into a table var and substitute the var for the OPENXML in the join the optimizer seems to do produce much better plans. So I can sort of understand that and, as I’ve said, the proof is in the executing and it does work…almost always, the is an issue…

My colleagues  were investigating a deadlock issue with a couple of long-ish transactions. The basic activity is:
TXA -> 1) Inserts Row X -> 2) Join using X 3) Complete
TXB -> 1) Inserts Row Y -> 2) Join using Y 3) Complete

The deadlock was occurring because (greatly simplified):
TXA -> 1) Exclusive key locks (on X) -> 2) Range Locks (X+ others)
TXB -> 2) Exclusive key locks (on Y) !Deadlock

So the problem seemed to be that the optimizer has asked for more rows to be locked in (2) than was necessary. Examining the query it was using a table var and the var usually only had one row in it, so why did it think it needed to take out such a wide lock? Drawing on my experience from OPENXML I suggested we try putting PRIMARY KEY on the table var. Previously I’d not done this because I was concerned about the overhead of creating a key for table var that would usually only have one row in it. However, the effect was dramatic. With the primary key in place the optimizer now seemed to realize that the table var would now only have key data in it, and therefore should be able to take out individual key locks rather than range locks. The result was that the deadlocks disappeared. The is obviously great new since this represents a significant boost in concurrency. I do have a warning note, should the number of rows inserted into the temp var grow to…well I don’t know and wouldn’t like to 2nd guess, the optimizer may at some point switch back to range locks. I hope not and by that time I hope to be exclusively on SQL 2005+ and can supply my own plans if necessary.

The (ahem) key point here is that don’t assume the query optimizer will get it right each time, especially on "tables" with no statistics, you need to give it as much information as you can.

Mono == Frustration

pauliomLeave a comment
Finally managed to find a 1/2 decent code editor for the Mac (smultron) so I thought I port my Wake Up On Lan code to mono and run it on the Mac. What an annoying tool mono is. After using the tools of dinosaurs (Terminal) I asked the compiler to do its work. It came back with… (4, 255+) Expecting ‘;’. Well fine but what the **!* does that mean? I sort of assumed that it was column and row. So I went to the 5th line and stuck in some rubbish characters to confirm my theory… (1, 124) Expecting ‘;’. Eh? Now I understand why they don’t supply some lovely rich IDE but I would hope that the compiler gives useful error messages not some random value that makes it impossible to use. Ok I’ll look up the error codes, couldn’t find any documentation on that. Off to the forums, surely I must just have something configured incorrectly. What forum, must be a community support. Nope some link to a commercial company. Ah here we go…an email list. What is this? A tool for the uber geek who takes pleasure in finding the most painful and awful way to communicate? I’ve half a mind to simply give up on this if "they" simply want to keep it a dark room project.

[Edit] A plus in Mono’s column… Although the error message is still *rubbish* I finally discovered that I had some generics code and I was using the v1 compiler. So I downloaded the latest version of Mono and updated (nice that it updated the existing version) and used the gmcs compiler (v2) and the file compiled without problems, so expect a WOL for OSX in the near future.

Wake Up On Lan for Pocket PC

pauliomLeave a comment
If you’ve read my previous blogs you’ll know that I became fixated on Wake Up Lan and published a code snippet for C#, you’ll also probably know that I try and cram my IT world into my Pocket PC. Well, tonight the enivatable happened, I wrote a WOL client for the Pocket PC. I’ll try and post it somewhere but if you need it in a hurray then drop me a line. It wasn’t quite a 100% port from the desktop version as the Compact Framework doesn’t support TryParse but it isn’t hard to replicate that feature. So now if I want to stream music to my stereo from my PC via the Pocket PC but my PC isn’t on, well I can now wake it up from the PDA, it will mean running for an extra 1 min to burn off those conserved calories from running up and down the stairs though.

Reporting Services

pauliomLeave a comment
Over the years I’ve kept an eye on SQL Reporting Services but I’ve never really tried it. Some of the demo’s have looked very promising and a particular project I’m currently involved with could really do with overhauling the reporting mechanism. So I booked myself onto a evening Reporting Services session at Microsoft to learn a bit more. However, before I attend the course I thought I’d better crack open the seal and have a poke around for myself…
 
One of the constant complaints levelled at Report Services is that it is difficult to install and if it goes wrong it’s really difficult to figure out why. Well I now have some sympathy for that. The installation was pretty easier but you do have to wear a number of different hats and there isn’t a lot of help when you miss part of the configuration. For example, I was happily running through a tutorial and it said, "now select run and deploy". Only to be faced with an authentication prompt for a web site. "What site? Ah I was supposed to have created a site? Ok off to the configuration tool. Big green tick next to the site so that seems to have been done, hmm. Oh well press create and see what happens. Ah no prompts, that seems to have done the trick". Not exactly the nicely task led process I expect, neh demand, of modern products. So I decided to plough through the tutorials. Nothing really exciting although the mix of vb like expression and SQL syntax is a bit odd. I’d would’ve expected you’d select one or the other rather than typing in one box and letting it figure it out and the code checking wasn’t very helpful, wot-no-intellisense. My first problem came with using parameters. I followed the instructions, even used the "copy text" facility but it just displayed an error when I went to the preview. But the error was the very definition of vague. I poked around and couldn’t persuade it to run. So I created a new report with my own settings and used a parameter from the start and that seemed to work. I copied the same code over and it carried on working, very strange. However, when I ran the report it would just repeat the first rows data for every row returned in the join, WHAT!!! So the join works ’cause I can see that in the "run grid" and the number of rows is correct. But it’s not getting the data for each row, very odd. Finally I just junked the whole layout and started again and it worked fine. So it seems very sensitive to the order you do things, the design surface seems to get very confused very easily. Good grief and I’m expecting some business user to write some of these, plus if I’m having this much hassle with a simple tutorial what happens when I’m writing something far more complicated? I’ll have no idea if I’ve written it incorrectly or I’ve simply done something in the wrong order. This is certainly a topic I’ll want to raise with the good folks at Microsoft. Still it promises so much that I really want to work, perhaps it will just be hope vs. reality?
 
 

Adventures with Silverlight

pauliomLeave a comment
I’ve started on the path to Silver(en)light(enment). I thought I use the blog as way of recording any gotcha’s or interesting facts.
 
Installation – The links to the Silverlight runtime didn’t work so I had to grab that from http://www.microsoft.com/silverlight/downloads.aspx
 
Once installed I went straight for the Chess sample. Watched .net murder javascript at chess, then watched it murder me at chess. I must try and play Javascript 😉
 
Onto the quickstart materials. First off you have to go through a brief manual installation involving setting up a web site. Then you’re off and running.
 
Interesting points
#1. Silverlight consumes unhandled exceptions. Anyone used to to writing .net application will, I’m sure, have see the unhandled exception dialog. Well true to form pretty much my first line of code had an error, I’d put the name of my textbox onto the canvas element rather than the "TextBlock" (why the change in name?). The code ran without any errors. So I put catch all thrown exceptions on in the debugger and there it was, "cannot cast" exception. That could have caught me out if I’d been a bit further down the code than the first event handler!
 
#2. Auto wiring up of names to fields. If you provide an XAML element with a "Name" then you can access that element via FindName and the like. However, if you try to use it directly "MyElement.Text=…" then you’ll get a compile error. The trick is to use "x:Name=…" this automatically create a field for the element in the code-behind.
 
 
 
 

Virtual PC 2004 vs 2007

pauliomLeave a comment
Finally downloaded the VPC of Orcas (Visual Studio 9 Beta) so I can have a play with Silverlight. After what seemed like a life time to download the files I finally managed to get it running on VPC 2004. However, it was a bit slow, typical Virtual PC speed…poor. Now I know that VPC 2007 runs Vista better so I thought I’d give it a go and I’m not disapointed. VPC2007 seems much faster than VPC. If feels like your running on old machine, capable but you have to be a bit patient at times. Whereas VPC2004 was like you were running on a PIII that was also doubling as a server for hotmail. So my advice to all you VPC 2004 users, get 2007 now!
 

Developing GPS software on the Loox N560

pauliomLeave a comment
"Enough is enough, I’m sick of these dodgy Pocket PC GPS tools, time to write my own". Hmm, I did wonder why there seemed to be so many dodgy GPS tools out there and now I know why. First, a bit of history…
When GPS first hit the Pocket PC it was a "simple" matter of attaching a GPS device to the COMM port and off you went. The trouble is that COMM ports are tricky little things and it’s very easy to get things wrong. One of the classic problems is that opening a port means no one else can access it. Worse still, if you crash big time and don’t release that resource, well I think everyone with a Pocket PC is all to familiar with the reset button.
So along came Window Mobile 5 which introduced a nice GPS abstraction. Basically a a bit of software that grabs the GPS port and then becomes a proxy to all the other applications wanting GPS data. That way any number of applications can receive GPS data, great.
Ok, so how do I use this? Armed with my .Net Compact Framework, Visual Studio 2005 and my trusty (?) N560 I went to work…except I didn’t, I couldn’t find the library for the GPS API. Ah, it’s not .net, oh great, time to get PInvoke out then. Luckly just as I was dusting off my interop gloves I discovered a sample in the Compact SDK written in C# and nicely encapsulating calls such as GPSOpenDevice and GPSGetPosition into a nice GPS class. It also converts all that odd GPS text into nice Position, Satellite, etc classes. I built it and deployed it to the PDA (that was very easy in VS2005) and…not a lot. It all seemed to say the right things, "Device ON" but I noticed that the GPS was not on. So the software was sitting there waiting for data but the GPS devices was most certainly off. So it seems that the software is talking to the abstract layer ok, but the device layer is not talking to the device. I wrote a quick COMM test using the SerialDevice classes (found in .net 2.0) and opened COM8. This started the GPS device and a few event handlers later I was getting GPS text stuff. So all that lovely abstraction layer just doesn’t seem to work and I’m going to have go back to parsing the GPS text just like all the other poor developers before me.
"Only now at the end do I understand".
[Edit] or so I thought…
Asking around someone pointed me to an MSDN page about registry entries and the GPS Driver layer. This page comes up a lot on Pocket PC forums because some devices have chosen to hide the GPS Control Panel and you can unhide(?) it by a quick change of the registry. However, I decided to have a look around the registry and see if there was anything useful. Once in there I realised that the driver doesn’t actually enumerate the device looking for a GPS it simply has the port saved in the registry. Then I realised that on the GPS Control there was this strange drop down that said GPS Device Port. I thought that was telling me the port it exposed but actually it is asking me to tell it the port it should be reading from. Once I changed that to COM8 my software leapt into life, hurray! Interestingly all the software that comes with the PDA all use the port method to access the GPS and are blocked when my, now well behaved, software runs. I wonder if this is why so many people have trouble with N560, GPS and Navigation software?

TMC Navigation with Navigon and Pocket Loox n560

pauliomLeave a comment
The story so far…
I have purchased; Pocket Loox n560 (built in GPS), GNS TMC (built in speaker) and Navigon 5 (from ebay).
The GPS navigation worked well but I could never get the GPS and the TMC on at the same time and my primary reason for buying all this kit is really about avoiding traffic jams rather than route planning so I was a bit disappointed. Reading various forums it seemed that I needed to upgrade Navigon 5 (MN5) to at least 5.1+. This presented a problem. Although the guy I purchased the software from assured me it was legit, there was no serial number, without a serial number there is no upgrade. I couldn’t buy MN5 now because 6 is out but that doesn’t work with TMC in the UK! So I went back to trawling ebay. Finally I managed to get hold of MN5.2 although worryingly the guy was selling it because he couldn’t get it working with a n560…eek. Finally it arrived this morning and I hit my first installation problem. The dumb ***** have created an installation that requires a C++ DLL that seems to ship with MS Office – I don’t have Office. So I manually copied over the CAB file and installed it myself, although bang goes the Clever Map software.
Connected all the various bits together, changed the GPS settings to GPS/TMC integrated, manually put 9600 as the baud rate COM 8 as the port and…it worked! The only slight snag is I can only get a very weak signal from Classic FM so I’m not sure how well the traffic avoidance is going to work, but I’ll have to be out and about to test that, but so far so good. I do feel a bit sorry for the guy I bought MN5.2 off, if only he’d had this blog to read

[Edit] Still a bit unclear about this. I’ve heard reports that the TMC now uses commercial radio stations, might explain why the auto-tuning went to my local, "better music mix" station. So rather than attempting to stay fixed on Classic FM is went on my usual commute letting it auto-tune. Well it wasn’t very successful. Admittedly whilst driving it’s difficult to know what the signal is like but when it was safe I glanced at the signal indicator. A couple of times it looked green, whilst close to home, after that it was red, red, red. At one point I was, ironically, stuck in traffic so I switched it the TMC tuning view. It would lock onto a strong signal and then move on. I assume (since the instructions and so utterly rubbish) this means its found an FM station but isn’t getting the TMC signal. I’ll have to try and find a list of stations broadcasting it.

 
[Edit] A bit more information of who is sending the TMC signals…http://www.trafficmaster.co.uk/shownews.cfm?num=379
 

The death of cross-platform code?

pauliomLeave a comment
After some recent exposure to Java and .net Mono I was pondering about the various advantages of abstracting the hardware into a virtual machine that the code runs within. However, it occurred to me that recent demonstrations of both technologies were run on virtual machine software, i.e. VMWare and Virtual PC/Server. With Vitualization (yuk made up word warning) improving seemingly by the month and with backing from the major chip manufacturers too it seems that Virtual Machine software is becoming the common place. It also seems that it is starting to make serious inroads into replacing machines for commercial use…albeit with, perversely, specialised hardware. So back to the topic, if you make the leap of faith that these Virtual Machine vendors will produce high performing products then why worry about cross-platform at all? Why should I port my Microsoft .net application to the .net Mono on the Mac when I can just fire up Parallels/VMWare and run it on a copy of Windows on the Mac. The expense of producing and maintaining software is almost always more expensive than throwing hardware at a problem. I’d argue that if you spend your budget on writing quality software for one platform and IF Virtual Machines perform, as the vendors are suggesting they will, then just run the software on the same platform on different kit via VMs.

Reality check. I’m not actually convinced that VMs will run at a decent rate for at least a year yet. Once we get 4/8 cores as standard then I think the story will look far more plausible.

[Edit] I’m now very interested in Silverlight, could this be the software VM that ruins my argument? I hope so!

Who is to blame for Credit Card validtion code on web sites?

pauliomLeave a comment
For no means the first time I’ve just purchased something from an Internet site and I got the, "invalid credit card number" message. What was wrong, yes I’d included spaces in the number. Now is so rubbish for the following reasons;
1. The spaces are there to help us humans enter the correct value, so why encourage mistakes?
2. How difficult is it to remove spaces from text if that’s what is needed, really? A semi-training monkey could manage that.
3. I’m putting one of my most trusted assets in the hands of developers who can’t remove spaces, hardly comforting.

So if it is you who can’t write these routines for major credit card companies or retailers, contact me. I have very competitive rates when it comes to code that removes spaces!

…and relax.