谁懂 database tuning

乱码

原帖由 bffbffbff 于 2011-1-24 12:37 发表


我遇到用curosr的情况与表的大小倒没直接关系，经常是需要对loop里面的每一row数据进行比较复杂的运算或者逻辑

还有几次见到复杂的cursor甚至跨越了多个trigger 基本上当成是temp table用了

你可以用case when的结构在where中，logic来说是简单些，也显得比较高深 但看它的execution plan，scan table是一定的，不到万不得已，不推荐用。

(case when... then 1 else..then 2 end)=1 这种形式

bffbffbff

原帖由 乱码 于 2011-1-24 12:56 发表


你可以用case when的结构在where中，logic来说是简单些，也显得比较高深 但看它的execution plan，scan table是一定的，不到万不得已，不推荐用。

(case when... then 1 else..then 2 end)=1 这种形式

， 试过了，有时候还是搞不定

举个例子说吧，比如说loop through一个不大的表（可以是1-20条记录不等），然后根据每个row里面的几个field值来决定什么样的一行记录去insert到temp/table variable里，最终形成的temp table用于下一步运算。

我能想到过的我的问题是
1. 有时候当前记录如果不符合条件剩下的rows就不需要处理了直接break
2，有时候当前记录和前一条记录需要有比较或者需要前面记录里的值。
3. 有时候当前记录可能需要slip掉进入下一个记录

IsDonIsGood

但凡需要将business logic写进sp的时候，都会遇到不得不用cursor或者while的时候，
我的选择是将尽量少的数据写进temp table，然后再temp 表内 cursor或者loop。
写过最麻烦的一个要比较previous row ,current row ,next row的所有值来确定mapping rule，实在没办法，只能用loop来实现，5分钟一次的schedule，运行还好，基本上20s内完成。

不得不loop的时候个人经验是尽量减小loop的表容量，如果实在减少不了，应该考虑将businiess logic 写到application而不是db里去

IsDonIsGood

原帖由 乱码 于 2011-1-24 12:56 发表


你可以用case when的结构在where中，logic来说是简单些，也显得比较高深 但看它的execution plan，scan table是一定的，不到万不得已，不推荐用。

(case when... then 1 else..then 2 end)=1 这种形式

case 的cost应该也不低吧，尤其是在where 里，而且稍微复杂点的logic用case写不加comment的话很难读。

bffbffbff

原帖由 IsDonIsGood 于 2011-1-24 13:18 发表
但凡需要将business logic写进sp的时候，都会遇到不得不用cursor或者while的时候，
我的选择是将尽量少的数据写进temp table，然后再temp 表内 cursor或者loop。
写过最麻烦的一个要比较previous row ,current row ,next r ...

很同意, 现在我一旦遇到非用cursor的不可的时候，就尽量减少所选数据数量。
只是见网上很多人讨论cursor的alternatives，而且发现这些方案真的把大多数cursor给取代了， 所以在这里就想问问大伙能否把这个也解决了。

当然，cursor的东西确实不易做很频繁使用的scheduled job

[ 本帖最后由 bffbffbff 于 2011-1-24 13:24 编辑 ]

bffbffbff

原帖由 IsDonIsGood 于 2011-1-24 13:20 发表

case 的cost应该也不低吧，尤其是在where 里，而且稍微复杂点的logic用case写不加comment的话很难读。

难度是一方面，主要还是觉得case搞不定所有cursor能做的东西

IsDonIsGood

原帖由 bffbffbff 于 2011-1-24 13:25 发表


难度是一方面，主要还是觉得case搞不定所有cursor能做的东西

稍微复杂点的business logic case就不行了，个人感觉一旦 case嵌套了2层以上判断 performance就会有影响。case也就做做简单的判断还可以，cursor/while实现business logic的能力还是很强的，就是cost太高。不过个人意见是小数量data loop还素很快的，没什么performance issue。
不过我的经验是sql server的，不知道Oracle怎样，感觉上oracle 的cursor 功能更强大？performance呢？呼Oracle大侠们现身说法~~~
刚开始用case的时候经常忘记指定所有的条件，然后就返回一大串NULL，当时那个郁闷啊~~~
另：楼上能给个cursor的替代例子么？谢啦~~

乱码

