Chris Shaw posted a new SQL Quiz where he asks: “What are the largest challenges that you have faced in your career and how did you overcome those?”

Denny Cherry (@mrdenny on twitter) tagged SQLDenis who tagged George who tagged Ted who then tagged me so here is my story:

And yeah I just copied the above from someone else’s post and improved it a little. I hate these tagging things. But since George and Ted could kill me if I don’t, I will anyway.

1) The Guid mistake.

When I redesigned my system and the accompanying database, I decided to use Guids all over the place. It’s easier than integers because I don’t have to wait on the database to give me an ID. And I’m one of those guys that thinks an object should get a proper ID. So I tested the new system and everything ran fine. But like George, I didn’t test with a big enough dataset. And this one table has millions of rows and the primary key is on the guid because it has no other natural key, the two other fields are just nearly unique but can be the same. Anyway, a certain part of that system became slow and it was the part that, among others, pulled data from that table. 421 rows or multiples of that but never more then 10×421. And yet it was slow as hell. Loading a record with, among other things, the 421 rows took seconds, while it used to take less then a second on the old system. It took me a while to find out that the primary key and paging was the problem. With the Guid, the 421 rows were in several pages now (even 421 pages), so it had to do a lot more disk-IO. Changing to an int identity solved the problem because now all the rows fit in one page or two. Easily.

2) The nHibernate mystery.

For my school project, I had to write something and I decided to use some new stuff I didn’t know yet. Well that’s not entirely true. I knew the Java side of things because that was the main language we used at school and I decide to use all the same technologies but then the .Net versions. This was not fun since I could not get any help from the teacher and I got stuck several times, but I learned lots of neat stuff and I learned to read blogs from persons like Oren Eini, Jeremy Miller and Jeff Atwood; I never asked for help but found out most things by myself and although this was very frustrating it was very rewarding too. But the most frustrating was that nobody read the 400 page “book” I wrote and no-one ever looked at the 200,000 lines of code I wrote for this project. They just listened to the presentation and watched the demo and saw that it was good.

That’s it, for the rest I’m perfect ;-).

I will also tag 2 LTD members for whom (among others) I have much respect AlexCuse and damber