SQLCASEWHEN语句性能优化
背景：性能应该是功能的⼀个重要参考，特别是在⼤数据的背景之下！写SQL语句时如果仅考虑业务逻辑，⽽不去考虑语句效率问题，有可能导致严重的效率问题，导致功能不可⽤或者资源消耗过⼤。其中的⼀种情况是，处理每⽇增量数据的程序，实际执⾏过程中可能会进⾏全表扫描，效率与全量程序并⽆⼆致。
案例：
mio_log数据量：134,092,418条记录
freph_a01_fromtask3数据量：176,581,388条记录
⽣产系统上按照业务处理逻辑编写的SQL语句核⼼代码如下：
1SELECT (CASE
2 WHEN c.in_force_dateISNOT NULL
3 THEN (CASE
4 WHEN a.mio_date>=c.in_force_dateTHENa.mio_date
5 ELSE c.in_force_date
6 END )
7 WHEN c.in_force_dateISNULL THEN (CASE
8 WHEN a.mio_date>=a.plnmio_dateTHENa.mio_date
9 ELSE a.plnmio_date
10 END )
11 ELSE a.mio_date
12 END ) mio_date
13FROM dbo.mio_loga
14 INNER JOIN dbo.freph_a01_fromtask3c
15 ON atr_no = ctr_no
16 AND a.pol_code=c.pol_code
17WHERE ((c.in_force_dateISNOT NULL
18 AND((CASE
19 WHEN a.mio_date>=c.in_force_dateTHENa.mio_date
20 ELSE c.in_force_date
21 END ) BETWEEN @stat_begindateAND@stat_enddate))
22 OR(c.in_force_dateISNULL
23 AND((CASE
24 WHEN a.mio_date>=a.plnmio_dateTHENa.mio_date
25 ELSE a.plnmio_date
26 END ) BETWEEN @stat_begindateAND@stat_enddate)) )
导致虽然mio_log表的mio_date、plnmio_date字段，以及freph_a01_fromtask3表的in_force_date字段上均有索引，但是由于两表不同字段进⾏CASE WHEN⽐较，执⾏计划为聚集索引扫描：
优化思路：
由于mio_log表的mio_date、plnmio_date字段，以及freph_a01_fromtask3表的in_force_date字段上均有索引，可先通过单个
mio_date、in_force_date、plnmio_date索引取出增量时间段数据，在增量数据上进⾏不同表、字段的⽐对。
1SELECT (CASE
2 WHEN in_force_date IS NOT NULL
3 THEN ( CASE
4 WHEN mio_date >= in_force_dateTHENmio_date
5 ELSE in_force_date
6 END )
7 WHEN in_force_date IS NULL
8 THEN ( CASE
9 WHEN mio_date >= plnmio_dateTHENmio_date
10 ELSE plnmio_date
11 END )
12 ELSE mio_date
13 END ) mio_date
14from(
15 SELECT a.mio_date,
16 c.in_force_date,
17 a.plnmio_date,
18 a.MIO_LOG_ID
19 FROM dbo.mio_loga
20 INNER JOIN dbo.freph_a01_fromtask3c
21 ON atr_no = ctr_no
22 ANDa.pol_code=c.pol_code
23 WHERE
24 a.mio_dateBETWEEN@stat_begindateAND@stat_enddate
25union
26 SELECT a.mio_date,
27 c.in_force_date,
28 a.plnmio_date,
29 a.MIO_LOG_ID
30 FROM dbo.mio_loga
31 INNER JOIN dbo.freph_a01_fromtask3c
32 ON atr_no = ctr_no
33 ANDa.pol_code=c.pol_code
34 WHERE
35 c.in_force_dateBETWEEN@stat_begindateAND@stat_enddate
36union
37 SELECT a.mio_date,
38 c.in_force_date,
39 a.plnmio_date,
40 a.MIO_LOG_ID
41 FROM dbo.mio_loga
42 INNER JOIN dbo.freph_a01_fromtask3c
43 ON atr_no = ctr_no
44 ANDa.pol_code=c.pol_code
45 WHERE
46 a.plnmio_dateBETWEEN@stat_begindateAND@stat_enddate
47
48) T
49WHERE ((in_force_dateIS NOT NULL
50 AND((CASE
51 WHEN mio_date>= in_force_dateTHENmio_date
52 ELSE in_force_date
53 END ) BETWEEN @stat_begindateAND@stat_enddate))
54 OR(in_force_dateIS NULL
55 AND((CASE
56 WHEN mio_date>= plnmio_dateTHENmio_date
57 ELSE plnmio_date
58 END ) BETWEEN @stat_begindateAND@stat_enddate)) )该语句存在两个问题：
1. 如果⼦查询中mio_log、freph_a01_fromtask3没有主键，则需通过ROWID标识不同记录，即如果没有主键，可以通过ROWID进⾏替换。
ROWID这个概念在Oracle中⾮常重要，使⽤也⾮常⼴泛，其意义如下：
ROWIDPseudocolumn
Foreach row in the database, the ROWID pseudocolumn returns the address of therow. Oracle Database rowid values contain information necessary to locate arow:
· The dataobject number of the object
· The datablock in the datafile in which the row resides
· The positionof the row in the data block (first row is 0)
· The datafilein which the row resides (first file is 1). The file number is relative to thetablespace.
SQLServer中并没有ROWID这个概念， SQL Server2008及以后版本中%%physloc%%虚拟列与ROWID最相近，信息如下：
The closest equivalent tothis in SQL Server is the rid which has three components File:Page:Slot.
In SQL Server 2008 it ispossible to use the undocumented and unsupported %%physloc%% virtual column to see this. Thisreturns a binary(8) value with the Page ID in the firstfour bytes, then 2 bytes for File ID, followed by 2 bytes for the slot locationon the page.
The scalar function sys.fn_PhysLocFormatter or the sys.fn_PhysLocCracker TVF can be used to convert this into amore readable form.sql语句优化方式
1CREATE TABLET(XINT);
2
3INSERT INTOTVALUES(1),(2)
4
5SELECT %%physloc%%AS[%%physloc%%],
6 sys.fn_PhysLocFormatter(%%physloc%%)AS[File:Page:Slot]
7FROM T
%%physloc%%File:Page:Slot
0x7600000001000000(1:118:0)
0x7600000001000100(1:118:1)
Note that this is not leveraged by the queryprocessor. Whilst it is possible to use this in a WHERE clause
1SELECT *FROMT
2WHERE %%physloc%%=0x7600000001000000
SQL Server will not directly seek to thespecified row. Instead it will do a full table scan, evaluate %%physloc%% foreach row and return the one that matches (if any do).
2. 该语句有parameter sniffing问题：
当使⽤存储过程的时候，总是要使⽤到⼀些变量。变量有两种，⼀种是在存储过程的外⾯定义的，当调⽤存储过程的时候，必须要给它代⼊值，SQLServer在编译时知道它的值是多少。还有⼀种变量是在存储过程⾥⾯定义的。它的值是在存储过程的语句执⾏过程中得到的。对这种本地变量，SQLServer在编译时不知道它的值是多少。
SQLServer在处理存储过程时，为了节省编译时间，是⼀次编译多次使⽤的。那么计划重⽤就有两个潜在问题：
（1） 对于第⼀类变量，根据第⼀次运⾏时带⼊的值⽣成的执⾏计划，是不是就能够适合所有可能的变量值？
（2） 对于第⼆类本地变量，SQL Server在编译时并不知道它的值是多少，那怎么选择“合适”的执⾏计划？
parametersniffing”问题的定义：因为语句的执⾏计划对变量值很敏感，⽽导致重⽤执⾏计划会遇到性能问题。本地变量做出来的执⾏计划是⼀种⽐较“中庸”的⽅法，⼀般不会有parameter sniffing那么严重，很多时候，它还是解决parametersniffing的⼀个候选⽅案。
解决parameter sniffing问题的⽅法：
（1） ⽤exec()⽅式运⾏动态SQL语句：如果在存储过程⾥不是直接运⾏语句，⽽是把语句带上变量，⽣成⼀个字符串，再让exec()命令多动态语句运⾏，那SQL Server就会在运⾏到这个语句的时候，对动态语句进⾏编译。这时，SQLServer已经知道了变量的值，会根据值⽣成优化的执⾏计划，从⽽绕过parametersniffing问题。
（2） 使⽤本地变量：如果把变量值赋给⼀个本地变量，SQLServer在编译的时候是没有办法知道这个本地变量的值的。所以它会根据表格⾥数据的⼀般分布情况“猜测”⼀个返回值。不管⽤户在调⽤存储过程的时候带⼊的变量值是多少，做出来的执⾏计划都是⼀样的。⽽这样的执⾏计划⼀般⽐较“中庸”，不会是最优的执⾏计划，但是对⼤多数变量值来讲，也不会是⼀个很差的执⾏计划。该⽅法的好处是保持了存储过程的优点，缺点是要修改存储过程，⽽执⾏计划也不是最优的。
（3） 在语句⾥使⽤query hint指定执⾏计划：
在SELECT、INSERT、UPDATE、DELETE语句最后，可以加⼀个“Option(<query_hint>)”⼦句，对SQL Server将要⽣成的执⾏计划进⾏指导。⽬前的query_hint很强⼤，有⼗⼏种hint。完整的定义如下：
1<query_hint>::=
2{ {HASH| ORDER } GROUP
3 | {CONCAT| HASH | MERGE} UNION
4 | {LOOP| MERGE | HASH} JOIN
5 | FASTnumber_rows
6 | FORCEORDER
7 | MAXDOPnumber_of_processors
8 | OPTIMIZEFOR( @vaariable_name= literal_constant[ , ...n ])
9 | PARAMETERIZATION{SIMPLE | FORCED }
10 | RECOMPILE
11 | ROBUSTPLAN
12 | KEEPPLAN
13 | KEEPFIXEDPLAN
14 | EXPANDVIEWS
15 | MAXRECURSIONnumber
16 | USEPLANN'xml_plan'
17}
这些hint的⽤途不⼀样。有些是引导执⾏计划使⽤什么样的运算的，例如{HASH|ORDER}GROUP、{CONCAT|HASH|MERGE} UNION、{LOOP|MERGE|HASH}JOIN。有些是防⽌重编译的，例如PARAMETERIZATION{SIMPLE|FORCED
}、KEEPPLAN、KEEPFIXEDPLAN，有些是强制重编译的，如RECOMPILE。有些是影响执⾏计划的选择的，
如FAST number_rows、FORCEORDER、MAXDOP number_of_processors、OPTIMIZE FOR(@vaariable_name=
literal_constant[ , ...n ])，它们是和在不同的场合。具体定义参见SQL Server联机帮助。
为避免parameter sniffing问题，主要有以下⼏种常见query hint
（1）Recompile
Recompile这个查询提⽰告诉SQL Server，语句在每⼀次存储过程运⾏的时候，都要重新编译⼀下。这样就能够使SQL Server根据当前变量的值，选⼀个最好的执⾏计划。对前⾯的那个例⼦，我们可以这么改写。
1CREATE PROCNosniff_queryhint_recompile(@iINT)
2AS
3 SELECT Count(b.SalesOrderID),
4 Sum(p.Weight)
5 FROM dbo.SalesOrderHeader_testa
6 INNER JOIN dbo.SalesOrderDetail_testb
7 ON a.SalesOrderID=b.SalesOrderID
8 INNER JOIN Production.Productp
9 ON b.ProductID=p.ProductID
10 WHERE a.SalesOrderID=@i
11 OPTION (recompile)
12go
和这种⽅法类似的，是在存储过程的定义⾥直接指定"recompile"，也能达到避免parameter sniffing的效果。
1CREATE PROCNosniff_spcreate_recompile(@iINT)
2WITH recompile
3AS
4 SELECT Count(b.SalesOrderID),
5 Sum(p.Weight)
6 FROM dbo.SalesOrderHeader_testa
7 INNER JOIN dbo.SalesOrderDetail_testb
8 ON a.SalesOrderID=b.SalesOrderID
9 INNER JOIN Production.Productp
10 ON b.ProductID=p.ProductID
11 WHERE a.SalesOrderID=@i
12
13go
（2） 指定JOIN运算
1CREATE PROCNosniff_queryhint_joinhint(@iINT)
2AS
3 SELECT Count(b.SalesOrderID),
4 Sum(p.Weight)
5 FROM dbo.SalesOrderHeader_testa
6 INNER JOIN dbo.SalesOrderDetail_testb
7 ON a.SalesOrderID=b.SalesOrderID
8 INNER hash JOIN Production.Productp
9 ON b.ProductID=p.ProductID
10 WHERE a.SalesOrderID=@i
11go
（3） OPTIMIZEFOR（@variable_name= literal_constant[ , …n] ）
使⽤OPTIMIZE FOR 这个查询指导，就能够让SQL Server做到这⼀点。这是SQL 2005以后的⼀个新功能。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论