« Doh! Using this.mappings in Application.cfc | Main | CF8 tabbed-form layout gotcha in Firefox, Safari »

Using MS-SQL's NOLOCK for faster queries

A nice little coincidence just happened for me: right after my boss had emailed me asking if a certain click-tracking report in our site's admin section could run any faster, the developer next to me, who was looking through some of our existing code, asked me what this "NOLOCK" mention in an MS-SQL query meant. Talk about perfect timing! I probably wouldn't have remembered NOLOCK if he hadn't brought it up.

Used correctly, NOLOCK is a handy-dandy little feature. You may not know that your database puts locks on tables just like ColdFusion's cflock statement can lock variables in memory. Locks can either be exclusive, meaning that no other process can read or write the table because the first process is writing to it, or they can be read-only, meaning that other processes can read the table at the same time the first process is reading it also (but no process can write to the table, or else the reads might become corrupted).

So, locks are used by databases to ensure data integrity. But, every once in a while, if two processes are trying to access the same data at around the same time, locks mean that queries might run a little slower than usual because one process will have to wait for the other to finish before it can run. That's where NOLOCK can come in handy. If you're running a SQL statement for, say, a report, and you know it's going to take a long time to run and be reading lots of records, you can use NOLOCK to speed it up. Just add the phrase "WITH (NOLOCK)" in your FROM clause after any table which you want to read without locking. So, for example, in my click-tracking report code, which reads thousands upon thousands of click records while the clicks table is being updated with new click data:


SELECT clicks.entryURL, clicks.entryTime, sessions.userID
FROM sessions, clicks WITH (NOLOCK)
WHERE sessions.sessionID = clicks.sessionID
     AND clicks.entryTime > DATEADD(day, -1, GETDATE())

Now remember, locking is a good thing. Databases do it automatically so that your data doesn't become corrupted. So don't use NOLOCK to do any writing of data, or even for quick reads. But if you've got a long-running query which needs to read a large number of records, you won't hurt anything by reading without a lock. Plus, any other transactions won't have to wait to run, which could speed up the application for everyone else while you're running your report.

Note that MySQL also locks tables automatically like MS-SQL does, but doesn't appear to allow any way to prevent locking at all. You can, however, lock and unlock tables via SQL code.

Comments (113)

Good article. Thank you.

Thanx it cleared my concept.

MySQL doesn't lock tables like MS-SQL does. The locking mechanisms that MySQL uses differ depending on the storage engine. InnoDB in particular generally does only row-level locking: http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.html

MyISAM and some of the other storage engines do full table-level locking: http://dev.mysql.com/doc/refman/5.0/en/table-locking.html

It sounds like SQL Server behaves more like MyISAM in this case. MyISAM is popular for read-heavy scenarios, but it's locking mechanism is among the reasons InnoDB is preferable in many others.

Excellent article on NOLOCK.

with tranactSQL you can set it with the following one time command per the procedure, save some typing:

SET transaction isolation level read uncommitted


Nice. Very useful. Thanks a lot.

good and clear article. thanks.

This is a very usefull, thanks now i know what is my problem

Excellent article.....explained the concept in a simple and clear way....Thanks

Excellent article...its helped me a lot...

Thanks
Raja

really it hepled me alot
thanks for such a good article.
Expecting more articles........

It's well worth reading the MSDN blog post 'SQL Server NOLOCK Hint & other poor ideas.':

"NOLOCK / READ UNCOMMITTED HINT
This Hint is much more dangerous than its name suggests. And that it why most people who don’t understand the problem, recommend it. It creates 'incredibly hard to reproduce' bugs. The type that often destroy your end-users confidence in your product & your company."

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

Very good article.
Thank you.

I've loaded your blog in 3 totally different internet browsers and I must say this website loads a lot quicker then most. Would you mind e-mailing me the company name of your web hosting company? My personal email is: kitdupont@gmail.com. I'll even sign up through your own affiliate link if you'd like. Kudos

トリーバーチといえば、靴。その中でも、ペタンコなシューズをファッションの一つとして定着させた業績はすごいの一言です。

If some one wishes to be updated with most recent technologies after that he must be pay a quick visit this web site and be up to date everyday.

There is a greater tendency to over compensate when
you are looking to compensate for a perceived fault in any given relationship.
He is going to come to realize you are doing well even without him.
If your ex boyfriend perceives in your voice that you're thriving
as well as even better after the relationship ended,
it will feel like a fast kick to their personality.

