question about NOLOCK - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

question about NOLOCK

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-30 : 10:25:29

Hi,

I have never used NOLOCK, or ROWLOCK, or anything of that sort. It's something I thought was over my head when I first encountered it and as I am gaining a better grip on SQL I have decided I want to try and tackle it. Also, because I think its causing me some problems :)

I recently implemented a new query into my application.

The query below gets the COUNT(*) of certain tables. Some of these tables are tall (5 million plus rows)

The counts returned are not absolutely critical information, and we are not joining or doing any other transactions based on this data. I believe running these counts on these pretty active tables (selects,inserts) are possibly causing slowdowns ?

Would using NOLOCK be an appropriate situation for something like this ? I am looking for a performance increase hopefully, and hoping there isnt much of a downside? I'm also not sure what type of reliability to expect from NOLOCK.

Any help much appreciated !!

Thanks once again

mike123

create PROCEDURE [dbo].[select_UserStats_Admin_TEST]
(
@userID int
)
AS SET NOCOUNT ON

DECLARE @Mail_Sent INT, @Mail_Received INT, @Comments_Sent INT, @Comments_Received INT,

@friendsListCount INT

SELECT @Mail_Sent = count(*) FROM tblMessage WHERE messageFrom = @userID
SELECT @Mail_Received = count(*) FROM tblMessage WHERE messageTo = @userID

SELECT @Comments_Sent = count(*) FROM tblComment WHERE CommentFromID = @userID
SELECT @Comments_Received = count(*) FROM tblComment WHERE CommentTo = @userID

SELECT @friendsListCount = count(*) FROM tblFriends WHERE userID = @userID

SELECT @Mail_Sent as Mail_Sent, @Mail_Received as Mail_Received, @Comments_Sent as Comments_Sent,
@Comments_Received as Comments_Received, @friendsListCount as friendsListCount

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-30 : 10:43:47

[code]SELECT @Mail_Sent = SUM(CASE WHEN messageFrom = @userID THEN 1 ELSE 0 END)
@Mail_Received = SUM(CASE WHEN messageTo = @userID THEN 1 ELSE 0)
FROM tblMessage
WHERE @userID IN (messageFrom, messageTo)

SELECT @Comments_Sent = SUM(CASE WHEN CommentFromID = @userID THEN 1 ELSE 0 END)
@Comments_Received = SUM(CASE WHEN CommentTo = @userID THEN 1 ELSE 0 END)
FROM tblComment
WHERE @userID IN (CommentFromID, CommentTo)

SELECT @friendsListCount = count(*)
FROM tblFriends
WHERE userID = @userID[/code]

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-30 : 11:55:37

Hi Peso,

Wow this is much better, and should cut the queries in half almost, without even touching on locking :)

I am currently getting this error tho

Incorrect syntax near '@Mail_Received'

I can't see anything wrong with the syntax tho?

Thanks very much

bfoster
Starting Member

30 Posts

Posted - 2008-03-30 : 15:06:19

To correct the error you are getting from the previous suggestion, place a comma at the end of the first line.

If you want to use nolock which sounds like it could be appropriate for this task, the syntax would be like this:
SELECT @Mail_Sent = count(*) FROM tblMessage WITH (NOLOCK) WHERE messageFrom = @userID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-30 : 18:57:31

quote:
Originally posted by mike123

I am currently getting this error tho

Incorrect syntax near '@Mail_Received'

I can't see anything wrong with the syntax tho?

There is an END missing last in the sentence, just before closing paranthesis and after the 0.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-30 : 18:58:33

[code]SELECT @Mail_Sent = SUM(CASE WHEN messageFrom = @userID THEN 1 ELSE 0 END),
@Mail_Received = SUM(CASE WHEN messageTo = @userID THEN 1 ELSE 0 END)
FROM tblMessage
WHERE @userID IN (messageFrom, messageTo)

SELECT @Comments_Sent = SUM(CASE WHEN CommentFromID = @userID THEN 1 ELSE 0 END),
@Comments_Received = SUM(CASE WHEN CommentTo = @userID THEN 1 ELSE 0 END)
FROM tblComment
WHERE @userID IN (CommentFromID, CommentTo)

SELECT @friendsListCount = count(*)
FROM tblFriends
WHERE userID = @userID[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources