MSSQL教程之五种进步SQL功能的办法

上面我们说了DML的闪回方案。但对于DDL却无能为力，对于大多数的DDL，即使是rowbase格式，二进制日志binlog中仍只记录语句本身。对于删表操作，只记录一个语句droptablet。仅凭这句话，无法还原表的数据。功能五种进步SQL功能的办法
JohnnyPapa

偶然，为了让使用程序运转得更快，所做的全体事情就是在这里或那边做一些很小调剂。啊，但关头在于断定怎样举行调剂！早晚您会碰到这类情形：使用程序中的SQL查询不克不及依照您想要的体例举行呼应。它要末不前往数据，要末泯灭的工夫长得出奇。假如它下降了呈报或您的企业使用程序的速率，用户必需守候的工夫太长，他们就会很不中意。就像您的怙恃不想听您注释为何在深更三更才返来一样，用户也不会听你注释为何查询泯灭这么长工夫。（“对不起，妈妈，我利用了太多的LEFTJOIN。”）用户但愿使用程序呼应敏捷，他们的呈报可以在刹时以内前往剖析数据。就我本人而言，假如在Web上冲浪时某个页面要泯灭十多秒才干加载（好吧，五秒更实践一些），我也会很不耐心。

为懂得决这些成绩，主要的是找到成绩的本源。那末，从那里入手下手呢？基本缘故原由一般在于数据库计划和会见它的查询。在本月的专栏中，我将报告四项手艺，这些手艺可用于进步基于SQLServer?的使用程序的功能或改良其可伸缩性。我将细心申明LEFTJOIN、CROSSJOIN的利用和IDENTITY值的检索。请记着，基本没有奇妙的办理计划。调剂您的数据库及其查询必要占用工夫、举行剖析，还必要大批的测试。这些手艺都已被证实卓有成效，但对您的使用程序而言，大概个中一些手艺比另外一些手艺更合用。


本页内容


从INSERT前往IDENTITY



内嵌视图与一时表



制止LEFTJOIN和NULL



天真利用笛卡尔乘积



拾遗补零
从INSERT前往IDENTITY
我决意从碰到很多成绩的内容动手：怎样在实行SQLINSERT后检索IDENTITY值。一般，成绩不在于怎样编写检索值的查询，而在于在那里和什么时候举行检索。在SQLServer中，上面的语句可用于检索由最新在举动数据库毗连上运转的SQL语句所创立的IDENTITY值：

SELECT@@IDENTITY

这个SQL语句其实不庞大，但必要记着的一点是：假如这个最新的SQL语句不是INSERT，大概您针对非INSERTSQL的其他毗连运转了此SQL，则不会取得希冀的值。您必需运转以下代码才干检索紧跟在INSERTSQL以后且位于统一毗连上的IDENTITY，以下所示：

INSERTINTOProducts(ProductName)VALUES(Chalk)



SELECT@@IDENTITY

在一个毗连上针对Northwind数据库运转这些查询将前往一个称号为Chalk的新产物的IDENTITY值。以是，在利用ADO的VisualBasic?使用程序中，能够运转以下语句：