原帖由 IsDonIsGood 于 2011-1-24 13:18 发表
但凡需要将business logic写进sp的时候，都会遇到不得不用cursor或者while的时候，
我的选择是将尽量少的数据写进temp table，然后再temp 表内 cursor或者loop。
写过最麻烦的一个要比较previous row ,current row ,next r ...

如果是牵扯到previous/next row,最好用cte sort一下,出rownumber，然后用两个/多个table instance，在where中用table1.rownumber=table2.rownumber+1,这种形式.

把需要计算的condition写到where中，一定会引起table scan,不光是case when, 所以design很重要，design阶段不能解决的，才考虑把business logic 放到where中，但performance wise，的确不推荐。

bffbffbff

原帖由 IsDonIsGood 于 2011-1-24 13:38 发表

稍微复杂点的business logic case就不行了，个人感觉一旦 case嵌套了2层以上判断 performance就会有影响。case也就做做简单的判断还可以，cursor/while实现business logic的能力还是很强的，就是cost太高。不过个人意见是 ...

简单点的string concatenation 比如说建立个dynamic query 来create，insert或者query table之类的 可以
select @sql = @sql + a.col from a
就是实现了，偶尔借助点case就可以把很多简单的cursor 搞定
稍微有点计算或者逻辑的可以用 UDF 写好。
还有就像乱码兄说的可以用cte+rownumber搞定，可惜我学了这招之后几次试图用但由于我遇到的情况不太一样所以用不上

IsDonIsGood

之前都没怎么用CTE，看来要好好学一学了。
有没有链接一观？hoho，懒得放狗了~~

bffbffbff

原帖由 IsDonIsGood 于 2011-1-24 14:36 发表
之前都没怎么用CTE，看来要好好学一学了。
有没有链接一观？hoho，懒得放狗了~~

CTE可是好东西啊，有问题你抓CTE专家乱码兄一顿狂问就可以了

http://msdn.microsoft.com/en-us/library/ms190766.aspx

老衲

对net developer来说，Per Tuning这个提法可能太大。看过一篇文章，觉得很不错，如果都能理解，对net developer来说应该足够了。

SQL Server DO's and DONT's
http://www.codeproject.com/KB/database/sqldodont.aspx

澳贼

原帖由 老衲 于 2011-1-24 14:45 发表
对net developer来说，Per Tuning这个提法可能太大。看过一篇文章，觉得很不错，如果都能理解，对net developer来说应该足够了。

SQL Server DO's and DONT's
http://www.codeproject.com/KB/database/sqldodont.aspx

谢谢长老。 您就的这片如何？
http://www.databasejournal.com/f ... rver-Developers.htm

老衲

原帖由 澳贼 于 2011-1-24 14:53 发表


谢谢长老。 您就的这片如何？
http://www.databasejournal.com/f ... rver-Developers.htm

过于专业。那是for SQL Server Developers

IsDonIsGood

原帖由 老衲 于 2011-1-24 14:45 发表
对net developer来说，Per Tuning这个提法可能太大。看过一篇文章，觉得很不错，如果都能理解，对net developer来说应该足够了。

SQL Server DO's and DONT's
http://www.codeproject.com/KB/database/sqldodont.aspx

文章太旧，SQl2005，2008做了很多优化，这个应该是base sql 2k的吧。
Do normalize your tale
这点太绝对，很多时候normalize带来的就是必须用join来查找相关的信息，降低performance，适当的denormalize table会显著提高 query的performance。

temporary table我一直用，没发现什么performance issue。有大虾给分析下temporary table对performance的影响么？

澳贼

原帖由 老衲 于 2011-1-24 15:00 发表

过于专业。那是for SQL Server Developers

谢谢。有没有推荐的书？

IsDonIsGood

Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization
http://www.microsoft.com/learnin ... 65&locale=en-us
比较深，要花时间看

[ 本帖最后由 IsDonIsGood 于 2011-1-24 15:25 编辑 ]

澳贼

原帖由 IsDonIsGood 于 2011-1-24 15:24 发表
Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization
http://www.microsoft.com/learnin ... 65&locale=en-us
比较深，要花时间看

非常谢谢！！！ 对于非sql developer,是不是太深了？

月亮

可以看一下这本：Microsoft SQL Server 2008 Internals (Pro - Developer)

不需要看的很精很深，就平时没事的时候看一下，了解一下SQL DB的存储，结构，出去找个DBA的工作基本没问题了。

这本书在2008以前叫Inside SQL Server 2005/2000，作者是很有名的Kalen Kelaney.