whoah this blog is fantastic i like studying your posts.
Stay up the great work! You understand, many persons are hunting round for this information,
you could help them greatly.

It's an remarkable post in support of all the web visitors; they will get advantage from it I am sure.

Hi I am so happy I found your website, I really found you by accident, while I
was looking on Yahoo for something else, Anyways I am here now and
would just like to say many thanks for a incredible post and a all
round entertaining blog (I also love the theme/design), I don't
have time to go through it all at the moment but I have saved it and also added in your RSS feeds, so when I have time
I will be back to read much more, Please do keep up the awesome
work.

Lorsque clash of clans hack toi-même achetez un compétition vers votre micro , assurez-vous-même duquel vous-même avez ces spécifications par l'exécuter .
En particulier, si le agissements élevant inusité , et votre automate levant vieux, toi
risquez d'être déçu si toi vérifiez d'proximité
. Regardez la allure

Amazing! This blog looks just like my old one!

It's on a entirely different subject but it has pretty much the same page layout and design.
Superb choice of colors!

Hi there, I llog on to your new stuff regularly.
Your writing style is witty, kedep up the good work!

Hola , ​​creo que vi que visitó mi Página web así
que llegué a " devolver el favor " Soy . intentar encontrar
cosas para mejorar mejorar mi ! sitio web Supongo que su autorización para
utilizar algunas de algunas de tus ideas! !

The building has been able to achieve a 75% waste diversion (from landfill) utilizing
on-site recycling and composting. One can receive an luxury in various
non cultural hunting. Alfred Hitchcock Hour - S02, E04 'You'll Be the Death of Me' - A woman (Pilar Seurat) becomes the target of her murderous
spouse after finding a victim's button.

Thank you a lot for sharing this with all people you actually recognize what you're talking
approximately! Bookmarked. Kindly also visit my site =).
We will have a hyperlink alternate contract among us

If some one wants to be updated with most recent technologies then he must be pay a quick visit this website and
be up to date everyday.

I'm not sure where you're getting your information, but good topic.
I needs to spend some time learning much more or understanding more.

Thanks for magnificent information I was
looking for this info for my mission.

Thank you for another great post. The place else could
anybody get that kind of information in such an ideal means of writing?
I've a presentation subsequent week, and I am on the look for such information.

Heya i am for the first time here. I found this board and I to find It truly useful &
it helped me out a lot. I am hoping to present something again and
aid others like you helped me.

What's up, I log on to your blogs daily. Your humoristic style is awesome, keep doing what you're doing!

Hello to all, the contents existing at this website are truly amazing
for people knowledge, well, keep up the good work fellows.

Hello there! Do you know if they make any plugins to safeguard against hackers?

I'm kinda paranoid about losing everything I've worked hard on.
Any recommendations?

Nintendo had positioned itself as a group gaming system with many of its
games encouraging multiplayer cooperation and interaction.
Reaction has been swift to yesterday's report that EA was giving up on its quest to acquire Grand Theft Auto publisher Take-Two Interactive.
However, competition is keen so the amount is usually very reasonable.

It's a shame you don't have a donate button!
I'd without a doubt donate to this outstanding blog!
I suppose for now i'll settle for book-marking and adding your RSS feed to my
Google account. I look forward to brand new updates and
will talk about this blog with my Facebook group. Talk soon!

Hello, I wish for to subscribe for this blog to obtain latest
updates, therefore where can i do it please assist.

Hey there would you mind stating which blog platform
you're using? I'm planning to start my own blog in
the near future but I'm having a tough time choosing
between BlogEngine/Wordpress/B2evolution and Drupal.
The reason I ask is because your design and style seems different then most blogs
and I'm looking for something unique. P.S Apologies
for being off-topic but I had to ask!

Your choice: for you get to have your photo taken laying next to me in
bed, and for you can have your photo taken 'spooning' or with me
lying my head on your chest. The experts also explained
that snoring is generally associated with the upper airway resistance syndrome.

There is no additional requirement of separate NOx capturing devices which reduces capital investment.

Sweet blog! I found it while surfing around on Yahoo News.
Do you have any tips on how to get listed in Yahoo News?
I've been trying for a while but I never seem to get there!
Appreciate it

Matthew Najar, baby around Adelaide, Questions, put any movies employment option absolutely four years backwards considering the discharge of their particular to start with small 'I For example
the Technique Your family Are', came up with upon i-tunes foreign.