SetoRs=oCn.Execute("SETNOCOUNTON;INSERTINTOProducts_

(ProductName)VALUES(Chalk);SELECT@@IDENTITY")



lProductID=oRs(0)

此代码告知SQLServer不要前往查询的行计数，然后实行INSERT语句，并前往方才为这个新行创立的IDENTITY值。SETNOCOUNTON语句暗示前往的纪录集有一行和一列，个中包括了这个新的IDENTITY值。假如没有此语句，则会起首前往一个空的纪录集（由于INSERT语句不前往任何数据），然后会前往第二个纪录集，第二个纪录会合包括IDENTITY值。这大概有些使人狐疑，特别是由于您历来就没有但愿过INSERT会前往纪录集。之以是会产生此情形，是由于SQLServer看到了这个行计数（即一行遭到影响）并将其注释为暗示一个纪录集。因而，真实的数据被推回到了第二个纪录集。固然您可使用ADO中的NextRecordset办法猎取此第二个纪录集，但假如总可以起首前往该纪录集且只前往该纪录集，则会更便利，也更无效率。

此办法固然无效，但必要在SQL语句中分外增加一些代码。取得不异了局的另外一办法是在INSERT之前利用SETNOCOUNTON语句，并将SELECT@@IDENTITY语句放在表中的FORINSERT触发器中，以下面的代码片断所示。如许，任何进进该表的INSERT语句都将主动前往IDENTITY值。

CREATETRIGGERtrProducts_InsertONProductsFORINSERTAS

SELECT@@IDENTITY

GO

触发器只在Products表上产生INSERT时启动，以是它老是会在乐成INSERT以后前往一个IDENTITY。利用此手艺，您能够一直以不异的体例在使用程序中检索IDENTITY值。

前往页首
内嵌视图与一时表
某些时分，查询必要将数据与其他一些大概只能经由过程实行GROUPBY然后实行尺度查询才干搜集的数据举行连接。比方，假如要查询最新五个订单的有关信息，您起首必要晓得是哪些订单。这可使用前往订单ID的SQL查询来检索。此数据就会存储在一时表（这是一个经常使用手艺）中，然后与Products表举行连接，以前往这些订单售出的产物数目：

CREATETABLE#Temp1(OrderIDINTNOTNULL,_

OrderDateDATETIMENOTNULL)



INSERTINTO#Temp1(OrderID,OrderDate)

SELECTTOP5o.OrderID,o.OrderDate

FROMOrdersoORDERBYo.OrderDateDESC



SELECTp.ProductName,SUM(od.Quantity)ASProductQuantity

FROM#Temp1t

INNERJOIN[OrderDetails]odONt.OrderID=od.OrderID

INNERJOINProductspONod.ProductID=p.ProductID

GROUPBYp.ProductName

ORDERBYp.ProductName



DROPTABLE#Temp1

这些SQL语句会创立一个一时表，将数据拔出该表中，将其他数据与该表举行连接，然后撤除该一时表。这会招致此查询举行大批I/O操纵，因而，能够从头编写查询，利用内嵌视图代替一时表。内嵌视图只是一个能够连接到FROM子句中的查询。以是，您不必在tempdb中的一时表上泯灭大批I/O和磁盘会见，而可使用内嵌视图失掉一样的了局：

SELECTp.ProductName,

SUM(od.Quantity)ASProductQuantity

FROM(

SELECTTOP5o.OrderID,o.OrderDate

FROMOrderso

ORDERBYo.OrderDateDESC

)t

INNERJOIN[OrderDetails]odONt.OrderID=od.OrderID

INNERJOINProductspONod.ProductID=p.ProductID

GROUPBY

p.ProductName

ORDERBY

p.ProductName

此查询不但比后面的查询效力更高，并且长度更短。一时表会损耗大批资本。假如只必要将数据连接到其他查询，则能够尝尝利用内嵌视图，以节俭资本。

前往页首
制止LEFTJOIN和NULL
固然，有良多时分您必要实行LEFTJOIN和利用NULL值。可是，它们其实不合用于一切情形。改动SQL查询的构建体例大概会发生将一个花几分钟运转的呈报延长到只花几秒钟如许的大相径庭的效果。偶然，必需在查询中调剂数据的形状，使之顺应使用程序所请求的显现体例。固然TABLE数据范例会削减大批占用资本的情形，但在查询中另有很多地区能够举行优化。SQL的一个有代价的经常使用功效是LEFTJOIN。它能够用于检索第一个表中的一切行、第二个表中一切婚配的行、和第二个表中与第一个表不婚配的一切行。比方，假如但愿前往每一个客户及其订单，利用LEFTJOIN则能够显现有订单和没有订单的客户。

