Tag: SQL

Posted on

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.

Posted on

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)
;-)