The only received average recommendations but received this popular
artist good care because of subway rock music social networks, propping ascending this about the
popularity enough where large performance title IslandJaMusic Group
of people authorized her or him as a result of close by.

After that he's freed several much more men and women and
one diversity of records on-line video coupled with dancing segments income the pup a quality reputation as being a fulfilled dancer, merely 2 decades old enough.
In spite of doing this new-found recognition and attention, Matthew were based on songs.

Not again.

Hello, I check your blog regularly. Your story-telling style is awesome,
keep up the good work!

It's an remarkable paragraph in favor of all the web users; they will obtain advantage from it I am sure.

Its like you read my mind! You appear to know so much abnout this, like you wrote the book in
it or something. I think thaat you could
do with a few pics to drive the message home a little bit, but other than that, this is fantastic blog.
A great read. I'll definitely be back.

You really make it seem really easy together with your presentation however I find this matter
to be really one thing which I think I'd by no means understand.

It kind of feels too complex and very extensive for me.
I am having a look ahead on your subsequent put up, I'll attempt to get the hold of it!

Hi i am kavin, its my first occasion to commenting anywhere, when i read this article
i thought i could also create comment due to this sensible piece of
writing.

This is really interesting, You are a very skilled blogger.

I have joined your rss feed and look forward to seeking more of
your wonderful post. Also, I have shared your website in my social networks!

I'm extremely impressed with your writing skills and also with the layout on your weblog.
Is this a paid theme or did you modify it yourself? Either way keep up the nice
quality writing, it's rare to see a great blog
like this one today.

I got this web page from my friend who shared with me on the topic of this web page and now this time I am visiting
this web site and reading very informative articles or reviews at this time.

My brother suggested I might like this website. He was totally right.
This submit actually made my day. You cann't
imagine simply how so much time I had spent for this info!

Thanks!

Thiis article is genuinely a good one it assists new the
web viewers, who are wishjing for blogging.

I am regular reader, how are you everybody? This paragraph posted at
this web site is genuinely good.

Take a image of the mother's legs together with father's legs next to baby footwear.
Are you quite hunting for reputed Facebook developers towards help in
you create to deploy fan pages additionally assorted different applications for their Twitter business
profile. Then there are architects, engineers, photographers,
sales professionals, copywriters, developers, then any
other profession you might think concerning, almost all of whom
have always been providing his or her skills and expertise on a freelance basis.

It's appropriate time to make a few plans for the longer term and it is time to be happy.

I've learn this submit and if I may I wish to counsel you few attention-grabbing issues or tips.
Maybe you could write subsequent articles referring to this article.

I desire to learn even more things approximately it!

If your inside sales staff is making the cold calls, they may
decide to close the deal after that, transfer the call to a closing agent,
or arrange for a time for a meeting. "Website building and website optimization are on two separate pages. Then comes the most important branch of any website, which is the content, content alteration is also a very significant part of a SEO task.

What's up, after reading this awesome piece of writing
i am too glad to share my experience here with colleagues.

It's really a nice and useful piece of info. I'm happy that you just shared this helpful information with us.

Please stay us up to date like this. Thank you for sharing.

When someone writes an paragraph he/she keeps the idea of a user in his/her brain that how
a user can be aware of it. Therefore that's why this paragraph is great.
Thanks!

This article offers clear idea designed for the new users of blogging, that actually how to do running a blog.

We sincerely welcome you to our website and we offer
our thankfulness to you for visit our site.As You know i'm ebay seller gama-sand.You can
purchase your medicines by my website for cheaper than ebay.Our All
Buyers are safe with 45 days paypal buyer protection.We giving you best
service as we can.if you need medicines not listed in site, contact me using website and i will respond you,We kindly invite
you to buying medicines from us and do like to our fan page.Thank you

If some one desires to be updated with most up-to-date
technologies therefore he must be visit this site and be
up to date all the time.

I think everything typed made a bunch of sense. But, what about this? suppose you wrote a catchier title? I mean, I don't wish to tell you how to run your blog, however suppose you added something to possibly grab folk's attention? I mean Using MS-SQL's NOLOCK for faster queries (Tom Mollerus' Weblog) is kinda plain. You could look at Yahoo's front page and note how they create article headlines to get viewers to open the links. You might add a related video or a picture or two to grab readers excited about everything've got to say. Just my opinion, it would bring your website a little livelier.

There are new studies that say otherwise on coffee consumption.
" British health care is delivered through the National Health Service (NHS), which like Canada is publicly funded through tax contributions and national insurance contributions from employees, employers, individuals, and the government. This also leads to the better absorption of nutrients and expulsion of unhealthy fats.