此工具大概会被过分利用。LEFTJOIN损耗的资本十分之多，由于它们包括与NULL（不存在）数据婚配的数据。在某些情形下，这是不成制止的，可是价值大概十分高。LEFTJOIN比INNERJOIN损耗资本更多，以是假如您能够从头编写查询以使得该查询不利用任何LEFTJOIN，则会失掉十分可不雅的报答（请参阅中的图）。

查询

加速利用LEFTJOIN的查询速率的一项手艺触及创立一个TABLE数据范例，拔出第一个表（LEFTJOIN左边的表）中的一切行，然后利用第二个表中的值更新TABLE数据范例。此手艺是一个两步的历程，但与尺度的LEFTJOIN比拟，能够节俭大批工夫。一个很好的划定规矩是实验各类分歧的手艺并纪录每种手艺所需的工夫，直到取得用于您的使用程序的实行功能最好的查询。

测试查询的速率时，有需要屡次运转此查询，然后取一个均匀值。由于查询（或存储历程）大概会存储在SQLServer内存中的历程缓存中，因而第一次实验泯灭的工夫仿佛稍长一些，而一切后续实验泯灭的工夫都较短。别的，运转您的查询时，大概正在针对不异的表运转其他查询。当其他查询锁定息争锁这些表时，大概会招致您的查询要列队守候。比方，假如您举行查询时或人正在更新此表中的数据，则在更新提交时您的查询大概必要泯灭更长工夫来实行。

制止利用LEFTJOIN时速率下降的最复杂办法是尽量多地环绕它们计划数据库。比方，假定某一产物大概具有种别也大概没有种别。假如Products表存储了其种别的ID，而没有效于某个特定产物的种别，则您能够在字段中存储NULL值。然后您必需实行LEFTJOIN来猎取一切产物及其种别。您能够创立一个值为“NoCategory”的种别，从而指定外键干系不同意NULL值。经由过程实行上述操纵，如今您就能够利用INNERJOIN检索一切产物及其种别了。固然这看起来仿佛是一个带有过剩数据的变通办法，但多是一个很有代价的手艺，由于它能够打消SQL批处置语句中损耗资本较多的LEFTJOIN。在数据库中全体利用此观点能够为您节俭大批的处置工夫。请记着，关于您的用户而言，即便几秒钟的工夫也十分主要，由于当您有很多用户正在会见统一个联机数据库使用程序时，这几秒钟实践上的意义会十分严重。

前往页首
天真利用笛卡尔乘积
关于此技能，我将举行十分具体的先容，并倡始在某些情形下利用笛卡尔乘积。出于某些缘故原由，笛卡尔乘积(CROSSJOIN)遭到了良多斥责，开辟职员一般会被告诫基本就不要利用它们。在很多情形下，它们损耗的资本太多，从而没法高效利用。可是像SQL中的任何工具一样，假如准确利用，它们也会很有代价。比方，假如您想运转一个前往每个月数据（即便某一特定月份客户没有订单也要前往）的查询，您就能够很便利地利用笛卡尔乘积。中的SQL就实行了上述操纵。

固然这看起来仿佛没甚么奇妙的，可是请思索一下，假如您从客户到订单（这些订单按月份举行分组并对发卖额举行小计）举行了尺度的INNERJOIN，则只会取得客户有订单的月份。因而，关于客户未订购任何产物的月份，您不会取得0值。假如您想为每一个客户都绘制一个图，以显现每月和该月发卖额，则大概但愿此图包含月发卖额为0的月份，以便直不雅标识出这些月份。假如利用中的SQL，数据则会跳过发卖额为0美圆的月份，由于在订单表中关于零发卖额不会包括任何行（假定您只存储产生的事务）。

