指定序的排序问题，记一个学生的问题
主要内容
- 问题的分析与划归
- 排序算法映射，算法解析
- 不考虑重复字符串的实现代码
最近在传智论坛遇到一个算法的问题，想了一下，有一个我认为比较有趣的解法. 下面算 法或许不是最优的，但是可以参考一下.
问题：
=======================================================================
给定两个字符串，仅由小写字母组成，它们包含了相同字符。求把第一个字符串变
成第二个字符串的最小操作次数，且每次操作只能对第一个字符串中的某个字符移
动到此字符串中的开头。
例如给定两个字符串“abcd" "bcad" ，输出：2，因为需要操作2次才能把"abcd"变
成“bcad" ，方法是：abcd->cabd->bcad。
我开始用广度优先搜索做。可是比如abcdefg 和 gfedcba 递归下去就是7的7次方的
时间复杂度 然后再在变换位置的函数里循环 循环次数报表。
然后我用List记录 abcdefg出现的东东 把重复的去掉了，也就是7*6*5*4*3*2
但是每次判断list.contain()在内部貌似又要循环时间和上面差不多.
有没有比较好的解决方式.
========================================================================
这个问题比较有趣，这里我使用C#来完成，主要是考虑微软提供了许多数据结构 不用再重新实现数据结构和相关方法了.
问题分析
开始拿到这个问题，先分析一下需要做的事儿：就是将一个字符串，变成一个指 定的字符串. 抽象一下，将目标字符串看成一个有序集的结构，可以重新一个有序集.
再将原来的字符串看成一个等待排序的数据结构，就可以将问题简化为简单的排序算
法了. 即：指定序的排序问题.
举个例子：将字符串"abcd"变成"bcad". 那么字符集"bcad"构成一个有序集，可以得到映射表，如下：
{ ('b', 0), ('c', 1), ('a', 2), ('d', 3) } 因此需要排序的字符串就变成了一个数字集：{ 2, 0, 1, 3 }. 即 需要将这个数据集排序为 { 0, 1, 2, 3 }，需要求最小的步骤. 因
此将问题转化为排序最小步骤问题.
另一方面，问题考虑了算法实现的要求，即每次只能将一个数据 取出，插入到开始的地方. 也就是说具体操作就是一个固定的函数：
将第i项插入到0项，原来前i-1项的数据依次后移一位. 故方法很简单:
1 // 注意到需要处理的是字符串，由于字符串是不可变的 2 // 这里考虑使用字符数组 3 public void jk_remove(char[] s, int index) { 4 char temp = s[index]; 5 for (int i = index - 1; i >= 0; i--) { 6 s[i + 1] = s[i]; 7 } 8 s[0] = temp; 9 }
那么这个函数调用几次，就表示操作了几次.
下面考虑另一个问题. 就是什么时候调用这个函数. 也就是如何操作才算是较优. 这个问题有点麻烦. 起初我打算从现成的算法中找一个. 不过现成的都不太友好. 想到
了使用"逆序"的方法.
所谓最少的步骤. 就是不要移动多余的步骤. 在前面已经提到，问题已经划归成排 序问题，下面姑且使用升序.
要将一个数字序列排序，又只能将数据往前放，那么最小的步骤就是每次移动一个 数据并且这个数据就是一个良序的，也就是说这个数字不会再次移动. 那么一个长度为
n的字符串，最多移动n-1次. 我想这个应该是最少的步骤了吧！！！
下面看看怎么移动会比较好.
首先考虑移动的特征就是每次移动数据都放在最开始的地方. 同时下一次移动开始 后这个已经被移动过的数据就会自动挤到后面去. 那么不再重复移动这个数据的办法就
是首先移动最大的数. 那么在移动剩下的最大数，就可以保证移动完成以后结果就是良
序的了.
那么什么样的数据需要移动呢？很简单逆序(注1)的. 举个例子：
延续前面的例子，数字2013. 第一个数字不需要考虑，第二个数字0与2构成一个逆 序，因此0是逆序数. 第三个数字1与2构成逆序，即1为逆序数. 那么首先移动较大的，
移动1得到1203. 由于数据排列发生变化，需要重新计算逆序. 因此得到0为逆序数，移
动0，得到结果0123.
这里就得到一个结论，就是判断逆序，移动最大的逆序数即可. 由此得到伪代码：
1 // 这里依旧使用升序排列 2 public void jk_sort(char[] s) { 3 char temp = ' '; // 用来存放最大的逆序数 4 // 由于第一个数字不需要考虑逆序，因此从1开始循环 5 for(int i = 1; i < s.length; i++) { 6 for(int j = 0; j < i; j++) { 7 // 后一个数比前一个数小，那么构成逆序 8 if(s[i] < s[j]) { 9 // 判断记录最大的逆序数 10 if(temp == ' ' || temp < s[i]) { 11 temp = s[i]; 12 } 13 // 跳出循环，看下一个数是否为逆序 14 break; 15 } 16 } 17 } 18 // 移动逆序数 19 if(temp != ' ') { 20 jk_remove(s, temp); 21 // 如果存在逆序数就递归继续判断，否则离开函数排序完成 22 jk_sort( s ); 23 } 24 }
这里使用了递归，算法复杂度提升了，具体的优化可以以后再说. 那么有了这个思
路以后，就可以实现了.
代码实现
第一种情况，字符串中没有重复的字符
首先写一个类
1 public class JKSort { 2 3 }
提供静态方法，移动数据
1 private static void jk_remove(char[] s, int index) { 2 char temp = s[index]; 3 for (int i = index - 1; i >= 0; i--) { 4 s[i + 1] = s[i]; 5 } 6 s[0] = temp; 7 }
提供映射，可以考虑使用一个键值对
1 private Dictionary<char, int> dic; // 使用构造函数初始化 2 private JKSort(char[] chs) { 3 dic = new Dictionary<char, int>(); 4 for(int i = 0; i < chs.Length; i++) { 5 dic.Add(chs[i], i); 6 } 7 }
提供方法实现排序
1 private static void internal_jk_sort(char[] s, Dictionary<char, int> dic) { 2 int temp = -1; // 记录最大逆序数的索引 3 4 for(int i = 1; i < s.Length; i++) { 5 for(int j = 0; j < i; j++) { 6 7 if(dic[s[i]] < dic[s[j]]) { 8 9 if(temp == -1 || dic[s[temp]] < dic[s[i]]) { 10 temp = i; 11 } 12 13 break; 14 } 15 } 16 } 17 // 移动逆序数 18 if(temp != -1) { 19 jk_remove(s, temp); 20 // 如果存在逆序数就递归继续判断，否则离开函数排序完成 21 internal_jk_sort( s, dic ); 22 } 23 }
实现对外公开的方法
1 public static string JK_Sort(string str, string strObj) { 2 char[] chs = str.ToCharArray(); 3 char[] obj = strObj.ToCharArray(); 4 5 JKSort j = new JKSort(obj); 6 7 internal_jk_sort(chs, j.dic); 8 9 10 return new string(chs); 11 }
那么就完成了排序. 这里提供的算法没有实现优化，如想了解优化与带有重复字符
串的解决办法，请等待下文.
睡觉，2013年12月18日凌晨0时.
注1
逆序：在一个数字排列中，如果其中的两个数前面的一个比后面的一个大，那么就称
它们构成一个逆序. 相关逆序的结论与问题，可以参考《高等代数》或《线性代数》
的教材.
【译】什么情况下应该分解复杂的查询来提升性能
简介
Microsoft SQL Server在大多数情况下都能够创建非常高效的执行计划。但是在某些特定的查询类型会给查询优化器带来麻烦。本篇文章阐述了其中4种情况，这些情况通常包含了需要SQL Server传递多个数据集、产生没有统计信息的中间结果集，抑或是中间结果集的统计信息无法被正确计算。
将单独的查询语句分解为多个语句或是多个步骤，来使得SQl Server生成完全不同的执行计划，或是为中间结果集创建统计信息。分解语句，而不是使用提示的好处是使得SQL Server可以根据数据的变化，动态的生成高效的执行计划。
虽然本篇文章所探讨的查询类型大多是基于ETL或是报表，但同样也可以在其他类型的查询中找到。
本篇文章主要阐述了下述4中查询模式：
Where字句中包含了OR逻辑
在这种情况下，在OR或Join操作符两端的条件涉及了不同的表。该类语句可以通过使用UNION操作符来替代WHERE或ON子句中的OR操作符。
中间解决集存在聚合
该类查询是与聚合后的数据集进行连接，这往往会导致低效的查询性能。该类问题可以通过将聚合后的查询结果存入临时表来解决。
大量复杂的Join
该类查询模式包含了大量连接，尤其是连接条件是不等连接，由于统计信息随着表连接的增多精度逐渐下降，这会导致低效的查询性能。解决这类情况可以通过分解查询，并将中间解决存入临时表解决。
在Where或Join中包含了Case子句
该类查询在WHERE或JOIN子句中包含了CASE子句，这会导致难以有效的利用统计信息，从而造成查询性能低效。解决该类问题可以使用T-SQL的IF语句来替代CASE子句将语句执行流程提炼出来。
理解上述4种查询类型的概念可以更好的帮助解决其他类似情况导致的低效查询。因此可以重写查询来获得更高效稳定的查询性能。
下列使用OR子句的例子不会引起性能问题.
WHERE a.col1 = @val1 OR a.col1 = @val2 …
这种查询模式仅仅是WHERE COL1 IN (@val1，@val2)的另一种写法。使用这种模式不会产生低效的查询计划。但是，值得注意的是WHERE子句中的每一个值都会使得SQL Server从B-Tree的根节点找到叶子节点。
该查询模式的关键是OR操作符两边的列都会被计算出值。在上述例子中，该列是col1。如果col1上存在索引，则在该索引上执行查找操作就可以满足所有条件。如果索引足够小，甚至可能出现索引扫描。同样的指导原则适用于覆盖索引和其他索引。
WHERE a.col1 = @val1 OR a.col2 = @val2 …
在这种查询模式中，同一个表的两列中的值需要被估算出来，如果在这两列上都存在索引，其中一个索引的第一列是COL1，另一个索引的第一列是COL2，如果数据量大，则SQL Server使用索引来获取数据（如图1所示）方式为UNION两个索引查找的值，而如果数据量比较小，则SQL Server会使用扫描。
图1.
WHERE a.col1 = @val1 OR a.col2 IN (SELECT col2 FROM tab2)
通过分析该模式可以看出，SQL Server重写了IN子句，将IN变为和表2进行了连接，并使用了不同的索引来获取满足条件的行。并且在最后对结果集进行排序来消除重复行（如图2）。这同样也是高效的执行计划，对该类语句应用本文所探讨的技术并不会产生更好的执行计划。
图2.
会引起问题的OR用法
在上述例子中，OR操作符两边的条件都是在同一个表之内，因此，执行计划不会出现问题。但如果，OR两端的操作符引用了不同的表时，则会出现问题。
WHERE a.col1 = @val1 OR b.col2 = @val2
上述查询所代表的查询模式是会导致问题的。将该查询分解为两步可以生成成本更小的执行计划。请注意在该查询模式中涉及到了两个表。在OR操作符两端的两个表的条件中涉及到不同的两个表。
注意：为了简便起见，例子中仅仅使用了等号作为条件。然而，即使使用了不等式或诸如BETWEEN的变量作为条件也可以出现相同的情况。
下述是附件1中所描述的会导致问题的示例查询。
DECLARE @minEmp INT
DECLARE @maxEmp INT
SET @minEmp= 100
SET @maxEmp= 200
SELECT e.* FROM HumanResources.Employee e
LEFT JOIN Adventureworks.Person.Contact c ON e.EmployeeID=c.ContactID
WHERE e.EmployeeID BETWEEN @minEmp and @maxEmp
OR c.EmailAddress IN('sabria0@adventure-works.com','teresa0@adventure-works.com','shaun0@adventure-works.com')
代码清单1.会导致问题的示例查询
现在可以看到OR两端的条件涉及到了HumanResources.Employee以及Person.Contact表。现在我们来看在 Person.Contacts上存在的索引。
表1.AdventureWork
| 索引名称 | 索引描述 | 索引键 |
| AK_Contact_rowguid | 非聚集，唯一 | rowguid |
| IX_Contact_EmailAddress | 非聚集 | EmailAddress |
| PK_Contact_ContactID | 非聚集，唯一，主键 | ContactID |
在Person.Contact(EmailAddress)上存在的索引可以被用于获取查询条件中满足邮件地址的行。在Employee表上的主键和聚集索引是EmployeeID。按理来说SQL Server应该从两个表上的索引通过低成本的索引查找获得。然而，执行查询所获得的执行计划却显示了对两个索引使用了索引扫描，如图4所示。
图4.
此时如果将STATISTIC IO和STATISTIC TIME启用的话，结果如下：
Table 'Contact'. Scan count 1, logical reads 569, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Employee'. Scan count 1, logical reads 6963, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
代码清单2.
结果显示了总共7532个逻辑读以及94ms的CPU时间。该计划或许在小系统且执行不频繁时不会引起注意，然而，如果表中数据增长到一定数量，且查询的复杂度增长时，该类查询将会成为将系统资源损耗殆尽的罪魁祸首。
为了降低查询成本，可以将该类查询分为更容易让SQL Server解释的多个小查询。您可以重写查询，将Where子句中的条件进行分解，使得单个查询条件只涉及一个表。
对于如例子所示的示例，可以很容易看出两个表进行UNION操作可以替代上述出问题的查询。如下面代码所示：
DECLARE @minEmp INT
DECLARE @maxEmp INT
SET @minEmp = 100
SET @maxEmp = 200
SELECT e.*FROM HumanResources.Employee e
LEFT JOIN Adventureworks.Person.Contact c ON e.EmployeeID = c.ContactID
WHERE EmployeeID BETWEEN @minEmp and @maxEmp
UNION
SELECT e.*FROM HumanResources.Employee e
LEFT JOIN Adventureworks.Person.Contact c ON e.EmployeeID = c.ContactID
WHERE
c.EmailAddress in('sabria0@adventure-works.com','teresa0@adventure-works.com','shaun0@adventure-works.com')
代码清单3.
虽然功能上代码清单3和代码清单1一致，但SQL Server对于这两种查询语句的处理方式则完全不同。在该查询计划中，使用UNION来代替OR。使用UNION之后，SQL Server可以通过索引扫描查找，而不再是索引扫描，因此降低了成本，如图5所示。
图5.
结果如代码清单4：
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Employee'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Contact'. Scan count 3, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
代码清单4.
因此可以很容易看出，逻辑读下降到26，CPU时间低于1MS。
查询反模式2：由聚合产生的中间结果集
诸如GROUP BY或DISTINCT之类的关键字会产生与原表行数不同的中间结果集。如果这些中间结果集还与其他表或数据集进行连接，则中间结果集上不会存在统计信息。因此SQL Server只能通过原数据集的统计信息估算基数，但是这种方式的精度随着数据集的增多和查询复杂度的增加变得越来越不尽人意。任何不准确的基数预估都可能导致低效的查询计划。
如果不准确的基数预估是源于过期的统计信息，则更新表或索引的统计信息就能够产生更高效的查询计划。但是，如果查询语句需要生成中间结果集以供后续步骤使用的话，这种中间结果集上不会存在统计信息，虽然可以从原数据集中获取统计信息，但这种方式很难生成高效的执行计划。本文的附件C和附件D的例子就是这类查询无法使用统计信息而导致的低效执行计划。在生产系统中，该类查询往往出现在ETL或报表作业中。值得注意的是如需做出该类DEMO则需要足够量的数据。代码清单5包含了两个可以作为DEMO来演示该情况和解决方案的查询。
下述步骤可以帮您准备好演示。
1.执行下述代码
/*
Do this all in tempdb to get the auto-cleanup after restart if all is forgotten
*/
USE tempdb
GO
SET NOCOUNT ON
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO
/*
Cleanup so multiple runs can be done if necessary
*/
IF OBJECT_ID('dbo.Customers') IS NOT NULL
DROP TABLE dbo.Customers
IF OBJECT_ID('dbo.InternetOrders') IS NOT NULL
DROP TABLE dbo.InternetOrders
IF OBJECT_ID('dbo.StoreOrders') IS NOT NULL
DROP TABLE dbo.StoreOrders
IF OBJECT_ID('dbo.InternetQuotes') IS NOT NULL
DROP TABLE dbo.InternetQuotes
IF OBJECT_ID('dbo.StoreQuotes') IS NOT NULL
DROP TABLE dbo.StoreQuotes
IF OBJECT_ID('dbo.SurveyResults') IS NOT NULL
DROP TABLE dbo.SurveyResults
IF OBJECT_ID('dbo.SurveyDetails') IS NOT NULL
DROP TABLE dbo.SurveyDetails
IF OBJECT_ID('dbo.TransactionType3') IS NOT NULL
DROP TABLE dbo.TransactionType3
IF OBJECT_ID('dbo.TransactionType4') IS NOT NULL
DROP TABLE dbo.TransactionType4
IF OBJECT_ID('dbo.TransactionType5') IS NOT NULL
DROP TABLE dbo.TransactionType5
IF OBJECT_ID('dbo.TransactionType6') IS NOT NULL
DROP TABLE dbo.TransactionType6
/*
create tables for customers, internet orders, and store orders
*/
CREATE TABLE dbo.Customers
(
customerID INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, customerName VARCHAR(30)NOT NULL
, otherStuff NCHAR(100) NULL
)
GO
CREATE TABLE dbo.InternetOrders
(
customerID INT NOT NULL
, orderID INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, orderTotal MONEY NOT NULL
, orderDate DATETIME NOT NULL
, otherDetails NCHAR(100) NULL
)
CREATE INDEX InternetOrders_customerID on InternetOrders(customerID) INCLUDE(orderTotal)
CREATE INDEX InternetOrders_OrderDate ON dbo.InternetOrders(orderDate) INCLUDE(CustomerID, orderTotal)
GO
CREATE TABLE storeOrders
(
customerID INT NOT NULL
, storeOrderID INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, orderTotal MONEY NOT NULL
, orderDate DATETIME NOT NULL
, otherDetails NCHAR(100) NULL
)
CREATE INDEX storeOrders_customerID ON storeOrders(customerID) INCLUDE(orderTotal)
CREATE INDEX StoreOrders_OrderDate ON dbo.StoreOrders(orderDate) INCLUDE(CustomerID, orderTotal)
GO
CREATE TABLE dbo.InternetQuotes
(
customerID INT NOT NULL
, quoteID INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, quoteTotal MONEY NOT NULL
, quoteDate DATETIME NOT NULL
, otherDetails NCHAR(100) NULL
)
CREATE INDEX InternetQuotes_customerID on InternetQuotes(customerID) INCLUDE(quoteTotal)
CREATE INDEX Internetquotes_OrderDate ON dbo.InternetQuotes(quoteDate) INCLUDE(CustomerID, quoteTotal)
GO
CREATE TABLE dbo.StoreQuotes
(
customerID INT NOT NULL
, storeQuoteID INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, quoteTotal MONEY NOT NULL
, quoteDate DATETIME NOT NULL
, otherDetails NCHAR(100) NULL
)
CREATE INDEX StoreQuotes_customerID on StoreQuotes(customerID) INCLUDE(quoteTotal)
CREATE INDEX StoreQuotes_OrderDate ON dbo.StoreQuotes(quoteDate) INCLUDE(CustomerID, quoteTotal)
GO
CREATE TABLE dbo.TransactionType3
(
customerID INT NOT NULL
, orderID INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, orderTotal MONEY NOT NULL
, orderDate DATETIME NOT NULL
, otherDetails NCHAR(100) NULL
)
CREATE INDEX TransactionType3_customerID on dbo.TransactionType3(customerID) INCLUDE(orderTotal)
CREATE INDEX TransactionType3_OrderDate ON dbo.TransactionType3(orderDate) INCLUDE(CustomerID, orderTotal)
GO
CREATE TABLE TransactionType4
(
customerID INT NOT NULL
, storeOrderID INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, orderTotal MONEY NOT NULL
, orderDate DATETIME NOT NULL
, otherDetails NCHAR(100) NULL
)
CREATE INDEX TransactionType4_customerID ON dbo.TransactionType4(customerID) INCLUDE(orderTotal)
CREATE INDEX TransactionType4_OrderDate ON dbo.TransactionType4(orderDate) INCLUDE(CustomerID, orderTotal)
GO
CREATE TABLE dbo.TransactionType5
(
customerID INT NOT NULL
, orderID INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, orderTotal MONEY NOT NULL
, orderDate DATETIME NOT NULL
, otherDetails NCHAR(100) NULL
)
CREATE INDEX TransactionType5_customerID on dbo.TransactionType5(customerID) INCLUDE(orderTotal)
CREATE INDEX TransactionType5_OrderDate ON dbo.TransactionType5(orderDate) INCLUDE(CustomerID, orderTotal)
GO
CREATE TABLE TransactionType6
(
customerID INT NOT NULL
, storeOrderID INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, orderTotal MONEY NOT NULL
, orderDate DATETIME NOT NULL
, otherDetails NCHAR(100) NULL
)
CREATE INDEX TransactionType6_customerID ON dbo.TransactionType6(customerID) INCLUDE(orderTotal)
CREATE INDEX TransactionType6_OrderDate ON dbo.TransactionType6(orderDate) INCLUDE(CustomerID, orderTotal)
GO
CREATE TABLE dbo.SurveyResults
(
contactID INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, customerID INT NULL
, partnerID INT NULL
, aggResults TINYINT NOT NULL
, otherDetails NCHAR(100) NULL
)
CREATE INDEX SurveyReults_CustomerID ON dbo.SurveyResults(CustomerID)
GO
CREATE TABLE dbo.SurveyDetails
(
surveyID INT NOT NULL
, questionNbr TINYINT NOT NULL
, customerID INT NOT NULL
, rating TINYINT NOT NULL
, surveyDate DATETIME NOT NULL
, verbatim NCHAR(500) NULL
)
GO
/*
Populate these tables to form the first part of the query
*/
CREATE TABLE #firstNamePart
(
namePart NVARCHAR(14)
)
GO
CREATE TABLE #secondNamePart
(
namePart NVARCHAR(14)
)
INSERT INTO #firstNamePart VALUES (N'Some')
INSERT INTO #firstNamePart VALUES (N'Another')
INSERT INTO #firstNamePart VALUES (N'Different')
INSERT INTO #firstNamePart VALUES (N'Contoso')
INSERT INTO #firstNamePart VALUES (N'Similar')
INSERT INTO #firstNamePart VALUES (N'Dissimilar')
INSERT INTO #firstNamePart VALUES (N'My')
INSERT INTO #firstNamePart VALUES (N'Your')
INSERT INTO #firstNamePart VALUES (N'Their')
INSERT INTO #firstNamePart VALUES (N'Somebody''s')
INSERT INTO #firstNamePart VALUES (N'This')
INSERT INTO #firstNamePart VALUES (N'That')
INSERT INTO #firstNamePart VALUES (N'Varied')
INSERT INTO #secondNamePart VALUES (N'Inc.')
INSERT INTO #secondNamePart VALUES (N'LLC')
INSERT INTO #secondNamePart VALUES (N'Hobby')
INSERT INTO #secondNamePart VALUES (N'Unlimited')
INSERT INTO #secondNamePart VALUES (N'Limited')
INSERT INTO #secondNamePart VALUES (N'Musings')
INSERT INTO #secondNamePart VALUES (N'Manufacturing')
INSERT INTO #secondNamePart VALUES (N'Exploration')
INSERT INTO #secondNamePart VALUES (N'Enterprise')
INSERT INTO #secondNamePart VALUES (N'Services')
INSERT INTO #secondNamePart VALUES (N'Attempts')
INSERT INTO #secondNamePart VALUES (N'Dreams')
INSERT INTO #secondNamePart VALUES (N'Ideas')
-- populate customer
INSERT INTO dbo.Customers(customerName, otherStuff)
SELECT a.namePart +N' '+ b.namePart,N'otherStuff'
FROM #firstNamePart a CROSS JOIN #secondNamePart b
INSERT INTO dbo.Customers(customerName, otherStuff)
SELECT a.namePart +N' '+ b.namePart,N'otherStuff'
FROM #firstNamePart a CROSS JOIN #secondNamePart b
GO
DROP TABLE #firstNamePart
DROP TABLE #secondNamePart
GO
-- populate the internetOrders and storeOrders tables:
DECLARE @customerID INT -- as we go through
DECLARE @orderTotal MONEY
DECLARE @orderDate DATETIME
DECLARE @numRecords SMALLINT
DECLARE @ct SMALLINT
DECLARE crs CURSOR FOR SELECT customerID from dbo.Customers
OPEN crs
FETCH NEXT FROM crs INTO @customerID
WHILE @@FETCH_STATUS= 0
BEGIN
-- internet orders
SET @numRecords =RAND()* 10000
SET @ct = 0
WHILE @ct < @numRecords
BEGIN
SET @orderTotal =RAND()* 10000
SET @orderDate =DATEADD(dd,RAND()* 1500,'2008-01-01 00:00:00.000')
INSERT INTO dbo.InternetOrders(customerID, orderTotal, orderDate, otherDetails)
VALUES (@customerID, @orderTotal, @orderDate,'Other Details')
SET @ct = @ct + 1
END
-- set up store orders
SET @numRecords =RAND()* 1000
SET @ct = 0
WHILE @ct < @numRecords
BEGIN
SET @orderTotal =RAND()* 10000
SET @orderDate =DATEADD(dd,RAND()* 1500,'2008-01-01 00:00:00.000')
INSERT INTO dbo.StoreOrders(customerID, orderTotal, orderDate, otherDetails)
VALUES (@customerID, @orderTotal, @orderDate,'Other Details')
SET @ct = @ct + 1
END
INSERT INTO dbo.SurveyResults(customerID, aggResults, otherDetails)
VALUES (@customerID, @customerID % 5,N'Other Details')
FETCH NEXT FROM crs INTO @customerID
END
CLOSE CRS
DEALLOCATE CRS
/*
Populate the quote tables with sample data by duplicating the sales data
Also populate TransactionType3 and TransactionType4
*/
INSERT INTO dbo.InternetQuotes(customerID, quoteDate, quoteTotal, otherDetails)
SELECT customerID, orderDate, orderTotal, otherDetails
FROM dbo.InternetOrders
INSERT INTO dbo.StoreQuotes(customerID, quoteDate, quoteTotal, otherDetails)
SELECT customerID, orderDate, orderTotal, otherDetails
FROM dbo.storeOrders
INSERT INTO dbo.TransactionType3(customerID, orderDate, orderTotal, otherDetails)
SELECT customerID, orderDate, orderTotal, otherDetails
FROM dbo.InternetOrders
INSERT INTO dbo.TransactionType4(customerID, orderDate, orderTotal, otherDetails)
SELECT customerID, orderDate, orderTotal, otherDetails
FROM dbo.storeOrders
INSERT INTO dbo.TransactionType5(customerID, orderDate, orderTotal, otherDetails)
SELECT customerID, orderDate, orderTotal, otherDetails
FROM dbo.InternetOrders
INSERT INTO dbo.TransactionType6(customerID, orderDate, orderTotal, otherDetails)
SELECT customerID, orderDate, orderTotal, otherDetails
FROM dbo.storeOrders
GO
/*
Populate SurveyDetails with sample data for 50 questions
*/
DECLARE @questionNbr TINYINT
DECLARE @surveyID INT
SET @questionNbr = 1
WHILE @questionNbr < 51
BEGIN
INSERT INTO dbo.SurveyDetails(surveyID, questionNbr, customerID, rating, surveyDate, verbatim)
SELECT 1, @questionNbr, customerID, customerID % 5,'2008-01-01',N'Feedback from the customer'
FROM dbo.Customers
INSERT INTO dbo.SurveyDetails(surveyID, questionNbr, customerID, rating, surveyDate, verbatim)
SELECT 2, @questionNbr, customerID, customerID % 5,'2008-01-01',N'Feedback from the customer'
FROM dbo.Customers
SET @questionNbr = @questionNbr + 1
END
GO
/*
Update all statistics to be sure they are all in the best possible shape
*/
UPDATESTATISTICS dbo.Customers WITHFULLSCAN
UPDATESTATISTICS dbo.InternetOrders WITHFULLSCAN
UPDATESTATISTICS dbo.storeOrders WITHFULLSCAN
UPDATESTATISTICS dbo.InternetQuotes WITHFULLSCAN
UPDATESTATISTICS dbo.StoreQuotes WITHFULLSCAN
UPDATESTATISTICS dbo.TransactionType3 WITHFULLSCAN
UPDATESTATISTICS dbo.TransactionType4 WITHFULLSCAN
UPDATESTATISTICS dbo.TransactionType5 WITHFULLSCAN
UPDATESTATISTICS dbo.TransactionType6 WITHFULLSCAN
UPDATESTATISTICS dbo.SurveyResults WITHFULLSCAN
代码清单5.
2.分别在两个窗口执行代码清单6和代码清单7
set statistics io on
set statistics time on
SELECT T1.customerName, R.ContactID, R.AggResults
, D.surveyId, D.questionNbr, D.rating, D.verbatim
FROM
(
SELECT Tab1.customerID, Tab1.customerName, Tab1.StoreOrderTotal, Tab1.InternetOrderTotal
, Tab1.TotalOrders, Tab2.InternetQuoteTotal, Tab2.StoreQuoteTotal, Tab2.TotalQuote
FROM
(
SELECT A.customerID, a.customerName, a.orderTotal as InternetOrderTotal, b.orderTotal as StoreOrderTotal,
TotalOrders = a.orderTotal + b.orderTotal
FROM
(
SELECT c.customerID, c.customerName,
SUM(i.orderTotal)as orderTotal
FROM dbo.Customers c JOIN dbo.InternetOrders i ON c.customerID = i.customerID
WHERE i.orderDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(i.orderTotal)> 10000.00
) A
JOIN
(
SELECT c.customerID, c.customerName,
SUM(s.orderTotal)as orderTotal
FROM dbo.Customers c JOIN dbo.StoreOrders s ON c.customerID = s.customerID
WHERE s.orderDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(s.orderTotal)> 10000.00
) B on A.customerID = B.customerID
WHERE a.orderTotal + b.orderTotal > 100000.00
) Tab1 JOIN
(
SELECT A.customerID, a.customerName, a.quoteTotal as InternetQuoteTotal, b.quoteTotal as StoreQuoteTotal,
TotalQuote = a.quoteTotal + b.quoteTotal
FROM
(
SELECT c.customerID, c.customerName,
SUM(i.quoteTotal)as quoteTotal
FROM dbo.Customers c JOIN dbo.InternetQuotes i ON c.customerID = i.customerID
WHERE i.quoteDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(i.quoteTotal)> 10000.00
) A
JOIN
(
SELECT c.customerID, c.customerName,
SUM(s.quoteTotal)as quoteTotal
FROM dbo.Customers c JOIN dbo.StoreQuotes s ON c.customerID = s.customerID
WHERE s.quoteDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(s.quoteTotal)> 10000.00
) B on A.customerID = B.customerID
WHERE a.quoteTotal + b.quoteTotal > 100000.00
) Tab2 ON Tab1.customerID = Tab2.customerID
) T1 JOIN
(
SELECT Tab1.customerID, Tab1.customerName, Tab1.StoreOrderTotal, Tab1.InternetOrderTotal
, Tab1.TotalOrders, Tab2.InternetQuoteTotal, Tab2.StoreQuoteTotal, Tab2.TotalQuote
FROM
(
SELECT A.customerID, a.customerName, a.orderTotal as InternetOrderTotal, b.orderTotal as StoreOrderTotal,
TotalOrders = a.orderTotal + b.orderTotal
FROM
(
SELECT c.customerID, c.customerName,
SUM(i.orderTotal)as orderTotal
FROM dbo.Customers c JOIN dbo.TransactionType3 i ON c.customerID = i.customerID
WHERE i.orderDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(i.orderTotal)> 10000.00
) A
JOIN
(
SELECT c.customerID, c.customerName,
SUM(s.orderTotal)as orderTotal
FROM dbo.Customers c JOIN dbo.TransactionType4 s ON c.customerID = s.customerID
WHERE s.orderDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(s.orderTotal)> 10000.00
) B on A.customerID = B.customerID
WHERE a.orderTotal + b.orderTotal > 100000.00
) Tab1 JOIN
(
SELECT A.customerID, a.customerName, a.quoteTotal as InternetQuoteTotal, b.quoteTotal as StoreQuoteTotal,
TotalQuote = a.quoteTotal + b.quoteTotal
FROM
(
SELECT c.customerID, c.customerName,
SUM(i.orderTotal)as quoteTotal
FROM dbo.Customers c JOIN dbo.TransactionType5 i ON c.customerID = i.customerID
WHERE i.orderDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(i.orderTotal)> 10000.00
) A
JOIN
(
SELECT c.customerID, c.customerName,
SUM(s.orderTotal)as quoteTotal
FROM dbo.Customers c JOIN dbo.TransactionType6 s ON c.customerID = s.customerID
WHERE s.orderDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(s.orderTotal)> 10000.00
) B on A.customerID = B.customerID
WHERE a.quoteTotal + b.quoteTotal > 100000.00
) Tab2 ON Tab1.customerID = Tab2.customerID
) T2 ON T1.customerID = T2.customerID
LEFT OUTER JOIN dbo.SurveyResults R on T1.customerID = R.customerID
LEFT OUTER JOIN dbo.SurveyDetails D on T1.customerID = D.customerID
WHERE T1.TotalOrders > 10000.00 AND T2.TotalQuote > 100000.00
代码清单6.
SELECT T1.customerID, T1.customerName
INTO #temp
FROM
(
SELECT Tab1.customerID, Tab1.customerName, Tab1.StoreOrderTotal, Tab1.InternetOrderTotal
, Tab1.TotalOrders, Tab2.InternetQuoteTotal, Tab2.StoreQuoteTotal, Tab2.TotalQuote
FROM
(
SELECT A.customerID, a.customerName, a.orderTotal as InternetOrderTotal, b.orderTotal as StoreOrderTotal,
TotalOrders = a.orderTotal + b.orderTotal
FROM
(
SELECT c.customerID, c.customerName,
SUM(i.orderTotal)as orderTotal
FROM dbo.Customers c JOIN dbo.InternetOrders i ON c.customerID = i.customerID
WHERE i.orderDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(i.orderTotal)> 10000.00
) A
JOIN
(
SELECT c.customerID, c.customerName,
SUM(s.orderTotal)as orderTotal
FROM dbo.Customers c JOIN dbo.StoreOrders s ON c.customerID = s.customerID
WHERE s.orderDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(s.orderTotal)> 10000.00
) B on A.customerID = B.customerID
WHERE a.orderTotal + b.orderTotal > 100000.00
) Tab1 JOIN
(
SELECT A.customerID, a.customerName, a.quoteTotal as InternetQuoteTotal, b.quoteTotal as StoreQuoteTotal,
TotalQuote = a.quoteTotal + b.quoteTotal
FROM
(
SELECT c.customerID, c.customerName,
SUM(i.quoteTotal)as quoteTotal
FROM dbo.Customers c JOIN dbo.InternetQuotes i ON c.customerID = i.customerID
WHERE i.quoteDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(i.quoteTotal)> 10000.00
) A
JOIN
(
SELECT c.customerID, c.customerName,
SUM(s.quoteTotal)as quoteTotal
FROM dbo.Customers c JOIN dbo.StoreQuotes s ON c.customerID = s.customerID
WHERE s.quoteDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(s.quoteTotal)> 10000.00
) B on A.customerID = B.customerID
WHERE a.quoteTotal + b.quoteTotal > 100000.00
) Tab2 ON Tab1.customerID = Tab2.customerID
) T1 JOIN
(
SELECT Tab1.customerID, Tab1.customerName, Tab1.StoreOrderTotal, Tab1.InternetOrderTotal
, Tab1.TotalOrders, Tab2.InternetQuoteTotal, Tab2.StoreQuoteTotal, Tab2.TotalQuote
FROM
(
SELECT A.customerID, a.customerName, a.orderTotal as InternetOrderTotal, b.orderTotal as StoreOrderTotal,
TotalOrders = a.orderTotal + b.orderTotal
FROM
(
SELECT c.customerID, c.customerName,
SUM(i.orderTotal)as orderTotal
FROM dbo.Customers c JOIN dbo.TransactionType3 i ON c.customerID = i.customerID
WHERE i.orderDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(i.orderTotal)> 10000.00
) A
JOIN
(
SELECT c.customerID, c.customerName,
SUM(s.orderTotal)as orderTotal
FROM dbo.Customers c JOIN dbo.TransactionType4 s ON c.customerID = s.customerID
WHERE s.orderDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(s.orderTotal)> 10000.00
) B on A.customerID = B.customerID
WHERE a.orderTotal + b.orderTotal > 100000.00
) Tab1 JOIN
(
SELECT A.customerID, a.customerName, a.quoteTotal as InternetQuoteTotal, b.quoteTotal as StoreQuoteTotal,
TotalQuote = a.quoteTotal + b.quoteTotal
FROM
(
SELECT c.customerID, c.customerName,
SUM(i.orderTotal)as quoteTotal
FROM dbo.Customers c JOIN dbo.TransactionType5 i ON c.customerID = i.customerID
WHERE i.orderDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(i.orderTotal)> 10000.00
) A
JOIN
(
SELECT c.customerID, c.customerName,
SUM(s.orderTotal)as quoteTotal
FROM dbo.Customers c JOIN dbo.TransactionType6 s ON c.customerID = s.customerID
WHERE s.orderDate BETWEEN'2010-01-01'and'2010-12-31 23:59:59.999'
GROUP BY c.customerID, c.customerName
HAVING SUM(s.orderTotal)> 10000.00
) B on A.customerID = B.customerID
WHERE a.quoteTotal + b.quoteTotal > 100000.00
) Tab2 ON Tab1.customerID = Tab2.customerID
) T2 ON T1.customerID = T2.customerID
WHERE T1.TotalOrders > 10000.00 AND T2.TotalQuote > 100000.00
SELECT T1.customerName, R.ContactID, R.AggResults, D.surveyId, d.QuestionNbr, D.Rating, D.verbatim FROM #temp T1
LEFT OUTER JOIN dbo.SurveyResults R on T1.customerID = R.customerID
LEFT OUTER JOIN dbo.SurveyDetails D on T1.customerID = D.customerID
drop table #temp
代码清单7.
3.启用IO和TIME计数
4.包含实际的执行计划
在完成代码清单5后，运行代码清单6两次。第一次执行会包含解析，执行时间以及物理读的时间，因此主要观察第二次执行时间，在第二次执行结束后，查看实际的执行计划。对其中估计的行数和实际的行数进行对比。
值得注意的是，在数据流的开始，实际的行数和估计的行数相差很小。随着每一个数据集集和聚合的中间结果集做连接后，估计的行数变得越来越不准确。
随着由右向左的数据流向观察，直至最左边，你会发现估计的行数急剧下降，虽然实际的行数却没有变。最终，估计的行数变为1，而实际的行数是300,如图6所示。
图6.
当SQL Server低估了操作符中需要处理的数据集的行数时，会导致查询分析器做出次优的决定，比如说低效的连接顺序。最简单的例子是，错误的低估行数会导致中间结果集作为loop join的外层表，见图7。
图7.
在循环嵌套连接中，对应外层表的每一行，都需要在内存表执行相应的操作。在本例中，SQL Server选择循环嵌套连接是由于外层结果集的估计行数是1。这意味着仅仅需要在内层结果集执行一次操作。低估行数导致性能噩梦是由于当内部操作是索引或表扫描时出现，如图8所示。此时估计的行数是1，而实际的行数则为334。
图8.
因为上述估计的不准确，SQL Server认为做扫描而不是使用索引查找或其他连接策略是最优的选择。如果统计信息准确，则对SurveyDetails表的扫描只占了18%的执行成本，但实际上该表扫描执行了334次而不是估计的1次。因此该成本要远高于预计的值。在执行计划编译时所有的统计信息都是最新，但sql server同时也需要中间结果集来做出最有的选择。
解决该类问题
为了解决上述问题，可以使用临时表存储中间结果集来使得SQL Server计算统计信息后重编译。
注意：这里必须使用临时表，不能使用表变量。因为在临时表中插入数据后就为数据创建统计信息。但表变量中没有这些统计信息。但如果表变量是在option（recompile）中使用的，那么表变量的统计信息会在编译时获得。
代码清单7和代码清单6得到的结果是一样的，但方式不同。首先是select…into，这种方式将聚合后的数据存入临时表。一旦结果集保存到临时表后，SQL Server就可以创建和更新统计信息并且重编译执行计划来使得代码清单6中低效执行计划的出现。然后接下来的查询用于获得最终结果，该查询会将临时表中的数据和其他两个表进行连接，如图9所示。
图9.
可以看出，SQL Server分为多个步骤来使得查询更加高效，最主要的事将物理连接类型变为hash join，从而避免了多次扫描。
注意：随着查询复杂度的增加，有很多种不同的方式可以分解查询来提高效率。比如说，聚合后的数据可以插入临时表，你可以查看执行计划来找到实际行数和估计行数严重不符的部分，最好的选择是估计只有一行，但实际有多行的部分。
性能提升是否明显？
由于测试数据的随机性，下面测试结果的数字或许会略微不同，但性能的提升一定会很明显。表2展示了IO和CPU时间的差异。
表2.对比两个查询的性能
| 测试标准 | 第一个查询 | 第二个查询 | 性能提升百分比 |
| 逻辑读 | 1,622,398 | 11,685 | 99.90% |
| CPU时间 | 4,914 | 1,139 | 76.82% |
| 消耗时间 | 5,278 | 2,803 | 46.89% |
数据越多，查询越复杂，则性能提升越明显。上述数据已经足够证明分解查询带来的性能提升。
虽然上述例子中的聚合数据集是来自衍生表，但同样也可以来自CTE和逻辑视图。通过同样的步骤，使用临时表存储中间结果集就可以利用统计信息来生成高效的执行计划。
其他办法
代码清单6中的查询提示了索引缺失，创建索引可以消除表扫描从而提升性能，至少在少量数据集中是这样，这类查询通常来说是用于ETL或报表，执行的次数往往很少，因此创建和维护索引的成本需要考虑，因此需要考虑trade off。
另一种办法是使用hash join提示，但这并不总是最好的选择。使用临时表存储中间结果集允许查询优化器根据数据的大小选择最好的执行计划。这意味着可以获得更稳定的性能收益，而使用连接提示则性能变得不稳定。
还有一种办法是为中间结果集创建索引视图，此时统计信息在索引视图上进行维护。但是，索引视图在数据修改时的开销也不小，这也是锁争抢的来源之一，因此需要将多种可能的解决方案进行评估。
其他需要分解的复杂查询
需要分解的复杂查询不仅仅包含之前讨论的两种，虽然查询优化器一直在提升，但仍然可能某些特定查询需要分解来获得性能提升。
包含大量连接的查询
随着每次连接，初始数据集中的数据不断被过滤，因此在估计基数时引入了更多因此，每次单表数据中出现的小异常都会随着连接的增多被放大。
因此大量连接的查询应该被分解为多个查询，将中间结果存入临时表，然后使用临时表和数据集进行连接。
在WHERE和Join子句中出现的CASE子句
这类查询的一个例子包括“WHERE col1 = CASE @val1 WHEN 1 THEN ‘val1’ WHEN 2 THEN ‘val2’ WHEN 3 THEN col1”这样的子句使得获取估计行数变得困难，甚至不可能。任何时候估计的行数不准确都会导致低效的查询计划。
使用T-SQL的条件语句IF…ELSE来将这类查询分解为多个查询可以获得更准确的估计行数。
小结
通常来说，很少需要只使用一个复杂的查询语句获取所有结果。在诸如需要连接聚合结果的情况下，在单独一个查询中完成所有的步骤会导致不准确的预计基数，基数估计的偏差会导致低效的查询，在单独一个查询语句中，SQL Server无法再查询执行过程中暂停，在中间结果集上计算统计信息，并随之调整执行计划。
将查询分解并将中间结果集存入临时表使得SQL Server可以做到单独查询中做不到的事，即为中间结果集创建统计信息并根据这些信息重新生成执行计划。使用UNION和IF逻辑可使得SQL Server生成更高效的执行计划来得到同样的结果。
在SQL Server查询语句的设计中，高性能不仅取决于获取到的结果，还取决于如何获取结果。虽然查询优化器在大多数情况下能够很好的完成工作，但也有些情况下无法计算出统计信息，因此需要改变获取数据的方式从而获得稳定的高性能。本篇文章讨论了将大且复杂的语句分解为多个部分所需的技术。
---------------------------------------------------
原文链接:http://blogs.msdn.com/b/sqlcat/archive/2013/09/09/when-to-break-down-complex-queries.aspx