我觉得一个好的developer学多一点DB知识是有好处的，一个好的query不光要运行结果准确，还要能最大程度的优化performance，有时候你的query可能现在运行起来没问题，可1，2年之后数据库大了，也许它就成为bottleneck了。

澳贼

原帖由 月亮 于 2011-1-24 15:30 发表
可以看一下这本：Microsoft SQL Server 2008 Internals (Pro - Developer)

不需要看的很精很深，就平时没事的时候看一下，了解一下SQL DB的存储，结构，出去找个DBA的工作基本没问题了。

这本书在2008以前叫Inside SQL Serve ...

要不你把我招去吧。

bffbffbff

原帖由 IsDonIsGood 于 2011-1-24 15:19 发表

文章太旧，SQl2005，2008做了很多优化，这个应该是base sql 2k的吧。
Do normalize your tale
这点太绝对，很多时候normalize带来的就是必须用join来查找相关的信息，降低performance，适当的denormalize table会显著提高 query ...

同问， 我也一直用temp 多过table variable， 因为temp table可以在embedded store proc里有效，还可以加index，还在系统里可以temdbo里找到。
如果说performance，顶多是在系统里生成log造成整个数据库transaction log过大？

典

The basic rule is:
use table variable: For small dataset and queries
use temporary table for larger dataset

for table variable, there are fewer proc-recompilations , fewer locking, fewer logging

table variable also have index if you put primary key. But the index can not be changed(after the creation) because no statistics.

IsDonIsGood

是有点太深，涉及到不少很底层的DB的东西，本来这个系列就是给DBA和DBD看得。当初是冲着DBD的方向去找工的（想着无缝升级到DBA来着）。结果天不随人愿，不过多看看这些对你了解DB系统和了解他怎么parse query有好处，值得花点时间。

IsDonIsGood

原帖由 月亮 于 2011-1-24 15:30 发表
可以看一下这本：Microsoft SQL Server 2008 Internals (Pro - Developer)

不需要看的很精很深，就平时没事的时候看一下，了解一下SQL DB的存储，结构，出去找个DBA的工作基本没问题了。

这本书在2008以前叫Inside SQL Serve ...

当DBA这么简单~~~~~，不是吧。

bulaohu

入门的确不难，往后就看各人的修行了

乱码

原帖由 月亮 于 2011-1-24 15:30 发表
可以看一下这本：Microsoft SQL Server 2008 Internals (Pro - Developer)

不需要看的很精很深，就平时没事的时候看一下，了解一下SQL DB的存储，结构，出去找个DBA的工作基本没问题了。

这本书在2008以前叫Inside SQL Serve ...

嗯，这本书很值得看。

乱码

原帖由 IsDonIsGood 于 2011-1-24 15:19 发表

文章太旧，SQl2005，2008做了很多优化，这个应该是base sql 2k的吧。
Do normalize your tale
这点太绝对，很多时候normalize带来的就是必须用join来查找相关的信息，降低performance，适当的denormalize table会显著提高 query ...

temp table 和table variable 都有对temp db的 i/o,这是用cte over them的主要原因

showen

同意，适当的数据冗余能提高性能。

marklan001

原帖由 乱码 于 2011-1-24 16:55 发表


temp table 和table variable 都有对temp db的 i/o,这是用cte over them的主要原因

个人感觉Temp Table，Table Variable and CTE适用的场合不同
1. DATE SET很大且需要INDEX的时候用应当用Temp Table，不需要INDEX的时候用Table Variable。
2. 当SERVER内存充足时，Temp Table，Table Variable会保存在内存里，而不会被写入tempdb。
3. CTE更多的是简化代码，增强代码可读性，而不是提高performance。

[ 本帖最后由 marklan001 于 2011-1-24 20:48 编辑 ]

典

原帖由 marklan001 于 2011-1-24 19:43 发表

个人感觉Temp Table，Table Variable and CTE适用的场合不同
1. DATE SET很大且需要INDEX的时候用应当用Temp Table，不需要INDEX的时候用Table Variable。
2. 当SERVER内存充足时，Temp Table，Table Variable会保存在内存 ..
3. CTE更多的是简化代码，增强代码可读性，而不是提高performance.

Agree
我以前也以为CTE能提高Performance, 试着把几个复杂的程序里用CTE改写，程序虽然看起来很简洁了，但Performance的一些指标没有任何提高，