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

SQL Server: Finding Dependent Objects

To find objects dependent on a given table, you can use the following:

execute sp_depends tablename

In the database I work with, the table I used brings up many views and a few stored procedures.

I ran into this on kudvenkat’s youtube video, “Dynamic SQL vs Stored Procedure.”

He was using it to see what objects were dependent on the table before deleting it.

I work with a database whose tables I don’t want to delete, but where it may be at times useful to know what views are dependent on a table just in order to know that a certain view exists in the first place. Thankfully with RedGate’s SQL Search app (which is a free tool, and which I use daily), I can see how a view is constructed, including which tables are used.