Hi! I could have sworn I've visited this site before
but after browsing through a few of the articles
I realized it's new to me. Regardless, I'm definitely pleased
I found it and I'll be bookmarking it and checking back
regularly!

I wanted to thank you for this fantastic read!!
I absolutely enjoyed every bit of it. I've got you saved
as a favorite to look at new things you post…

This blog was... how do you say it? Relevant!! Finally I
have found something that helped me. Thank you!

First off I would like to say wonderful blog! I
had a quick question in which I'd like to ask if you
do not mind. I was curious to know how you center yourself and clear
your mind before writing. I have had difficulty clearing
my thoughts in getting my ideas out there. I truly do
take pleasure in writing but it just seems like the first 10 to 15 minutes
are generally wasted just trying to figure out how to begin.
Any suggestions or tips? Many thanks!

That made dealing with unavoidable post production
problems that always come up easier to work through.
If you're happy with the clip and want to include it, drag
it to the storyboard. 'Spolier Alert' Out of
all these five robberies, this is the only one where the awesomely clever part is revealed at the very end.

Have you ever considered about adding a little bit more than just your articles?
I mean, what you say is fundamental and everything.
However think about if you added some great pictures or videos to give your posts more, "pop"!
Your content is excellent but with images and video clips, this blog could definitely be one of
the most beneficial in its field. Superb blog!

If you want to improve your knowledge simply keep visiting this website and be updated with the most recent gossip posted here.

Hello there! I could have sworn I've been to this site before but after checking through some of the post I realized it's new to me.
Nonetheless, I'm definitely delighted I found it and I'll be book-marking and checking back often!

The best Model is
Masha E.

It's an amazing piece of writing in support of all the web visitors; they will obtain advantage from it
I am sure.

When someone writes an article he/she retains the image of a user
in his/her mind that how a user can know it. So that's why this
post is great. Thanks!

Today, I went to the beach front with my children. I found a sea shell
and gave it to my 4 year old daughter and said "You can hear the ocean if you put this to your ear." She put the shell to her ear
and screamed. There was a hermit crab inside and it pinched her ear.

She never wants to go back! LoL I know this is totally off topic but
I had to tell someone!

Uneven Skin Tone: One of the most common type of skin aging, uneven skin tones are commonly the cause of the
decrease of the production of the natural pigment of the
skin and the melanin through the passage of time.
Age Defy provides 100 percent gray coverage and beautiful color to resistant grays, for softer, smoother and more vibrant hair than
before. This is one of the things that will help you in looking
younger.

Hi there colleagues, its great paragraph on the
topic of teachingand entirely explained, keep it up all the time.

Useful info. Lucky me I found your site unintentionally, and I'm stunned
why this coincidence didn't happened in advance! I bookmarked it.

I really love your website.. Excellent colors & theme.
Did you make this website yourself? Please reply back as I'm looking to
create my very own website and would like to learn where you got
this from or what the theme is named. Kudos!

Hi, i think that i noticed you visited my website so i came to go
back the want?.I'm attempting to in finding things to enhance my web site!I guess its good enough to use a few of
your ideas!!

It's going to be ending of mine day, however before ending I am reading
this wonderful post to increase my experience.

Hi there, its pleasant piece of writing concerning media print, we all understand media is a fantastic source of data.

This is my first time visit ɑt here and i am really impressed to read everthing at ɑlone рlace.

Howdy! Do you know if they make any plugins to safeguard against hackers?
I'm kinda paranoid about losing everything I've worked hard
on. Any tips?

It's awesome to visit this web site and reading the views of all
friends regarding this article, while I am also zealous of getting knowledge.

Excellent post. I was checking constantly this blog and I'm impressed!
Extremely useful info specially the remaining phase :
) I maintain such info much. I used to be looking for this
certain info for a long time. Thank you and best of luck.

I'm very pleased to find this web site. I wanted to thank you for ones time just for this fantastic read!!
I definitely enjoyed every little bit of it and i also have you saved as a favorite to check out
new stuff on your web site.

Accept this review and we'll help save a fictional creature!

Asking questions are actually pleasant thing if you are
not understanding anything completely, except this post
gives pleasant understanding even.

Hey! I just wanted to ask if you ever have any trouble
with hackers? My last blog (wordpress) was hacked and I ended
up losing many months of hard work due to no backup.
Do you have any methods to stop hackers?

Excellent post but I was wondering if you could write a litte more on this subject?
I'd be very thankful if you could elaborate a little bit more.
Many thanks!

There is definately a lot to know about this topic.
I really like all the points you have made.

Everyone lovss it when individuals come together and share ideas.
Great website, stick with it!

Cons of outsourcing - Of course, business outsourcing, internet marketing is not all about
great positivities. By integrating the latest technologies, unique processes and experts specialized
in data entry, these companies offer online data entry with accuracy,
efficiency and effectiveness. It is enabled by easily accessible communication techniques.

I'm not ѕure wɦere yoս'гe getting your info, butt ցood topic.
I needs to spend ѕome time learning mߋre oor understanding
more. Τhanks fοr magnificent injfo Ι waѕ lookiոg for thiѕ
info fߋr mʏ mission.

Hi there! I could have sworn I've been to this site
before but after browsing through some of the post I realized it's new to me.
Anyhow, I'm definitely glad I found it and I'll be book-marking and checking back often!

I every time spent my half an hour to read this blog's articles or
reviews all the time along with a mug of coffee.

Great post. I was checking continuously this blog and I am
impressed! Extremely helpful info specifically the last part :) I care for
such info a lot. I was looking for this certain info for a very long time.
Thank you and good luck.

First off I want to say wonderful blog! I had a quick
question in which I'd like to ask if you don't mind. I was interested to find out
how you center yourself and clear your head prior to writing.
I have had a difficult time clearing my thoughts in getting my ideas
out there. I do enjoy writing but it just seems like the first 10 to
15 minutes are wasted simply just trying to figure out how to begin.
Any recommendations or hints? Many thanks!

Hello there! This is my first visit to your blog! We are a team of volunteers and starting a new project in a
community in the same niche. Your blog provided us useful information
to work on. You have done a outstanding job!

Hey there! I know this is kind of off topic but
I was wondering which blog platform are you using
for this website? I'm getting tired of Wordpress because I've had issues with
hackers and I'm looking at options for another platform.
I would be awesome if you could point me in
the direction of a good platform.

Hi there would you mind sharing which blog platgform you're
working with? I'm looking to start my own blog soon but I'm having a difficult time choosing bbetween
BlogEngine/Wordpress/B2evolution andd Drupal. The reason I ask is because your design seems different then
most blogs and I'mlooking for something unique.
P.S Apologies for getting off-topic but I had to ask!

You really make it seem so easy together with your presentation but I to find this matter to be really something that
I feel I might by no means understand. It kind of feels too complex and very wide for me.
I am having a look ahead in your subsequent publish, I will
attempt to get the grasp of it!

Great web site. Lots of helpful info here. I am sending it to some friends ans also
sharing in delicious. And obviously, thank you in your effort!

Choose whether any of your constraints are holding you back
or whether you could employ the aid of others to pack in the spaces.
As with any online marketing activity, keep the message
short, sweet and direct and be sure to include the necessary contact information needed to send in a referral.
Learn how to market your home based sewing business online and in your
local community.

I enjoy what you guys tend to be up too. This sort of clever work and reporting!
Keep up the wonderful works guys I've incorporated you guys to my
personal blogroll.

Keep be few optimize scam can SEO presence our optimization" a the positive. However, Nuvyyo hopes to undercut the pricing of DVR and streaming services to rely solely on the cloud. Avoid feeling discouraged if you are faced with home selling on a small budget.

In fact no matter if someone doesn't understand then its up to other people that they will help,
so here it takes place.

It's a pity you don't have a donate button!

I'd most certainly donate to this brilliant blog! I suppose for now i'll settle for book-marking
and adding your RSS feed to my Google account.

I look forward to brand new updates and will talk about this blog with my Facebook group.
Talk soon!

There are two things you can do if you get hurt in an accident.
Another accident is just waiting to happen if you just brush it off and take an apology
off your boss. This is the reason you may want to consider appointing a work injury lawyer.

What's up to every one, as I am actually eager of
reading this weblog's post to be updated regularly.

It contains nice material.

Hi just wanted to give you a brief heads up and let you know a few
of the images aren't loading properly. I'm not sure
why but I think its a linking issue. I've tried it
in two different web browsers and both show the same outcome.

Heya i'm for the first time here. I came across this board and I in finding It truly helpful & it
helped me out a lot. I am hoping to give something back and aid others like you aided me.

Post a comment


Type the characters you see in the picture above.