SQL调优

SQL调优

分两部分:

1. SQL调优:主要是根据SQL的执行计划对SQL语句的结构，SQL中表的联接方式，表扫描的顺序，表扫描的方法进行修改,以达到响应速度的提高；
2. 存储过程调优:主要是根据过程中的业务逻辑进行优化；同时存储过程中可能存在有性能差的SQL语句的分析；

The Query Logical Flw Diagram

非聚集和聚集查询流程图

SQL调优

一、SELECT语句优化

• 避免使用“”，因为数据库在解析的过程中, 会将’’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间，应按照需要多少列，就使用多少列的原则；
• 字段的顺序尽量按索引的顺序进行排列；
• 根据业务逻辑，SELECT列表中有无不需要选择的列；

二、FROM子句优化

• 调整表扫描的顺序；
• 调整表的联接方式；
• 调整表扫描的方法；

1.调整表扫描的顺序：

多表查询中表的扫描顺序非常重要，一个原则是：扫描的第一个表一定是返回记录最少的表；要求where子句的谓词中最好有主键或唯一键的条件或返回一条记录的条件
方法:

1、将WHERE子句的主键或唯一约束的条件放到WHERE子句的最前面；
2、使用OPTION（Force Order)提示；

2.调整表的联接方式

嵌套循环联接（loop join）
合并联接（merge join）
哈希联接（hash join）

2.1调整表的联接方式：嵌套循环联接

嵌套循环联接：一般是两个表都比较小，同时这两个表都是使用的索引扫描，因此当两个表是嵌套循环联接，一般这两个表都使用了索引扫描；
条件：两表是较小的表；
两表使用了索引；
小表应当是内表，如果它非常小；
有最好索引的表应当是内表；
有限制条件子句的表应当是外表；
方法：使用LOOP提示：如select * from a inner loop join b on …
或：OPTION（INNER）

2.2 调整表的联接方式：合并联接

