Tom
This user hasn't shared any biographical information
Homepage: http://tomandrews.co.uk
Posts by Tom
MS SQL with insert into select
Jan 28th
Today at work I had to debug an MS SQL Stored Procedure that I wrote and ran fine on the internal test systems but not on the customer’s pilot site; as is the case with strange failures. The script was cloning a row so it could be modified later without effecting the original. To do this I performed an insert with a select as below:
[ccn_sql]
insert into exampleTbl
(a, b, c, link) — …etc…
select
a, b, c, @new_entity — …etc…
from
exampleTbl
where
link = @old_entity
and id not in (‘1′,’2′)
[/ccn_sql]
The stored procedure contained 3 queries doing similar things to those above (only 1 with the not in); the id was indexed. The query was timing out (even with a timeout of 30 seconds) when executed the C#/ASP.NET website on the customer test system. I was highly confused when I saw this taking so long. What had happened?! The select ran effectively instantaneously on the customer system and only ever returned very few rows a maximum of 5 with all my test data. How could it take more than 10 seconds (unscientifically split between the 3 queries) to retrieve and insert 15 rows?! So the investigation began…
A colleague of mine mentioned that it could be to do with the not in. This would scan all (1.3 million) rows in the table which could take a while even when using the index. Some further investigation discovered that this was not the case as all 3 versions of the insert ran equally as slow – with and without the not in!
I then spent some time trying to find another part of the system inserting into this somewhat populated table and checked to see how it ran expecting equally dogged performance, but this was not the case. The code was similar to that below:
[ccn_sql]
insert into exampleTbl
(guid)
values
(’some guid’)
update exampleTbl
set a = ‘1′, b = ‘hello’, c = ‘dave’ — ..etc…
where guid = ’some guid’
[/ccn_sql]
This code generated the guid in the C# inserted it and then updated that row with the rest of the data.
So as this alternate method was running fast it and meant my issue was either to do with the large number of parameters (20-ish) being used during the insert or the use of the select. The former seemed too common to cause such a huge issue even with 1.3 million rows in the table; it must be the select. However I had already found that the select was executing near instantaneously outside of the insert and was very sceptical that creating a table variable would fix the issue. Surely MS SQL was running it just the same inside as out so the issue must lie with the overpopulated insert. But I had to test it as this was all really guess work with no numbers to back it up. So I added the table variable and SHAZAM it worked near instantly! >30 seconds down to <200 milliseconds. WIN! + confusion.
All I can say is that I will never be using a select in an insert statement without first performing any work on a table variable so that it only has what it needs and inserts that. This raises the question of what the query optimiser is doing when it encounters an insert with a select? Surely it can’t be too crazy to ask for a select in an insert to run just as fast outside of it?! Either way it was a fun 2 hour debugging session and I’m glad I got to the bottom of it… eventually.
Epguides.com Ubiquity command
Jan 24th
So over the past week I have written another ubiquity command. This time it is for epguides.com to easily find the date of when the last episode of a particular TV show was and when the next one will be.
Here is a quick demo video I made. With actual commentary and editing this time!
The command only took about 3 hours once I got my development environment set up.
You can subscribe to the command by visiting the project page with ubiquity installed in Firefox.
The Brothers Awesome
Jan 17th
Before the catastrophic failure that befell me yesterday I watched The Brothers Bloom.
My expectations for this film were already through the roof after seeing some of the reviews it was getting and the fact it was made by Rian Johnson who made Brick a film that 2 1 of my servers are named after. But, what can I say? I loved it! I was rewatching it when my server fell over (because it was too awesome – that kills hard drives doesn’t it?) and less than 48 hours after my first viewing.
Every great film has a great opening and this film is no different. As I don’t want to spoil the film too much for you there is just the original sketches and I believe Rian Johnson doing the voice over.
The dialogue in this film is spectacular; witty, genuinely funny and multilayered. Brick was very dialogue heavy and some people didn’t like it because it was quite tricky to follow but The Brothers Bloom is much more “middle of the road”. It is less stylised and hence uses common language which helps to make the whole film much more approachable and the characters more relatable. The depth of the characters wrapped up in the lines of dialogue is vast and complex and just increases on repeat viewings. Unlike most “mystery” films made recently this film makes sure you are never sure what is really true at any point during the film. I kept watching as I wanted to know what was really happening.
Interesting characters and good acting builds upon the script and directing with Rachael Weisz as Penelope was awesome; a character that grows over the film and still keeps an air of mystery about her. Mark Ruffalo and Adrien Brody were great as the brothers Stephen and Bloom respectively. Their relationship while flawed is both complex and completely believable especially as you learn more about them over the film. Rinko Kikuchi as Bang Bang says more than a nearly mute character ever has.
As I said at the beginning this is a great film and one I recommend everyone goes out and rents straight away and I must say it looks stunning on Blu-Ray although you wouldn’t lose anything by watching it on DVD. I really hope Rian Johnson keeps making films because if he can inject the same level of depth and quality into his next projects then I will be more than happy.
P.S. I also loved the fan service with the use of Joseph Gordon-Levitt and Nora Zehetner at the party in the bar who both played major roles in Brick.
A Fundamental Shift
Nov 21st
OK, so the big announcement on Wednesday was the updates to ASP.NET AJAX libraries at PDC… you didn’t hear about that?! Were you sat in a cave? It integrates perfectly with jQuery (as a plugin) and .NET MVC and you no longer have to include 5+ different JavaScript libraries in the right order just to use a Calendar control.
If thats not a fundamental shift in code reduction I don’t know what is!
OK, OK the big news was Chrome OS perfectly timed to steal Microsoft PDC’s thunder and boy have they! The blogosphere has been alight with the sound of “ooooh cool” and “dude thats retarded”. I think it will have a huge effect on the web and general purpose computing as a whole. If you don’t know what it is – watch the video below.
I like Chrome OS for a few things that some people don’t understand or don’t seem to appreciate.
Chrome OS is not built for the current generation of applications it is built for what Google believe the future of applications is going to be. It is what they have been doing since they first released search some 10 years ago. Google have been building complex applications (100s of thousands of line of just JavaScript if you believe Microsoft) completely detached from the standard desktop environment. That is the power of the web.
As a developer I don’t care if you are running an x86 Windows machine with the latest upgrades or a Linux ARM machine that only has 128MB of RAM and has X installed. As long as it runs as a web browser and I develop on the principals of graceful degradation or progressive enhancement (there is no meaningful difference) then everyone gets access to the functionality. All they need to do is point their browser at the URL. Nothing needs installing it just works.
These “web applications” that you access can be used from any device at any time day or night don’t (or aren’t supposed to – ignoring browser bugs and cookies) physically edit your system. I’d be pissed off if YouTube changed my desktop image to it’s logo or something fundamental when I went to the site. It just makes no sense it doesn’t need to. I have an account there, why does it need to leave on my computer something it already knows? The web browser provides the sandbox environment for these applications to use and with the “cloud” can make your actions permanent and accessible from anywhere.
Google Chrome OS is taking this migration of applications from the desktop to the web to its logical conclusion. It gives you a machine which is nothing more than a web browser and wraps some nice features (moles and panels) that make the experience more pleasant. That really is it. It does some fancy things with boot that are fairly irrelevant. It looks to be using CoreBoot which most users won’t be able to use if they don’t buy a Google machine as this requires you to flash your own BIOS . Secondly, the demo was running on a netbook with a SSD drive which has a significant speed advantage. Also as it really it is only one app a lot of the system can be changed so that it doesn’t have to deal with that random Perl script you wrote and it is designed not to let you run that script - it is just a security risk and managing it is a performance hog.
Now, a lot of people right now (reading this or not) are thinking Chrome OS is going to suck as I can’t customise it; I can’t install apps, I can’t write that random Perl script to regex HTML for fun. People for some reason think that for every task they need an offline application because that is the way it has always been and how it always should be. But as discussed above, it is better for the developer and the end user that we use web apps. The end user gets a constantly updating app (in a perfect world) and the developer constantly supports all environments and doesn’t just support Mac or Windows etc.
For any platform there needs to be a killer app and yes we have a lot of good apps (GMail, YouTube, Digg etc) but they exist currently on our current platform there is nothing pulling us to use just Chrome OS or another purely web OS yet but we are nearly there.
The main two issues stopping everyone from rushing to the pure web OS. Firstly is the worrying question of peripherals. If I have my music/video collection on an iPod, HDD or local server then how am I supposed to browse that on an OS that doesn’t allow me to modify the hard drive? Will I l load a web app to do that that runs purely JavaScript and is allowed to read/access them? But how do I save more? Also, if I have a SatNav system that requires updates how will this OS allow this device to talk the web? The second issue are the apps. A lot of things you do on the desktop at the moment have to be done there because there are equivalent apps on the web for example, high performance gaming and truly assisted code development as with a powerful IDE come to mind. With so much processing required for each of these how is a web app meant to compete? Can it ever?
The Future
As I said at the beginning, Chrome OS is written for the future and is not yet ready to complete replace your desktop/laptop. It is for a time when most people have a fast 10Mbps connection always available connection, when content providers actually make their products available on the web and when there are competitive products for those on the desktop. And, I don’t believe we are that far away – especially given how quickly things change on the web. Lets look at my most commonly used applications and see what equivalents there are/will be in the future.
- Web browser (Chromium/Opera/Firefox) – Well, this has been negated some what given that I can access everything directly from the OS. But, what about testing my applications in them so they can be used by other web OS or browsers?
- IDE (MonoDevelop and VisualStudio) – Both of these are powerful IDEs and provide a startling array of functions but nothing that I do not believe could not be built and enhanced by them being web apps. Bespin from Mozilla labs is a good step in this direction but has a long way to go before it can compete.
- Media Player (Boxee, VLC, Miro and MPlayer) – Now here I come unstuck as I have a collection of videos on my server that can only be managed and distributed to my HTPC, Laptop and Desktop from this set up. If in theory I could stream them when I wanted and how I wanted then it could be possible to use a web app. Boxee running on my HTPC adds such a nice interface and lets me control it from my phone. There would need to be one hell of a web app to allow that. Having said that Boxee aims to bring most of its media from the web but is not a web app. Maybe it could become one? But at the moment there is really only iPlayer, Spotify, Hulu and YouTube for legal media content from the web.
- Games (Steam and Wine + Steam) – Now this is a prediction not a solution but it is – the return of the dumb terminal – OnLive. Or a more short term shift to all gaming on consoles and none on the desktop/laptop.
As you can see its not there yet but its on its way. 2 years and I wouldn’t be surprised if this web malarkey caught on and we actually had a fundamental shift.
Lagging behind
Sep 7th
So I finally updated my torrentz.com command so that it is now compatible with Ubiquity 0.5’s Parser 2. Despite this taking me nearly 4 months to get round to it was shockingly easy to do – literally only 10 minutes of work.
Now everyone can go and search torrentz.com from anywhere on the web ubiquity ctrl+space ftw!