Please help me with the Performance and Locking Issues....

Question

Please help me with the Performance and Locking Issues....

Lucky9

Default port

Points: 1470
More actions
June 8, 2010 at 10:36 pm

#219757

Hi,
I have written 5 different separate stored procedures inn the database and in each stored procedure i have sql script with joins more then 10 tables...
Each table will have more then 800,000 records and added to this am using the dense rank and partition clauses in the sql script...
what is the best approach to avoid the locks and commit the transactions...
Urgent help would be very helpful
June 8, 2010 at 11:36 pm

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's not enough information here to say anything useful. You're going to have to give a lot more info if you want useful help. The queries and table structures would help.

Why is this urgent anyway?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

stewartc-708166 (6/8/2010)
> use WITH(NOLOCK) on each table in the query

No, no, no, no!

Why are you offering advice without telling people the consequences? Nolock means 'get the data quickly, I don't care if it's slightly wrong'. Nolock allows SQL to do quick and dirty data access methods that can easily result in duplicate or missed data. There are places that's fine, but since we haven't been told that occasionally incorrect data is acceptable here, it's dangerous to assume that it is acceptable.

See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Just to emphasise what Gail has stated, I've actually seen duplicated data appear thanks to WITH(NOLOCK). I recommend NOT using it most of the time. The proper way to solve the problem is to write better code all the way around on the system, to keep transactions very short, and to use "Divide'nConquer" methods in association with proper set based technology.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)