合并联接：当两个表没有索引或两个表都是大表时，往往这两个表是使用合并联接；
条件：两个表记录数都比较大；
两个表没有可以使用的索引；
需要排序；
方法：MERGE提示；
OPTION(MERGE）；

2.3 调整表的联接方式：哈希联接

当两个表中一个是大表，一个是小表，并且小表没有索引时，表联接使用哈希联接；
方法：HASH提示；
OPTION（HASH）

3 调整表扫描的方法

表扫描的方法 ：索引扫描，全表扫描
关系：Table scan->index scan->index seek
与where子句中的条件紧密相关

3.1 表连接技巧

• 提高连接查询的最好方法是，尽可能在查询中添加WHERE子句避免连接表的条件中的字段含有大量的重复值
• 连接表的条件的字段类型理想的是数字型的数据类型，而不是char,varchar,或非数字的类型
• 具有索引的列做了连接表的条件，那么两个字段应当有相同的数据类型，最好是具有相同的宽度

使用ANSI JOIN Syntax
ANSI JOIN Syntax
SELECT fname, lname, department FROM names INNER JOIN departments ON names.employeeid = departments.employeeid

• 如果查询语句中经常出现4个或4个以上的表的连接，应考虑使用反范式理论重新设计表，将一些合并，以减少连接表的数量
• 如果查询比较慢，这种情况下通常查询中含有hint，删除 hint再重新测试，是否可以提高性能
• 对于非常大的连接查询，可以考虑将连接的大表单独存放到一个物理文件中
• 如果选择用join还是子查询时执行同一个任务时，通常join(经常是一个outer join)较快，但如果返回的数据较少，或连接字段上没有索引，这时子查询可能运行较快
• 如果在select子句中包含子查询，子查询中含有分组函数，这时查询是很慢的。采取的方面是使用join来代替子查询
• 当查询是用left outer join,请仔细检查，以确保你真正使用外连接
• 当有一个或多个join的查询时，应检查是否执行计划中用到hash join。当查询优化器请求连接两个没有合适索引的表时，查询优化器经常执行一个hash连接。

3.子查询

三种方法处理一个子查询
1、转化为标准连接，然后做为一个连接来处理；
2、out-to-in：对于外查询的每一行记录，在内查询中查找（使用exists查询），通常包含相互关系；
3、in-to-out：对于内查询的每一行记录，在外查询中查找（使用in查询），当内查询返回比外查询更少的行时用此方法；
通常情况下，对于子查询的in-to-out计划发生在⑴、开始用比较操作；⑵、不包括相互关系条件；
如果一个子查询是in-to-out，那么这个关系应当是少行对多行；

嵌套循环连接和子查询

区别：
一个子查询只需要找到一个单一的匹配行，因此它可以很早中断从内部的循环；一个连接需要所有的匹配行，因此它必须查询所有的行；
喜欢用连接来代替子查询的一个主要的特点是它不需要告诉数据库做什么。通过写连接，你强迫数据库遵循一个指定的计划；
喜欢用子查询的主要的特点是一个子查询循环可以有少数反复，因此子查询比连接快；
通常连接较好，但是如果通常对于驱动表的每一行相比，被驱动的查询返回几行记录，并且尤其如果匹配的行首先来自于被驱动查询时，那么子查询要比连接好；

三、 IN和EXISTS

IN：

SELECT * FROM Table1
                                        WHERE Table1.column1 IN
                                        (SELECT Table2.column1 FROM Table2)
IN子查询倾向于合并连接
1
2
3
4

EXISTS：

SELECT * FROM Table1
                                        WHERE EXISTS
                                        (SELECT * FROM Table
                                        WHERE Table1.column1 = Table2.column1)
EXISTS子查询倾向于嵌套循环连接
1
2
3
4
5

何时使用exists或in

1、out-to-in子查询用exists；
2、in-to-out子查询用in；
3、如果外查询比内查询有更多的行时，用IN；
4、如果外查询有一个附加的限制表达式时，用exists；
5、如果外查询是when not …时，用not exists；

四、 WHERE子句优化

谓词包括三类：1、连接谓词；
2、单行限定谓词；
3、多行限定谓词；
三类谓词的读取顺序：单行谓词、多行谓词、连接谓词；
为了保证按上述顺序执行SQL，应将限制谓词上推，先执行限制谓词，最后执行连接谓词；
将谓词上推的方法：可以采用视图的方式，即对有限制谓词的表的查询建立视图，然后再连接视图进行查询；
采用内部视图的方法；

WHERE子句优化技巧

• 避免在列字段上使用函数。如果你不能避免使用函数，不要使用upper函数来确保大小写的敏感性，应使用lower来代替；

• 常量优化：检查SQL语句中是否存在隐式转换数据类型的情况

• 操作符优化：

• AND：当WHERE所有条件都是相等条件，那么DBMS是从左到右（从上到下）的顺序评估一个连接的AND表达式（除了ORACLE，当它处于基于代价的优化器时，是从右到左（从下到上）的顺序进行评估AND表达式，基于规则的优化器，是同上述规则一致）；
  因此，最左边的第一个条件的限制范围应该是最大的，即返回的记录是最少的

• OR：当你写OR表达式时，将返回最多记录的表达式放在左边，返回记录最少的应放在右边，即返回最多记录的表达式应最先评估，基于规则的ORACLE则没有改变；
  对于一个连接的OR表达式，相同的列的表达式应该放在一起；
  对于相同的列的OR表达式，可转化为IN表达式；

• CASE：
  1、如果结果是一个涉及数字的缩影，在CASE表达式中放置一个搜索条件；
  2、在选择列表中，对最后的过滤条件用CASE表达式；
  例：WHERE slow_function(column1) = 3 or slow_function(column1) = 5
  转化为：…
  WHERE 1 = CASE slow_function(column1)
  WHEN 3 THEN 1
  WHEN 5 THEN 1
  END

• DISTINCT：用EXISTS或IN来代替DISTINCT，因为子查询是减少记录数的，当查询条件第一条记录时，子查询就会退出，进入下一个记录查询中；

五、GROUP BY子句优化

• GROUP BY：对通过WHERE子句过滤出来的结果集进行分组，分组时与SELECT子句无关，然后再根据SELECT子句的聚集函数对已分组后的每组记录进行计算。
• 分组，当分组列有一列以上时，按第一、二、三……列的顺序进行分组，是当按第一列分组时，第一列有重复值时，将按第二列进行分组，以此类推，但当第一列或后序列是唯一值时，如当按主键分组时，后序列分组是没有意义的；

GROUP BY子句优化技巧

1、如果你保留较少的分组列数时，GROUP BY子句的性能较好；
2、通过用分组函数来避免分组多余的列；
3、GROUP BY趋向于减少行数量，JOIN趋向于扩充行数据，所以当你在分组连接表时， 应当先分组后连接
4、当你分组连接表时，GROUP BY子句中的列应当来自于你应用函数集的那个同一个表中；
5、如果你的查询中不包括分组函数，可以用DISTINCT来代替GROUP BY；
6、当两表以上的连接中含有GROUP BY子句，如果两表连接后的记录较大，可以考虑将GROUP BY子句下移到连接之前执行，以减少分组时的记录数，方法是采用内联视图；

六、ORDER BY子句优化

ORDER BY：对通过WHERE子句过滤出来的结果集进行排序；
影响排序的三个条件：1、选择的行的数量；
2、ORDER BY子句中列的数量；
3、ORDER BY子句中定义的列的长度；
应严格控制ORDER BY子句中列的数量；
技巧：
1、varchar数据类型的长度影响排序，长度越小速度越快；
2、排序时integer比smallint整数类型要快，因为32bit是计算机的字符长度；
3、排序时integer比char数据类型要快，因为不能在同一时间比较大于2个字节的一个字符串；
ORDER BY子句按索引排序的条件：
1、ORDER BY中所有的列必须包含在相同的索引中，并保持在索引中的排列顺序；
2、ORDER BY中所有的列必须定义为非空；

存储过程优化

• 优点：可以减少网络传输量，提高应用程序的性能

• 过程的执行计划可以再利用，以减少服务器负载
  客户端执行请求更有效。如，一个应用程序需要插入一个大数据库的二进制值到一个图像数据类型的字段，如果不使用过程来实现，那么必须binary值到一个字符串给sql server。当sql server接收到它时，它必须将字符串转化为binary格式，这就产生很大的负载。过程就可以消除，如用一个binary格式的参数，将值从应用程序中传给sql server，这样就可以减少不必要的负载

• 过程可以提高代码的再用率。

• 过程可以压缩业务逻辑

• 过程可以对数据提供较好的安全性，开发人员可以直接从表中select,insert,update,delete，以减少DBA的时间
  1、set nocount on：默认情况下，每次过程被执行时，被过程执行所影响的行数的消息会发从服务器发送到客户端，这些消息在客户端很少是有用的，为减少网络负载，建议关闭
  2、在过程中保持事务尽可能的小。这样可以减少锁的数据，提高应用程序的性能
  两种方法：1、分解一个整个任务到多个小任务或多个过程，每个小任务可提交事务 2．使用SQL SERVER语句批量操作的优势，减少客户端和服务器之间的往返的次数
  3、如果查询的执行计划每次执行时都要发生改变，可以使用with recomplie选项
  4、在应用程序中使用大量的临时的存储过程需要考虑。考虑大量的临时存储过程可能增加系统表的争用，而降低性能，解决方法是使用sp_executesql，它可以提供临时存储过程的优点，但不在系统表中存储数据，避免争用的问题
  5、同一个过程中的所有对象应当具有同一个对象所有者，书写格式：object_owner.object_name，如果不指定对象的所有者，sql server必须执行对象的命名方案，以确定对象的所有者，这样将引起性能下降。如果对象的所有者没有SQL SERVER不使用过程在内存中的执行计划。这时sql必须重新生成新的执行计划，将降低性能
  6、使用sp_executesql代替execute语句来执行一个TSQL字符串
  7、避免嵌套过程
  8、在输入参数到存储过程之前，应当在应用程序中验证这些参数值
  9、如果过程需要返回一个值，而不是一个记录集，建议用一个output语句来返回这个单一的值
  10、避免在一个单一的事务insert,delete,update大量记录数的操作，因为这样操作，所有涉及到的记录将被锁定直到事务结束。

• 过程重编译太频繁：
  三种情况：
  1、行被修改和自动统计信息更新：
  a.用sp_executesql代替execute运行TSQL语
  b.用小的子过程代替大的过程。
  c.如果过程中使用临时表，用keep plan查询提示
  2、在同一个过程中存在DDL和DML语句不必要的过程重编译：如果有DDL，它将第一时间越过DML语句自动编译，为防止这种情况，应当将DDL语句写在过程的最前面，防止与DML语句混合
  3、指定临时表操作的不必要过程重编译：任何在过程中涉及的临时表都应当在过程内部创建，不要在过程外部创建临时表或用sp_executesql或execute语句来创建临时表
  a.过程中不应当在申明游标中引用一个临时表
  b.在过程应该有drop table语句
  c.过程中的控制流语句中不应当创建临时表

1、建议
将:
insert into log_SOInvoice (sonumber, wh ,invoicenumber)   
 select top 5000 sonumber,'50' , 1000 from newsql.newegg.dbo.changeorderloghistory  
 where sonumber in ( select sonumber from act..newegg_invoicemaster(nolock))  
 and changeordertime> getdate() - 30    
 and sonumber not in ( select sonumber from log_SOInvoice (nolock) where wh = '50') 

 CPU time = 484 ms,  elapsed time = 2230 ms.
修改为：
insert into log_SOInvoice (sonumber, wh ,invoicenumber)   
 select top 5000 sonumber,'50' , 1000 from newsql.newegg.dbo.changeorderloghistory  
 where changeordertime> getdate() - 30    
 and sonumber not in ( select sonumber from log_SOInvoice (nolock) where wh = '50')  
 and sonumber in ( select sonumber from act..newegg_invoicemaster(nolock)) 


2、select ReferenceSoNumber,ItemNumber,SerialNumber,ScanDate,ScanUser,LargeItemFlag,WarehouseNumber,
ShippingUser,ShippingDate,TransactionNumber 
from DropShipSerialNumber07  
where shippingDate>'12/18/2003' 
and transactionnumber not in (select transactionnumber from warehouse4..UploadSerialnumberLog07    
				where datediff(day,indate,getdate())<8)  
				and datediff(day,shippingdate,getdate())<7  
执行结果：CPU time = 21437 ms,  elapsed time = 33927 ms.

修改为：
select ReferenceSoNumber,ItemNumber,SerialNumber,ScanDate,ScanUser,LargeItemFlag,WarehouseNumber,
ShippingUser,ShippingDate,TransactionNumber 
from DropShipSerialNumber07 (nolock index(ShippingDate)) 
where shippingdate>dateadd(day,-7,getdate()) 
--shippingDate>'12/18/2003' 
and transactionnumber not in (select transactionnumber from warehouse4..UploadSerialnumberLog07(nolock)    
				where indate>dateadd(day,-8,getdate()))
执行结果： CPU time = 875 ms,  elapsed time = 2005 ms.


3.建议将：
 insert into log_SOInvoice (sonumber, wh ,invoicenumber) 
 select top 5000 a.sonumber,a.warehousenumber ,b.invoicenumber from newegg_sotransaction a(nolock)  
 inner join newegg_somaster b  (nolock) on a.sonumber = b.sonumber    
 where b.status <> 'v'  and a.warehousenumber is not null  
 and b.invoicenumber is not null and b.invoicenumber <> 0   
 and a.sonumber not in ( select sonumber from log_SOInvoice (nolock))   
 and warehousenumber > '' and warehousenumber is not null and  warehousenumber <>'00' and warehousenumber  <'90'  
 group by a.sonumber,a.warehousenumber,b.invoicenumber  
 
 CPU time = 18594 ms,  elapsed time = 6215 ms.
修改为：
 insert into log_SOInvoice (sonumber, wh ,invoicenumber) 
 select top 5000 a.sonumber,a.warehousenumber ,b.invoicenumber from newegg_sotransaction a(nolock)  
 inner join newegg_somaster b  (nolock) on a.sonumber = b.sonumber    
 where not exists( select sonumber from log_SOInvoice (nolock) where a.sonumber = sonumber ) 
 and b.status <> 'v'  and a.warehousenumber is not null  
 and b.invoicenumber is not null and b.invoicenumber <> 0   
 and warehousenumber > '' and warehousenumber is not null and  warehousenumber <>'00' and warehousenumber  <'90'  
 group by a.sonumber,a.warehousenumber,b.invoicenumber


4.建议将：
 update GCI set GCI.LeftAmount = GCI.LeftAmount + GD.SOAmount    
                  From GiftCertificateItem GCI ,giftredeem GD     
                  Where GCI.giftcode = GD.RedeemGiftCode     
                        And GD.SONumber = @SONumber     
                        And GD.Status ='O'  
update GCI set GCI.status = Case when GCI.giftunitprice < GCI.leftamount Then 'A' Else 'U' End     
                      
                                From GiftCertificateItem GCI ,giftredeem GD     
                  Where GCI.giftcode = GD.RedeemGiftCode     
                        And GD.SONumber = @SONumber     
                        And GD.Status ='O'    
合并为：
update GCI set GCI.LeftAmount = GCI.LeftAmount + GD.SOAmount，
	       GCI.status = Case when GCI.giftunitprice < GCI.leftamount Then 'A' Else 'U' End     
From GiftCertificateItem GCI ,giftredeem GD     
                  Where GCI.giftcode = GD.RedeemGiftCode     
                        And GD.SONumber = @SONumber     
                        And GD.Status ='O'  



5.建议将SQL语句修改为UNION ALL连接；
SELECT distinct a.ItemNumber,
       b.itemcatalog,
       ISNULL(c.ComboID,
              0) AS ComboID,
       c.ItemNumber AS ComboItem,
       ISNULL(c.ItemCatalog,
              0) AS comboItemCatalog
FROM   ECommerce2005.dbo.IM_ItemBuyAlong a (nolock)
       INNER JOIN abs.dbo.Arinvt01 b (nolock)
         ON a.ItemNumber = b.Item
       LEFT JOIN ECOMMERCE2005.DBO.IM_ComboTransaction c (NOLOCK)
         ON ((c.ItemNumber = a.itemNumber
              OR c.ItemCatalog = b.Itemcatalog)
             AND c.ComboGroupid <> a.comboGroupid)
            OR c.combogroupid IN (SELECT combogroupid
                                  FROM   ECOMMERCE2005.DBO.IM_ComboGiftItem(NOLOCK)
                                  WHERE  itemNumber = a.itemNumber
                                       AND comboGroupid <> a.comboGroupid)
修改为：
SELECT a.ItemNumber,
       b.itemcatalog,
       ISNULL(c.ComboID,
              0) AS ComboID,
       c.ItemNumber AS ComboItem,
       ISNULL(c.ItemCatalog,
              0) AS comboItemCatalog
FROM   ECommerce2005.dbo.IM_ItemBuyAlong a (nolock)
       INNER JOIN abs.dbo.Arinvt01 b (nolock)
         ON a.ItemNumber = b.Item
       LEFT JOIN ECOMMERCE2005.DBO.IM_ComboTransaction c (NOLOCK)
         ON ((c.ItemNumber = a.itemNumber
              OR c.ItemCatalog = b.Itemcatalog)
             AND c.ComboGroupid <> a.comboGroupid)
union 

SELECT a.ItemNumber,
       b.itemcatalog,
       ISNULL(c.ComboID,
              0) AS ComboID,
       c.ItemNumber AS ComboItem,
       ISNULL(c.ItemCatalog,
              0) AS comboItemCatalog
FROM   ECommerce2005.dbo.IM_ItemBuyAlong a (nolock)
       INNER JOIN abs.dbo.Arinvt01 b (nolock)
         ON a.ItemNumber = b.Item
       LEFT JOIN ECOMMERCE2005.DBO.IM_ComboTransaction c (NOLOCK)
         ON c.combogroupid IN (SELECT combogroupid
                                  FROM   ECOMMERCE2005.DBO.IM_ComboGiftItem(NOLOCK)
                                  WHERE  itemNumber = a.itemNumber
                                         AND comboGroupid <> a.comboGroupid)

原SQL语句执行结果： CPU time = 11187 ms,  elapsed time = 11264 ms.
修改为UNION ALL后执行结果：CPU time = 3486 ms,  elapsed time = 2412 ms.

6.建议将SQL语句：
select * from scm..potran01 (nolock) where purno = 912926 order by TransactionNumber
修改为：

select * from scm..potran01 (nolock) where purno = '912926'

order by TransactionNumber
结果：修改前：CPU time = 1109 ms,  elapsed time = 5319 ms.
      修改后：CPU time = 0 ms,  elapsed time = 40 ms.


7.可以考虑将SQL语句：
Select * From CodeCenter..ViewPropertiesInAdvSearch 
Where CatalogID=22 
Order By Priority,ValuePriority,ValueDescription
修改为：
Select * From CodeCenter..ViewPropertiesInAdvSearch 
Where CatalogID=22 
Order By Priority,ValuePriority,ValueDescription
option(maxdop 1)
结果：修改前：CPU time = 3284 ms,  elapsed time = 14832 ms.
      修改后：CPU time = 766 ms,  elapsed time = 1183 ms.

8.建议将SQL语句：
SELECT ACount = (SELECT COUNT(*)
                 FROM   ABS.dbo.arinvt01 a (nolock)
                        INNER JOIN codecenter..itemdescription b (nolock)
                          ON a.item = b.itemnumber
                 WHERE  NEWEGGITEMMARK > 0
                        AND checked = 1),
       VCount = (SELECT COUNT(*)
                 FROM   ABS.dbo.arinvt01 a (nolock)
                        INNER JOIN codecenter..itemdescription b (nolock)
                          ON a.item = b.itemnumber
                 WHERE  NEWEGGITEMMARK > 0
                        AND (ITEM LIKE '%sf'
                             OR ITEMCATALOG = 346
                             OR ITEM LIKE '50-%')
                        AND checked = 1),
       InStock = (SELECT COUNT(*)
                  FROM   ABS.dbo.arinvt01 A (nolock)
                         INNER JOIN inventory..ItemInventory B (NOLOCK)
                           ON A.ITEM = B.Item
                         INNER JOIN codecenter..itemdescription c (nolock)
                           ON a.item = c.itemnumber
                  WHERE  A.NEWEGGITEMMARK > 0
                         AND B.newegg_avail > 0
                         AND A.ITEM NOT LIKE '%sf'
                         AND A.ITEMCATALOG <> 346
                         AND A.ITEM NOT LIKE '50-%'
                         AND checked = 1),
       VirtualInStock = (SELECT COUNT(*)
                         FROM   ABS.dbo.arinvt01 A (nolock)
                                INNER JOIN inventory..ItemInventory B (NOLOCK)
                                  ON A.ITEM = B.Item
                                INNER JOIN codecenter..itemdescription c (nolock)
                                  ON A.item = c.itemnumber
                         WHERE  A.NEWEGGITEMMARK > 0
                                AND B.newegg_avail > 0
                                AND (A.ITEMCATALOG = 346
                                     OR A.ITEM LIKE '%SF'
                                     OR A.ITEM LIKE '50-%')
                                AND checked = 1),
       Refurbish = (SELECT COUNT(*)
                    FROM   ABS.dbo.arinvt01 A (nolock)
                           INNER JOIN inventory..ItemInventory B (NOLOCK)
                             ON A.ITEM = B.Item
                           INNER JOIN codecenter..itemdescription c (nolock)
                             ON a.item = c.itemnumber
                    WHERE  A.NEWEGGITEMMARK > 0
                           AND B.newegg_avail > 0
                           AND A.ITEM LIKE '%R'
                           AND checked = 1)
修改为：
SELECT ACount = SUM(ACount),
       VCount = SUM(VCount),
       InStock = SUM(InStock),
       VirtualInStock = SUM(VirtualInStock),
       Refurbish = SUM(Refurbish)
FROM   (SELECT ACount = COUNT(*),
              VCount = COUNT(CASE 
                             WHEN (ITEM LIKE '%sf'
                                   OR ITEMCATALOG = 346
                                   OR ITEM LIKE '50-%') THEN 1
                             END),
              InStock = 0,
              VirtualInStock = 0,
              Refurbish = 0
       FROM   ABS.dbo.arinvt01 a (nolock)
              INNER JOIN codecenter..itemdescription b (nolock)
                ON a.item = b.itemnumber
       WHERE  NEWEGGITEMMARK > 0
              AND checked = 1
       UNION ALL
       SELECT ACount = 0,
              VCount = 0,
              InStock = COUNT(CASE 
                              WHEN (A.ITEM NOT LIKE '%sf'
                                    AND A.ITEMCATALOG <> 346
                                    AND A.ITEM NOT LIKE '50-%') THEN 1
                              END),
              VirtualInStock = COUNT(CASE 
                                     WHEN (A.ITEMCATALOG = 346
                                           OR A.ITEM LIKE '%SF'
                                           OR A.ITEM LIKE '50-%') THEN 1
                                     END),
              Refurbish = COUNT(CASE 
                                WHEN A.ITEM LIKE '%R' THEN 1
                                END)
       FROM   ABS.dbo.arinvt01 A (nolock)
              INNER JOIN inventory..ItemInventory B (NOLOCK)
                ON A.ITEM = B.Item
              INNER JOIN codecenter..itemdescription c (nolock)
                ON A.item = c.itemnumber
       WHERE  A.NEWEGGITEMMARK > 0
              AND B.newegg_avail > 0
              AND checked = 1) A
结果：修改前：CPU time = 13375 ms,  elapsed time = 9772 ms.
      修改后：CPU time = 8916 ms,  elapsed time = 4776 ms.



9.建议将SQL语句：
SELECT SoNumber
FROM   [CNSLS].[dbo].[SOMaster](NOLOCK)
WHERE  CompanyCode = 1006
       AND Status = 'O'
       AND CreditCardVerifyMark = 'G'
       AND status <> 'V'
       AND (InvoiceNumber IS NULL 
            OR InvoiceNumber = 0)
       AND AcctPostDate IS NOT NULL 
       AND SoNumber NOT IN (SELECT SoNumber
                            FROM   [CNSLS].[dbo].[DownloadSO](NOLOCK))
修改为：
SELECT SoNumber
FROM   [CNSLS].[dbo].[SOMaster] a(NOLOCK)
WHERE  CompanyCode = 1006
       AND Status = 'O'
       AND CreditCardVerifyMark = 'G'
    --   AND status <> 'V'
       AND (InvoiceNumber IS NULL 
            OR InvoiceNumber = 0)
       AND AcctPostDate IS NOT NULL 
       AND not exists (SELECT SoNumber
                            FROM   [CNSLS].[dbo].[DownloadSO](NOLOCK) where SoNumber=a.SoNumber)
option (maxdop 1)

结果：修改前：CPU time = 3200 ms,  elapsed time = 4496 ms.
      修改后：CPU time = 1297 ms,  elapsed time = 1382 ms.

10.由于abs..PO_Detail_Query中的potran01表中的ITEM字段的值长度最大为10，因此建议将：
SELECT TOP 200 *
FROM   abs..PO_Detail_Query
WHERE  Item LIKE '[0-9]%'
       AND Item LIKE '%19-103-759%'
       AND purdate >= '10/16/2006 7:21:39 AM'
       AND purdate <= '1/17/2007 7:21:39 AM'
修改为：
SELECT TOP 200 *
FROM   abs..PO_Detail_Query
WHERE  Item = '19-103-759'
       AND purdate >= '10/16/2006 7:21:39 AM'
       AND purdate <= '1/17/2007 7:21:39 AM'

结果：修改前：CPU time = 6047 ms,  elapsed time = 10633 ms.
      修改后：CPU time = 0 ms,  elapsed time = 5 ms.
2、Personal表后未加NOLOCK；

11.建议将SQL语句：
SELECT Item = rtrim(b.ItemNumber) + 'R', 
       RefurbishIn = SUM(b.Quantity),  
       RefurbishOut = 0,
	RefurbishAdjust = 0  
       FROM [abs].dbo.RefurbishMaster AS a (NOLOCK)  
       INNER JOIN [abs].dbo.RefurbishTransaction AS b (NOLOCK)  
       ON a.RTNumber = b.RTNumber  
       WHERE a.ReceivingDate >= @pThisMonthBeginDate  
       AND a.ReceivingDate < @pOneDayAfterEffectDate  
       AND b.ItemNumber LIKE '%-%'  
       GROUP BY b.ItemNumber 
  
       UNION ALL  
       SELECT Item = b.ItemNumber,
	RefurbishIn = 0,  
       RefurbishOut = SUM(b.Quantity),  
       RefurbishAdjust = 0  
       FROM abs.dbo.RefurbishMaster AS a (nolock)  
       INNER JOIN [abs].dbo.RefurbishTransaction AS b (nolock)  
       ON a.RTNumber = b.RTNumber  
       WHERE a.ReceivingDate >= @pThisMonthBeginDate  
       AND a.ReceivingDate < @pOneDayAfterEffectDate  
       AND a.Source = '3'  
       AND b.ItemNumber LIKE '%-%'  
       GROUP BY b.ItemNumber
修改为：
SELECT Item =CASE WHEN a.Source = '3' THEN b.ItemNumber ELSE rtrim(b.ItemNumber) + 'R' END,  
       	      RefurbishIn = CASE WHEN a.Source = '3' THEN 0 ELSE SUM(b.Quantity) END,  
      	      RefurbishOut = CASE WHEN a.Source = '3' THEN SUM(b.Quantity) ELSE 0 END,
 	      RefurbishAdjust = 0  
       FROM [abs].dbo.RefurbishMaster AS a (NOLOCK)  
       INNER JOIN [abs].dbo.RefurbishTransaction AS b (NOLOCK)  
       ON a.RTNumber = b.RTNumber  
       WHERE a.ReceivingDate >= '2007-01-01'--@pThisMonthBeginDate  
       AND a.ReceivingDate < '2007-01-18'--@pOneDayAfterEffectDate  
       AND b.ItemNumber LIKE '%-%'  
       GROUP BY b.ItemNumber, a.Source
经查，调整后结果是一致的；

结果：调整前：CPU time = 846 ms,  elapsed time = 2161 ms.
      调整后：CPU time = 484 ms,  elapsed time = 901 ms.

12.由于使用sp_executesql函数时，字符型的参数变量是nchar或nvarchr的，但由于ZipCode，ShippingAddress是char型，因此查询时是不会使用索引的。因此建议将SQL：
exec sp_executesql N'       
SELECT * 
FROM imk.dbo.viewDetectFrud (nolock) 
WHERE ZipCode like rtrim(@ZipCode) + ''%'' 
and (ShippingAddress like rtrim(@FirstAddress) + ''%'' 
or ShippingAddress like rtrim(@SecondAddress) + ''%'')      ',
N'@ZIPCODE nvarchar(10),@FIRSTADDRESS nvarchar(80),@SECONDADDRESS nvarchar(80)', 
@ZIPCODE = N'97070', @FIRSTADDRESS = N'287', @SECONDADDRESS = N''
修改为：
declare @ZIPCODE varchar(10),@FIRSTADDRESS varchar(80),@SECONDADDRESS varchar(80)
set @ZIPCODE = '97070'
set @FIRSTADDRESS = '287'
set @SECONDADDRESS = ''
--exec sp_executesql N'  
exec (' 
SELECT * 
FROM imk.dbo.viewDetectFrud (nolock) 
WHERE ZipCode like ''' + @ZipCode + '%''
and (ShippingAddress like ''' + @FirstAddress + '%'' 
or ShippingAddress like '''+ @SecondAddress + '%'')')

结果：修改前：CPU time = 15781 ms,  elapsed time = 6395 ms.
      修改后：CPU time = 13 ms,  elapsed time = 13 ms.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372

use imk --查找所有存储过程在imk中 == ctrl+4
go
sp_stored_procedures  @sp_owner='dbo'

use imk --查找imk下的tomb相关的存储
go 
select * from  dbo.sysobjects with (nolock)where name like '%tmob%' and xtype='P'

use master --再master 表下寻找tmob存储
go
imk.dbo.FindSP 'tmob'

use imk
go
TMOB --ctrl+5 显示相关的tmob的存储



 
use sls  --查找sls下查找tmob 相关存储 
go
imk.dbo.FindSP 'tmob'

use sls   --查找sls中查找tmob 没有相关存储 
go 
imk.dbo.FindSP 'imk.tmob'

use imk   --查找imk中查找cps相关存储 
go 
imk.dbo.FindSP 'sls.cps'


ALTER procedure FindSP
 
@sp_name varchar(200)
 
as
--declare @sp_name varchar(256)
--set @sp_name='imk.dbo.tmob'
--set @sp_name='sls.cps'
declare @xml xml 
set @xml=CONVERT(xml,'' + REPLACE(@sp_name, '.', '') + '')
--select @xml
 --select @xml.query('/root/v[1]').value('/','varchar(50)')
-- select @xml.query('/root/v[4]').value('/','varchar(50)')
declare @sql varchar(200)
declare @db varchar(20)
declare @sp varchar(20)
--取数据库
set @db=@xml.query('/root/v[1]').value('/','varchar(50)')
--存储名称
set @sp=@xml.query('/root/v[2]').value('/','varchar(50)')
if(@sp='')
    begin 
      set @sp=@db  
          set @sql=N'select * from  dbo.sysobjects with (nolock)where name like ''%'+@sp+'%'' and xtype=''P''' 
   print @sql
        exec (@sql)    
    end 
else 
    begin 
     
        set @sql=N'select * from '+@db+'.dbo.sysobjects with (nolock) where name like ''%'+@sp+'%'' and xtype=''P''' 
    print @sql
        exec (@sql) 
    end


 


use imk  --找一条记录插入临时表 ，然后导出sql 
go
 
select  top 1* into #Somaster  from sls.dbo.Newegg_SoMaster with(nolock) 
 
spGenInsertSQL '#Somaster'
--ctrl+6
INSERT INTO [Somaster] ([SONumber],[CustomerNumber],[SODate],[SOAmount],[CustomerPONumber],[Status],[Type],[ShipViaCode],[PayTermsCode],[Payterms],[RMANumber],[InvoiceNumber],[SOMemo],[HoldMark],[HoldDate],[HoldUser],[HoldReason],[SalesPostUser],[SalesPostDate],[AcctPostUser],[AcctPostDate],[WarehousePostUser],[WarehousePostdate],[ShippingCompanyName],[ShippingContactWith],[ShippingAddress1],[ShippingAddress2],[ShippingCity],[ShippingState],[ShippingZipCode],[ShippingCountry],[ShippingPhone],[ShippingFax],[CreditCard1],[ExpireDate1],[CreditCard2],[ExpireDate2],[ShippingCharge],[TaxAmount],[SalesPerson],[LastEditUser],[LastEditDate],[OpenReason],[OnlineApproveDate],[DisapproveReason],[CreditCardVerifyMark],[OnLinePrintMark],[CreditCardCharged],[PrintCounter],[storeSign],[SplitFlag],[SoReason],[OriginalInvoiceNumber],[rushOrderFee],[bBillingCompanyName],[bBillingContactWith],[bBillingAddress1],[bBillingAddress2],[bBillingCity],[bBillingState],[bBillingZipcode],[bBillingCountry],[bBillingPhone],[bBillingFax],[IPAddress],[RiskScore],[BankPhone],[CVV2],[AVS],[Version],[VerifyUser],[VerifyDate],[TaxRate],[AddressVerifyMark],[CompanyCode],[ReferenceSONumber],[CustomerOwnShippingAccount],[ExtraServiceCode],[RevenueOwnerCompanyCode],[SAPImportRequired],[SAPImpotDate],[InvoiceRequired],[CurrencyCode],[CurrencyExchangeRate],[Duties],[LanguageID],[PartialShippmentEnabled],[ReferencePONumber],[DispatchDate],[DispatchBatchNumber],[SpecialComment],[CurrencySOAmount],[CurrencyRefundAmount],[CurrencyTaxAmount],[CurrencyShippingCharge]) values (13402925,3872142,'Aug 20 2005 12:00AM',0.01,'               ','V',' ','004 ','001 ','              ',NULL,NULL,NULL,0,NULL,'    ',NULL,'EGG ','Jun  6 2005  1:56AM','    ','Aug 20 2005 12:00AM','    ',NULL,'                                                  ','Andy Lau                      ','14166 w 138th ct                        ','                                        ','olathe              ','KS','66062     ','USA','913-829-9183        ','               ','4007000000027       ','01/06','8006177480          ','111  ',0.99,0.00,'EGG ','                         ','Jun  6 2005  1:55AM','001','Jun  6 2005  1:56AM','','B',0,'0',0,'Y','07','N ',NULL,0.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,NULL,10.00,0.00)
--INSERT INTO sls.dbo.Newegg_SoMaster ([SONumber],[CustomerNumber],[SODate],[SOAmount],[CustomerPONumber],[Status],[Type],[ShipViaCode],[PayTermsCode],[Payterms],[RMANumber],[InvoiceNumber],[SOMemo],[HoldMark],[HoldDate],[HoldUser],[HoldReason],[SalesPostUser],[SalesPostDate],[AcctPostUser],[AcctPostDate],[WarehousePostUser],[WarehousePostdate],[ShippingCompanyName],[ShippingContactWith],[ShippingAddress1],[ShippingAddress2],[ShippingCity],[ShippingState],[ShippingZipCode],[ShippingCountry],[ShippingPhone],[ShippingFax],[CreditCard1],[ExpireDate1],[CreditCard2],[ExpireDate2],[ShippingCharge],[TaxAmount],[SalesPerson],[LastEditUser],[LastEditDate],[OpenReason],[OnlineApproveDate],[DisapproveReason],[CreditCardVerifyMark],[OnLinePrintMark],[CreditCardCharged],[PrintCounter],[storeSign],[SplitFlag],[SoReason],[OriginalInvoiceNumber],[rushOrderFee],[bBillingCompanyName],[bBillingContactWith],[bBillingAddress1],[bBillingAddress2],[bBillingCity],[bBillingState],[bBillingZipcode],[bBillingCountry],[bBillingPhone],[bBillingFax],[IPAddress],[RiskScore],[BankPhone],[CVV2],[AVS],[Version],[VerifyUser],[VerifyDate],[TaxRate],[AddressVerifyMark],[CompanyCode],[ReferenceSONumber],[CustomerOwnShippingAccount],[ExtraServiceCode],[RevenueOwnerCompanyCode],[SAPImportRequired],[SAPImpotDate],[InvoiceRequired],[CurrencyCode],[CurrencyExchangeRate],[Duties],[LanguageID],[PartialShippmentEnabled],[ReferencePONumber],[DispatchDate],[DispatchBatchNumber],[SpecialComment],[CurrencySOAmount],[CurrencyRefundAmount],[CurrencyTaxAmount],[CurrencyShippingCharge]) values (13402925,3872142,'Aug 20 2005 12:00AM',0.01,'               ','V',' ','004 ','001 ','              ',NULL,NULL,NULL,0,NULL,'    ',NULL,'EGG ','Jun  6 2005  1:56AM','    ','Aug 20 2005 12:00AM','    ',NULL,'                                                  ','Andy Lau                      ','14166 w 138th ct                        ','                                        ','olathe              ','KS','66062     ','USA','913-829-9183        ','               ','4007000000027       ','01/06','8006177480          ','111  ',0.99,0.00,'EGG ','                         ','Jun  6 2005  1:55AM','001','Jun  6 2005  1:56AM','','B',0,'0',0,'Y','07','N ',NULL,0.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,NULL,10.00,0.00)
drop table #Somaster --用完了可以drop 掉

 
 
use test 

--alter procedure FindSP
-- 
--@sp_name varchar(200)
-- 
--as
--declare @sql varchar(200)
--set @sql=N'select * from sysobjects where name like ''%'+@sp_name+'%'' and xtype=''P''' 
--exec (@sql)
--
--FindSP @sp_name='Up1'
-------------拆分列-----------------------------------
if exists(select top 1 1 from sysobjects where name='tb' )
begin 
 drop table   tb
end 
Create table   tb
(
id int ,
[values] varchar(100)
)
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
--xml 拆分列 新老对比
有表tb, 如下:
id          values
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按,分拆values列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc

--1. 旧的解决方法
    SELECT TOP 8000 
        id = IDENTITY(int, 1, 1) 
    INTO # 
    FROM syscolumns a, syscolumns b
    SELECT 
        A.id, 
        SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
    FROM tb A, # B
    WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','
    DROP TABLE #
-- 2. 新的解决方法 
-- 示例数据
    DECLARE @t TABLE(id int, [values] varchar(100))
    INSERT @t SELECT 1, 'aa,bb'
    UNION ALL SELECT 2, 'aaa,bbb,ccc'
    -- 查询处理
    SELECT 
        A.id, B.value
    FROM(
        SELECT id, [values] = CONVERT(xml,
                '' + REPLACE([values], ',', '') + '')
        FROM @t
    )A
    --outer tb外表存在的都显示
    OUTER APPLY(
        SELECT value = N.v.value('.', 'varchar(100)')
        FROM A.[values].nodes('/root/v') N(v) ---按照 values 这个列去拆
    )B
/*--结果
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc

(5 行受影响)
--*/
------------------------------------------和并列------------------
if exists(select top 1 1 from sysobjects where name='tb' )
begin 
 drop table   tb
end 
Create table   tb
(
id int ,
[values] varchar(100)
)

insert into tb values(1,'aa')
insert into tb values(1,'bb')
insert into tb values(2,'aaa')
insert into tb values(2,'bbb')
insert into tb values(2,'ccc')

 

问题描述：

无论是在sql 2000， 还是在 sql 2005 中，都没有提供字符串的聚合函数， 所以， 当我们在处理下列要求时，会比较麻烦：
有表tb, 如下：
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc

需要得到结果：
id     values
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即， group by id, 求 value 的和（字符串相加）

1. 旧的解决方法
-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + [values]
    FROM tb
    WHERE id=@id
    RETURN STUFF(@r, 1, 1, '')

END

GO
-- 调用函数
SELECt id, [values]=dbo.f_str(id) 
FROM tb 
GROUP BY id
-- 2. 新的解决方法 

-- 示例数据

DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'
-- 查询处理

SELECT *
FROM(
    SELECT DISTINCT 
        id

    FROM @t

)A
OUTER APPLY(
    SELECT 
        [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM @t N
                WHERE id = A.id
                FOR XML AUTO
            ), ', ','), '"/>', ''), 1, 1, '')
)N
 
/*--结果
id          values
----------- ----------------
1           aa,bb
2           aaa,bbb,ccc
(2 行受影响)
--*/

 


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264