NOLOCK

I recently discovered and tried using the “NOLOCK” query hint and found in some cases it reduced my query times up to 50%, though at times it seemed to have no effect or minimal effect. I got the same number of results with each query as I had gotten in the past.

To use “NOLOCK” in a query, you put it after the table name you are querying.

Examples:

INNER JOIN Table1 (nolock)

FROM Table1 (nolock)

Basically it is a dirty read of the data – it overrides all locks currently being held and gives you a result set of what the data currently is in the database. This sounds equivalent to the “SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED” statement, except it seems to actually speed up the queries whereas I don’t see increased speed of queries with the “set transaction isolation level read uncommitted” statement.

This is new to me and I’m still experimenting with it, but if there are issues with records being locked by queries, putting (nolock) beside each joined table may leap over those locks and get the data back more quickly.

NOTE: I work in a database that is supposed to have static data outside of a nightly update. The NOLOCK query hint may not be a good choice if you are running reports inside a live system. The database I have access to is one in which I do not have the right to update, delete, or create tables, but still it appears there are locking issues based on how much the NOLOCK query hint sped up my queries.

Below is a sample query:

SELECT DISTINCT
        Meds.Med_ID as Med_ID,
        Meds.Desc as Med,
        Meds.Generic as Generic,
        Generic_Name.Desc AS Generic,
        Therapeutic.Desc AS Thera,
        Pharma.Desc AS Pharma,
        Meds.Strength As Med_Strength,
        Meds.Form as Med_Form,
        Meds.[Route] as Med_Route
INTO ##BPatchList
FROM Meds (nolock)
        LEFT JOIN Pharma (nolock)
        ON Meds.PHARM_ID = Pharma.PHARM_ID
        LEFT JOIN Generic_Name (nolock)
        ON Meds.GENERIC_ID = Generic_Name.GENERIC_ID
        LEFT JOIN Therapeutic (nolock)
        ON Meds.Thera_ID = Therapeutic.Thera_ID
        AND Meds.Med_id in (/*Buprenorphine patch*/ 1234, 3456, 2345, 5678, 4682, 1236, 5656, 6666, 1654, 5464)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s