Day 14

So where was everyone now getting in touch or asking questions about work and other projects in 2012?

I’m not complaining of course, it’s a fantastic start to 2013, I’ve got some interesting projects that I’m already wrapped up in and the learning is never ending, not just technologies but also methodologies, and aside from “Fitter” I’m covering off the three words aim for 2013 well (and taking into account the discussion I had the last time I had my haircut, I’m almost being epic and maybe even awesome in 2013 as well)

;-)

SQL Query perfomance v Stored Procedure issues

So I have a lovely big SQL which is returning over 2000 rows of data (going back to 2005) which is a recursive Common Table Expression (CTE). To perform certain calculations we have to compare each row with the previous days data and update the data, it’s quite expensive but as a piece of SQL will run in around 1 minute on SQL 2K8 running with 8Gb RAM under VMware. The same query as a stored procedure takes 27 minutes to run!

Yep, the exact same SQL when running as a stored procedure is over 20 times slower to produce the same result set.

Time to hit Google and the second result on the query “” brings up this little beauty of a posting.

So the first check:

SET ANSI_NULLS ON

Yep, got that one covered, the second one however of using local variables in the stored procedure query made all the difference.  In this stored procedures case I added in three local variables which took the values of the passed in parameters and that was it.

*BOOM*

(Note: the computer didn’t actually blow up, that would be irritating.)

The stored procedure now executes as fast as the SQL query.  Amazing, and now that I have a working and more timely query, hopefully I’ll have a happy client.  Now to update all of the stored procedures in the application.

Day 12

Back in the SCRUM, well the stand up anyway, next week we’ll be grabbing a wall to post up our 100+ user stories, which we’ll then break into smaller tasks, just creating and logging the user stories makes a difference to how the project feels, although it would be nice if we didn’t keep adding 10+ stories a day (although they mainly go in the backlog)

Oh and some snow fell, 2 hours in the office, then I headed home before the public transport fell apart.

Day 11

Yep if I don’t keep this up I’ll lose count.

Today started off well with me playing with D3.js on the train on the way in, and also finding out that my RoR code that I have in Github had passed its tests in Travis-CI – ok so it had also passed the tests on my laptop but it had taken me a couple of steps to get the right YML file to make it work in Travis.  Thanks to this article on Stack Overflow I found the magic was to do with the Rake DB Migrate task (I was on the right track which was nice)

My .travis.yml file is now like this:

language: ruby
rvm:
- 1.9.3
env:
- DB=sqlite
script:
- RAILS_ENV=test bundle exec rake db:migrate --trace
- bundle exec rake db:test:prepare
- bundle exec rspec spec/
bundler_args: --binstubs=./bundler_stubs

Which made things work, now every time I push my code to Github it will be grabbed and tested by Travis, overkill for a sample app with a known outcome, but good practice.

The down part was getting into the office I was working today and losing one of the rubber earbuds from my earphones, as I type this I’m listening to music in half stereo (not mono) and being subjected to the noise of my fellow commuters.

Once in to the office du jour it was down to some data and SQL checking, not too much of an issue except this was SQL including some lovely CTE queries that I hadn’t looked at in over 3 months, took a while to get going with it, and there were some changes to make (of course) with the client “helping” me write my code. Still managed to validate the results from the queries and client is happy and of course a happy client is what we all want.

So heading back home and more D3.js awesomeness. (and writing a blog post)
;-)

Day 10

So it’s another day, on the commute in I played around with a bit RoR as I continue to work through the Rails Tutorial and started to get back to working with JavaScript more using the helpful guys at Codecademy

So clearly I can tick today off as a day that isn’t wasted (my standard rule is that something learnt in a day means the day hasn’t been wasted!)

Which is handy as I’m working on a data visualisation piece at the moment which will make extensive use of JSON based data from REST queries with a view to being displayed using D3.js so even more learning going on.

;-)

Think it is safe to say that as part of my three words for 2013 I’m working well on “better” sad to report that “fitter” hasn’t even started, although I’m back doing my daily physio exercises – it’s a start!