m****e
发帖数: 1197 | 1 在有些SQL SERVER上NOLOCK是蓝色的，就像SELECT等等这种RESERVED WORD。有些
SERVER上TYPE OUT以后不会变成蓝色的。请问这是为什么？不会变蓝的SERVER上，
NOLOCK是不是不起作用的就算我加了？ |
|
m****e
发帖数: 1197 | 2 yes, it was set to default, blue.
i even tried customizing it to red, it applies to other keywords like SELECT
, etc, but nothing happened to NOLOCK.
it looks to me NOLOCK is not set as "reserved keywords" on this server. is
it possible? this setting comes from Mircorsoft or something can be altered
by DBA? |
|
m****e
发帖数: 1197 | 3 it's all default. and i reset it again.
my co-work's client is blue on NOLOCK, why mine is different? any clue?
execution |
|
z***y
发帖数: 7151 | 4 这个不奇怪啊。
你可以看一看那个procedure, 你看里面有几个对temp table 的改变， 这些数据的改
变都会触发
recompilation。
我这个版本是2008 SP2的。
create procedure sys.sp_replmonitorrefreshagentdata
as
begin
set nocount on
declare @retcode int
,@agent_id int
,@agent_id2 int
,@publisher_id int
,@xact_seqno varbinary(16)
,@logreader_latency int
,@publisher_db sysname
,@publication sysname
-- security check
-- Has to b... 阅读全帖 |
|
|
S*****0
发帖数: 538 | 6 Some suggestions:
1. Use set-based. NOT EXITS is more like row-based, and it kills when the
data volumn is high.
2. Create indexes on the join columns.
3. The approperiate locking hint helps.
4. Try to optimize the execution plan.
SELECT
A.*
FROM
DB.F4311 A (NOLOCK)
LEFT JOIN TESTDTA.F43121 B (NOLOCK)
ON ( (A.PDDOCO = B.PDDOCO)
AND (A. PDDCTO = B. PDDCTO)
AND (A.PDKCOO = B. PRKCOO)
)
LEFT JOIN TESTDTA.f4311 C (NOLOCK)
ON ( (A.pddoco = C.pddoco)
AND (A.pddc |
|
x***e
发帖数: 2449 | 7 try this with self join.
group by may not necessary be the best idea:
SELECT cp.title
, cp.name
, cp.age
FROM tbl_test AS cp WITH (NOLOCK)
WHERE (
SELECT count(*)
FROM tbl_test AS cr WITH (NOLOCK)
WHERE cr.title = cp.title
AND cr.age > cp.age
) = 0 |
|
v*****r
发帖数: 1119 | 8 There you go. Spark just show one typical mindset conflict between Oracle
developers and SQL Server developers.
SQL Server developers: We think 管理层的人不懂,听locking变色. locking is
necessary and we can't live without it. Anyone who try to use "nolock hint"
to improve performance will have to worry about Dirty Read (as "nolock hint"
just changed the Isolation Level to Read Uncomitted)
(Note: that mindset is actually true before SQL Server 2005 Yukon release.)
Oracle developers: Oracle by design achiev |
|
j*****n
发帖数: 1781 | 9 alright, it was close to the solution I thought. Well, let's go this way...
1. a log table with Identity column as the primary key and it is the
clustered index.
2. this log table was inserted via trigger from the table is monitored.
3. a SSIS or BCP job that runs periodically, say, once a minute.
3.1 step 1: select max ID from logTable, if thisID > lastSavedID, then
3.2 step 2: select * from logTable with (NOLOCK) where ID between
lastSavedID + 1 and thisID
3.3 lastSaveID = thisID for nex... 阅读全帖 |
|
s**********o
发帖数: 14359 | 10 我的SQL SERVER一般是这样的
select
t1.col1,
t2.col1,...
from
table1 t1 with (nolock)
left outer join
table2 t2 with (nolock) on
(
t1.col = t2.col
) |
|
i****a
发帖数: 36252 | 11 写 Stored Procedure 批量处理数据
1.数数有多少记录需要被处理
select @n = count(*) from tableA where condition = xyz
2.用个functionGetNextRecord, 每次返回一个 ID
select top 1 ID from tableA where condition = xyz order by ID
3.写 WHILE loop
SET @count = 1
SET @NextRecord = functionGetNextRecord()
WHILE (@NextRecord IS NOT NULL) and (@count<=@n) BEGIN
blah blah blah
END
如果我写:
select ID
INTO #temptable
from tableA where condition = xyz
然后用#temptable做工就可以, 一次读表可以做到的硬是作成成千千万万次的读
另外不知道是不是老印的创作. 查 account balance, 没有一个 current bala... 阅读全帖 |
|
i****a
发帖数: 36252 | 12 写 Stored Procedure 批量处理数据
1.数数有多少记录需要被处理
select @n = count(*) from tableA where condition = xyz
2.用个functionGetNextRecord, 每次返回一个 ID
select top 1 ID from tableA where condition = xyz order by ID
3.写 WHILE loop
SET @count = 1
SET @NextRecord = functionGetNextRecord()
WHILE (@NextRecord IS NOT NULL) and (@count<=@n) BEGIN
blah blah blah
END
如果我写:
select ID
INTO #temptable
from tableA where condition = xyz
然后用#temptable做工就可以, 一次读表可以做到的硬是作成成千千万万次的读
另外不知道是不是老印的创作. 查 account balance, 没有一个 current bala... 阅读全帖 |
|
j*******e
发帖数: 356 | 13 土姐我好久没摸vba了，不知道行不行呢，你看sql语句加 nolock 有没有效果 |
|
b*****n
发帖数: 3774 | 14 多谢大肚，咋个加nolock，我没有用过这个命令。我其他tool也有sql就不慢，只有这
个慢，奇怪得很。 |
|
j*****n
发帖数: 1781 | 15 alright, lets assume that you have enough space... more than 30G for data
and also enough space for log file (simple recovery model still needs a lot
log space while operation).
1. create a new table with same definitions plus your new integer column
with default = 0
2. execute
INSERT INTO newTable (...)
SELECT ...
FROM oldTable WITH (NOLOCK)
3. add all associated indexes same as the old one
NOTE: clustered index can not be the same name.
4. rename the old table
5. rename the new table |
|
j*****n
发帖数: 1781 | 16 it is a data warehouse, usually you don't have to worry about it.
see my NOLOCK hint? I didn't consider the dirty read as well.
the |
|
j*****n
发帖数: 1781 | 17 use EXISTS and WITH (NOLOCK) hint for your checking step would be a good
practice.
locks |
|
j*****n
发帖数: 1781 | 18 have WITH (NOLOCK) hint
otherwise it pretty depends on your indexes on your join keys for such
simply query. |
|
i****a
发帖数: 36252 | 19 from the same workstation client or are you using the client on different
machines? |
|
m****e
发帖数: 1197 | 20 i am using a clinet different from the server.
is this a setting issue? how can I reset it? |
|
i****a
发帖数: 36252 | 21 try to go Tools -> Options
Environment -> Fonts and Colors and click "Use defaults"
but in any case, the color on your sql query has no effect on the execution |
|
i****a
发帖数: 36252 | 22 are you on SQL management studio 2008?
in fonts and colors again, select "Keyword" in the "Display items:" box.
does it have blue color as foreground color? |
|
i****a
发帖数: 36252 | 23 again, color and font is on the client only, server has nothing to do with
it.
SELECT
is
altered |
|
g***l
发帖数: 18555 | 24 我公司正好相反，大胆使用新技术，什么新用什么，准备用PEER TO PEER REPLICATION
，好多东西我都没用过，比如RANK()，CTE，WITH （NOLOCK）老技术就是不行，一天不
学习就过时了，我准备赶紧认证，夏天去纽约TRAINING |
|
g***l
发帖数: 18555 | 25 其实我发现了CODE的写得好不好，不能只看出不出结果
*第一个是思路要清楚，谁JOIN谁，KEY是什么，什么JOIN，
*我的目标是什么，进来的是什么参数，出去的是什么
*复杂的QUERY,先把FLOW CHART画好，
大家讨论一下，看看有没有什么更好办法， SUBQUERY放到CTE或者TEMP TABLE里，
*不挂不需要的数据和COLUMN
*避免用*，避免用CURSOR，避免长时间的，或者繁琐的UPDATE DELETE (JOIN)
*READ ONLY一律用WITH (NOLOCK)
*容错性，进来的错参数会不会出烂数据,表中有错数据，会不会ERROR OUT
*最后看扩展性，再加个参数要花多长时间改。 |
|
g***l
发帖数: 18555 | 26 随便总结了一下，不包括ETL,抛砖引玉了，
1. never use * in query, only use specific columns and rows you need, never
use extra
2. table uses dbo prefix, selection uses with (nolock)
3. repeated code section can be replace by UDF or stored procedure
4. join is always on the key. if join is not the key, you need to be very
careful
5. layout flow chart for complicated stored procedure before coding
6. CTE is a good choice to replace subquery, table variable and temp table
7. all the work should be done on the temp t... 阅读全帖 |
|
y****w
发帖数: 3747 | 27 temp笨重不灵活，但是，可以加索引。如果后继操作时间长，还可以减少lock-wait(even nolock/ur)和死锁的机会。 |
|
b**a
发帖数: 1118 | 28 I am using sth like this now:
DECLARE @count2 int
SET @count2 = 10000
WHILE @count2 >= 10000
BEGIN
DELETE temp from ( SELECT TOP 10000 * FROM Transactions WITH(NOLOCK)
WHERE TransStartTime >= @FromDate and TransStartTime < @EndDate)
temp OPTION (MAXDOP 1)
SELECT @count2 = @@ROWCOUNT
END
and i really need it to run faster... |
|
y****w
发帖数: 3747 | 29 10000的批次太小，换成10w或100w重新跑。问下你们的dba，保证log空间足够。
WITH(NOLOCK
)
@EndDate) |
|
b**a
发帖数: 1118 | 30 I am using sth like this now:
DECLARE @count2 int
SET @count2 = 10000
WHILE @count2 >= 10000
BEGIN
DELETE temp from ( SELECT TOP 10000 * FROM Transactions WITH(NOLOCK)
WHERE TransStartTime >= @FromDate and TransStartTime < @EndDate)
temp OPTION (MAXDOP 1)
SELECT @count2 = @@ROWCOUNT
END
and i really need it to run faster... |
|
y****w
发帖数: 3747 | 31 10000的批次太小，换成10w或100w重新跑。问下你们的dba，保证log空间足够。
WITH(NOLOCK
)
@EndDate) |
|
g***l
发帖数: 18555 | 32 一般INSERT时间太长就容易DEADLOCK，一INSERT，就开始TABLE BLOCKING，如果
TRANSACTION太长，另外的INSERT就要等很久.注意光SELECT的话就WITH (NOLOCK)
DIRTY READ好了 |
|
y*****n
发帖数: 11251 | 33 insert加了row lock了也不行。是不是insert都是lock整个table啊？
select with nolock也有deadlock。 |
|
B*********L
发帖数: 700 | 34 总算可以了：
DECLARE @tableHTML NVARCHAR(MAX)
SET @tableHTML =
N'
SET NOCOUNT ON;
PRINT ''name NAME_html
''
SELECT [name]
,''http://xxxx.com/Report.aspx?ID='' + [name] + ''>'' + [name] + ''''
,''
''
FROM table WITH (NOLOCK)
' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'RF_Performance'
, @recipients = 'l*****[email protected]'
, @subject = 'test'
, @importance = 'High'
, @query = @tableHTML
, @exclude_query_output = 1
, @query_no_truncate = 0
,... 阅读全帖 |
|
s**********o
发帖数: 14359 | 35 SQL SERVER
如果就是SELECT，那就INNER JOIN WITN NOLOCK
如果是TRANSACTION，要先用IF EXIST，然后UPDATE/INSERT/DELETE
如果是复杂的JOIN + TRANSACTION TOUCH数据很多，一般是先弄个CTE把
要更新的KEY找出来，然后UPDATE/INSERT/DELETE |
|
s**********o
发帖数: 14359 | 36 显然你做DBA不久吧，SQL SERVER 所有的 SELECT QUERY包括STORED PROCEDURE都要用
WITH NOLOCK，否则长时间的READ也可以BLOCK其它的UPDATE INSERT，UPSERT一旦被
BLOCK住，LOCK就会ESCLATE的。不是有PROFILER吗，还有其它的MONITOR TOOLS比如
QUEST的spotlight或者idera sql diagnostic manager |
|
s**********o
发帖数: 14359 | 37 CTE recursive query, trigger这些对PERFORMANCE影响很坏的，
有时候会产生你意想不到的结果，为什么老要更新呢？能不能
搞成SCHEDULED JOBS呢，如果必须不断更新，考虑所有应用程序和
QUERY的SELECTION都用WITH (NOLOCK) |
|
|
v****e
发帖数: 145 | 39 方案一：对每一个客户ip 限制每半小时查询次数 超出次数就倒计时。对这样一个系统
，我觉得可以分为用户网页和合作商api. 和合作商的票是有保障的，可以在合作商本
地做计数，然后剩下来的座位给12306网站本身的用户买票。
方案二：另一种办法是做信用卡预授权，先授权然后告诉用户是不是买成功了。这样的
好处在于可以减少用户去纠结最后是剩下一座位还是两个座位。只剩少量余票的情况下
，交易成功本来概率就是低的，客户会有失败的预期。
现在的12306技术上的问题在于用户只要开始订，这个位子就被占上了，而不是等到买
完再减数。如果交易失败，才会把数字加回去。这样读写竞争很大。即使这样，数字变
动太频繁，也无法保证客户看到的数字是正确的，只能给出一个大概的数字。
查询上需要知道火车全程中的某一段有多少空位，用sql nolock爬爬看要多久，我没试
过但估计性能不乐观。只能用并行脏读的方式加速查询过程并缓存结果。
[在 totalctrl (沾衣十八跌+降龙十八涨) 的大作中提到：]
：google一下 才知道是铁道部订票的
：
：........... |
|
c******n
发帖数: 4965 | 40 【 以下文字转载自 Linux 讨论区 】
【 原文由 creation 所发表 】
br />
铱薔FS server & client on two machines at home,
connected through 100Mb ethernet,
both NFS v3,
但奇慢无比, 最快也就2Mbit/sec, 哪里有问题呢?
I tried rsize,wsize=1500~~~~ 65535, soft,hard, intr,nolock,
rmem_max ...
mtu iis 1500, can't be bigger,
thanks |
|
