Just recently, Rick Osborne told me about the NOLOCK SQL directive. I had never heard about this but Rick told me that it would help improve the performance of my queries. After some quick searching, I found a great page on Sql-Server-Performance.com. Apparently SQL server puts a locking mechanism around all data access and manipulation to prevent things like dirty reads and the reading of uncommitted data. I was totally not aware of this - SQL server just worked and that was good enough for me.
Apparently though, these locks (as with most locking) comes with a perforance hit. This is good though as it ensures the integrity of your data. However, there are times when you just don't care. Some tables, especially look up tables, are not updated often (if ever) and locking around these brings on unnecessary overhead.
As Rick Osborne (and the page above) explains, using the SQL directives NOLOCK and ROWLOCK can circumvent this SQL Server's locking mechanism and speed up queries. Here, I demonstrate using it on a SQL Join query in which I get Blog entry information:
SELECT b.id, b.name, ( t.id ) AS tag_id, ( t.name ) AS tag_name FROM blog_entry b INNER JOIN blog_entry_tag_jn btjn (NOLOCK) ON b.id = btjn.blog_entry_id INNER JOIN tag t (NOLOCK) ON btjn.tag_id = t.id
Notice that I am using the NOLOCK directive on the blog_entry_tag_jn and the tag Tables. The Tag table pretty much never gets updated and the blog_entry_tag_jn (joining of entries to tags) table gets updated ONLY when I add or update a blog entry. Due to the low frequency of updates, the requirement for locking on these tables is (next to) pointless. By using the NOLOCK directive I am asking SQL to ignore all locking mechanism surroundings those tables and proceed directly to data-retrieval. Theoretically, this should provide a small performance gain. Sweet!
Want to use code from this post? Check out the license.