中的代码固然较长，可是能够到达猎取一切发卖数据（乃至包含没有发卖额的月份）的方针。起首，它会提取客岁一切月份的列表，然后将它们放进第一个TABLE数据范例表(@tblMonths)中。下一步，此代码会猎取在该工夫段内有发卖额的一切客户公司的称号列表，然后将它们放进另外一个TABLE数据范例表(@tblCus-tomers)中。这两个表存储了创立了局集所必须的一切基础数据，但实践发卖数目除外。第一个表中列出了一切月份（12行），第二个表中列出了这个工夫段内有发卖额的一切客户（关于我是81个）。并不是每一个客户在已往12个月中的每月都购置了产物，以是，实行INNERJOIN或LEFTJOIN不会前往每月的每一个客户。这些操纵只会前往购置产物的客户和月份。

笛卡尔乘积则能够前往一切月份的一切客户。笛卡尔乘积基础上是将第一个表与第二个表相乘，天生一个行汇合，个中包括第一个表中的行数与第二个表中的行数相乘的了局。因而，笛卡尔乘积会向表@tblFinal前往972行。最初的步骤是利用这天期局限内每一个客户的月发卖额总计更新@tblFinal表，和选择终极的行集。

假如因为笛卡尔乘积占用的资本大概会良多，而不必要真实的笛卡尔乘积，则能够审慎地利用CROSSJOIN。比方，假如对产物和种别实行了CROSSJOIN，然后利用WHERE子句、DISTINCT或GROUPBY来选择出年夜多半行，那末利用INNERJOIN会取得一样的了局，并且效力高很多。假如必要为一切的大概性都前往数据（比方在您但愿利用每个月发卖日期添补一个图表时），则笛卡尔乘积大概会十分有匡助。可是，您不该该将它们用于其他用处，由于在年夜多半计划中INNERJOIN的效力要高很多。

前往页首
拾遗补零
这里先容其他一些可匡助进步SQL查询效力的经常使用手艺。假定您将按地区对一切发卖职员举行分组并将他们的发卖额举行小计，可是您只想要那些数据库中标志为处于举动形态的发卖职员。您能够按地区对发卖职员分组，并利用HAVING子句打消那些未处于举动形态的发卖职员，也能够在WHERE子句中实行此操纵。在WHERE子句中实行此操纵会削减必要分组的行数，以是比在HAVING子句中实行此操纵效力更高。HAVING子句中基于行的前提的选择会强迫查询对那些在WHERE子句中会被往除的数据举行分组。

另外一个进步效力的技能是利用DISTINCT关头字查找数据行的独自报表，来取代利用GROUPBY子句。在这类情形下，利用DISTINCT关头字的SQL效力更高。请在必要盘算聚合函数（SUM、COUNT、MAX等）的情形下再利用GROUPBY。别的，假如您的查询老是本人前往一个独一的行，则不要利用DISTINCT关头字。在这类情形下，DISTINCT关头字只会增添体系开支。

您已看到了，有大批手艺都可用于优化查询和完成特定的营业划定规矩，技能就是举行一些实验，然后对照它们的功能。最主要的是要测试、测试、再测试。在此专栏的未来各期内容中，我将持续深切报告SQLServer观点，包含数据库计划、好的索引理论和SQLServer平安典范。

若有向Johnny提出的成绩和倡议，请发送电子邮件到mmdata@microsoft.com

JohnnyPapa是北卡罗来纳州罗亨通的MJM研讨公司的信息手艺副总裁，他著有ProfessionalADO25RDSProgrammingwithASP(Wrox,2000)，并常常外行业集会中做演讲。要与他接洽，请发送电子邮件到datapoints@lancelotweb.com
提供多语言支持，常见的编码如中文的GB2312、BIG5，日文的Shift_JIS等都可以用作数据表名和数据列名。

对一张百万级别的表建游标，同时又没有什么过滤条件，取得游标效率是如果直接SQL查询百万条数据；如果再对每条记录做处理，耗时将更长。

但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

财务软件要用SQL也只是后台的数据库而已,软件都是成品的,当然多学东西肯定是有好处的..

原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。

也可谈一下你是怎么优化存储过程的?

仓酷云

大侠们有推荐的书籍和学习方法写下吧。

连做梦都在想页面结构是怎么样的，绝非虚言

在select语句中可以使用groupby子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。