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.
| Author |
Topic |
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
 Posted - 2012-07-18 : 14:18:59
|
| We have a TABLE that has at least 5 million records AND this STATEMENT is USED very often. We ARE planning TO NOT have ANY performance issues WHEN the DATABASE is live.is there ANYthing i should be aware OF?SET @UserID = (SELECT Users.UserID FROM Reviews.Users WITH (READUNCOMMITTED) WHERE Users.UserName = @UserName); |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-18 : 14:27:40
|
1. If you have duplicate UserName values the @UserID variable may not retrieve the same value consistently. (And unless you have a unique constraint/index on UserName, YOU WILL get duplicates)2. READUNCOMMITTED could very likely return...wait for it...uncommitted data that won't exist on subsequent calls. Don't use it.3. You're USE of capital LETTERS in YOUR post is not VERY CONSISTENT and may BE confusing.  |
 |
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2012-07-24 : 16:28:49
|
| I'm not sure why we use READUNCOMMITTED. But i remember once we stop using READUNCOMMITTED and we encounter multiple DEADLOCKS |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-24 : 16:52:51
|
quote:
As the name indicates, you can read UNCOMMITTED data. When you try to read rows, SQL Server will not place a shared lock and hence such queries will not block. This will help the queries to execute faster, compared to READ COMMITTED isolation level.
That's unfortunate wording, because it places too much stress on "faster vs. other method" and not enough on "potentially bad data vs. guaranteed correct data". Your bank can speed up an ATM withdrawal by not actually giving you the cash (while debiting your account anyway).If you have a brain tumor that needs surgery, do you want the surgeon to do it fast, or correctly?Speed is not always the most important thing. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
performance