app好做吗,手机网络优化,标志设计图案,网站建设经验介绍问题描述#xff1a; 有一个查询如下#xff0c;去掉 TOP 1 的时候#xff0c;很快就出来结果了#xff0c;但加上 TOP 1 的时候#xff0c;一般要 2~3 秒才出数据#xff0c;何解#xff1f; SELECT TOP 1 ??? A . INVNO FROM A , B WHERE A . Item B . ItemNumber…问题描述 有一个查询如下去掉 TOP 1 的时候很快就出来结果了但加上 TOP 1 的时候一般要 2~3 秒才出数据何解 SELECT TOP 1 ??? A . INVNO FROM A , B WHERE A . Item B . ItemNumber ??? AND B . OwnerCompanyCode IS NOT NULL ? 问题原因分问题描述有一个查询如下去掉TOP 1的时候很快就出来结果了但加上TOP 1的时候一般要2~3秒才出数据何解SELECTTOP1???A.INVNOFROMA,BWHEREA.Item B.ItemNumber???ANDB.OwnerCompanyCode ISNOTNULL?问题原因分析在使用TOP 1的时候SQL Server会尽力先找出这条TOP 1的记录这就导致它采用了与不加TOP时不一致的扫描算法SQL Server查询优化器始终认为应该可以比较快的找到匹配的第1条记录所以一般是使用嵌套循环的联接则不加TOP 1时SQL Server会根据结构和数据的统计信息决策出联接策略。嵌套循环一般适用于联系的两个表一个表的数据较大而另一个表的数据较小的情况如果查询匹配的值出现在扫描的前端则在取TOP 1的情况下是符合嵌套循环联系的使用条件的但当匹配的数据出现在扫描的后端或者是基本上没有匹配的数据时则嵌套循环要扫描完成两个大表这显然是不适宜的也正是因为这种情况导致了TOP 1比不加TOP 1的效率慢很多?关于此问题的模拟环境USEtempdbGO?SETNOCOUNTON----创建测试环境--RAISERROR(创建测试环境,10,1)WITHNOWAIT-- Table ACREATETABLE[dbo].A(???[TranNumber] [int] IDENTITY(1,1)NOTNULL,???[INVNO] [char](8)NOTNULL,???[ITEM] [char](15)NULLDEFAULT(),???PRIMARYKEY([TranNumber]))?CREATEINDEX[indexONinvno] ON[dbo].A([INVNO])CREATEINDEX[indexOnitem] ON[dbo].A ([ITEM])CREATEINDEX[indexONiteminnvo] ON[dbo].A([INVNO],[ITEM])GO?-- Table BCREATETABLE[dbo].B(???[ItemNumber] [char](15)NOTNULLDEFAULT(),???[CompanyCode] [char] (4)NOTNULL,???[OwnerCompanyCode] [char](4)NULL,???PRIMARYKEY([ItemNumber],[CompanyCode]))?CREATEINDEX[ItemNumber] ON[dbo].B([ItemNumber])CREATEINDEX[CompanyCode] ON[dbo].B([CompanyCode])CREATEINDEX[OwnerCompanyCode] ON[dbo].B([OwnerCompanyCode])GO?----生成测试数据--RAISERROR(生成测试数据,10,1)WITHNOWAITINSERT[dbo].A([INVNO],[ITEM])SELECTLEFT(NEWID(),8),RIGHT(NEWID(),15)FROMsyscolumns A,syscolumns B?INSERT[dbo].B([ItemNumber],[CompanyCode],[OwnerCompanyCode])SELECTRIGHT(NEWID(),15),LEFT(NEWID(),4),LEFT(NEWID(),4)FROMsyscolumns A,syscolumns BGO?速度测试脚本----进行查询测试--RAISERROR(进行查询测试,10,1)WITHNOWAITDECLAREdt DATETIME,id int,loop intDECLARE TABLE(???id intIDENTITY,???[TOP 1] int,???[WITHOUT TOP] int)?SETloop 0WHILEloop 10BEGIN???SETloop loop 1???RAISERROR(test %d,10,1,loop)WITHNOWAIT???SETdt GETDATE()???????SELECTTOP1???????????A.INVNO???????FROMA,B???????WHEREA.Item B.ItemNumber???????????ANDB.OwnerCompanyCode ISNOTNULL???INSERT([TOP 1])VALUES(DATEDIFF(ms,dt,GETDATE()))???SELECTid SCOPE_IDENTITY(),dt GETDATE()???????SELECT--TOP 1???????????A.INVNO???????FROMA,B???????WHEREA.Item B.ItemNumber???????????ANDB.OwnerCompanyCode ISNOTNULL???UPDATE SET[WITHOUT TOP] DATEDIFF(ms,dt,GETDATE())???WHEREid idENDSELECT*FROMUNIONALLSELECTNULL,SUM([TOP 1]),SUM([WITHOUT TOP])FROMGO?测试数据的变更脚本DECLAREvalue char(15),value1 char(15)SELECT???value LEFT(NEWID(),15),???value1 LEFT(NEWID(),15)?UPDATEASETItem valueFROMA???INNERJOIN(???????SELECTTOP1???????????[TranNumber]???????FROM(???????????SELECTTOP20PERCENT???????????????[TranNumber]???????????FROMA???????????ORDERBY[TranNumber]???????)AA???????ORDERBY[TranNumber] DESC???)B???????ONA.[TranNumber] B.[TranNumber]?UPDATEBSETItemNumber valueFROMB???INNERJOIN(??????SELECTTOP1???????????[ItemNumber],[CompanyCode]???????FROM(???????????SELECTTOP20 PERCENT???????????????[ItemNumber],[CompanyCode]???????????FROMB???????????ORDERBY[ItemNumber],[CompanyCode]???????)BB???????ORDERBY[ItemNumber] DESC,[CompanyCode] DESC???)B1???????ONB.[ItemNumber] B1.[ItemNumber]???????????ANDB.[CompanyCode] B1.[CompanyCode]GO?测试说明1??在刚建立好测试环境的时候是没有任何匹配项的这时候TOP 1会扫描两个表的所有数据运行“速度测试脚本”可以看到此时有无TOP 1的效率差异TOP 1明显比不加TOP慢2??修改“测试数据的变更脚本”中红色的20让匹配的数据出现在扫描的顶端、中间和尾端分别使用“速度测试脚本”测试可以看到匹配的值靠近扫描的前端的时候TOP 1比不加TOP快随着匹配数据很后端的推移这种效率差异会越来越小到后面就变成TOP 1比不加TOP 1慢。注意每次变更数据并且完成“速度测试脚本”测试后需要修改“测试数据的变更脚本”中红色的value为value1让刚才设置匹配的数据再变回为不匹配?附联接的几种方式1????嵌套循环联接嵌套循环联接也称为“嵌套迭代”它将一个联接输入用作外部输入表(显示为图形执行计划中的顶端输入)将另一个联接输入用作内部(底端)输入表。外部循环逐行处理外部输入表。内部循环会针对每个外部行执行在内部输入表中搜索匹配行。最简单的情况是搜索时扫描整个表或索引这称为“单纯嵌套循环联接”。如果搜索时使用索引则称为“索引嵌套循环联接”。如果将索引生成为查询计划的一部分(并在查询完成后立即将索引破坏)则称为“临时索引嵌套循环联接”。查询优化器考虑了所有这些不同情况。如果外部输入较小而内部输入较大且预先创建了索引则嵌套循环联接尤其有效。在许多小事务中(如那些只影响较小的一组行的事务)索引嵌套循环联接优于合并联接和哈希联接。但在大型查询中嵌套循环联接通常不是最佳选择。?2????合并联接合并联接要求两个输入都在合并列上排序而合并列由联接谓词的等效(ON)子句定义。通常查询优化器扫描索引(如果在适当的一组列上存在索引)或在合并联接的下面放一个排序运算符。在极少数情况下虽然可能有多个等效子句但只用其中一些可用的等效子句获得合并列。由于每个输入都已排序因此Merge Join运算符将从每个输入获取一行并将其进行比较。例如对于内联接操作如果行相等则返回。如果行不相等则废弃值较小的行并从该输入获得另一行。这一过程将重复进行直到处理完所有的行为止。合并联接操作可以是常规操作也可以是多对多操作。多对多合并联接使用临时表存储行。如果每个输入中有重复值则在处理其中一个输入中的每个重复项时另一个输入必须重绕到重复项的开始位置。如果存在驻留谓词则所有满足合并谓词的行都将对该驻留谓词取值而只返回那些满足该驻留谓词的行。合并联接本身的速度很快但如果需要排序操作选择合并联接就会非常费时。然而如果数据量很大且能够从现有B树索引中获得预排序的所需数据则合并联接通常是最快的可用联接算法。?3????哈希联接哈希联接有两种输入生成输入和探测输入。查询优化器指派这些角色使两个输入中较小的那个作为生成输入。哈希联接用于多种设置匹配操作内部联接左外部联接、右外部联接和完全外部联接左半联接和右半联接交集联合和差异。此外哈希联接的某种变形可以进行重复删除和分组例如SUM(salary) GROUP BY department。这些修改对生成和探测角色只使用一个输入。以下几节介绍了不同类型的哈希联接内存中的哈希联接、Grace哈希联接和递归哈希联接。内存中的哈希联接哈希联接先扫描或计算整个生成输入然后在内存中生成哈希表。根据计算得出的哈希键的哈希值将每行插入哈希存储桶。如果整个生成输入小于可用内存则可以将所有行都插入哈希表中。生成阶段之后是探测阶段。一次一行地对整个探测输入进行扫描或计算并为每个探测行计算哈希键的值扫描相应的哈希存储桶并生成匹配项。Grace哈希联接如果生成输入大于内存哈希联接将分为几步进行。这称为“Grace哈希联接”。每一步都分为生成阶段和探测阶段。首先消耗整个生成和探测输入并将其分区(使用哈希键上的哈希函数)为多个文件。对哈希键使用哈希函数可以保证任意两个联接记录一定位于相同的文件对中。因此联接两个大输入的任务简化为相同任务的多个较小的实例。然后将哈希联接应用于每对分区文件。递归哈希联接如果生成输入非常大以至于标准外部合并的输入需要多个合并级别则需要多个分区步骤和多个分区级别。如果只有某些分区较大则只需对那些分区使用附加的分区步骤。为了使所有分区步骤尽可能快将使用大的异步I/O操作以便单个线程就能使多个磁盘驱动器繁忙工作。border: 1pt solid #dedfef; padding: 0cm本文原创发布php中文网转载请注明出处感谢您的尊重
