精华内容
下载资源
问答
  • 提高sql执行效率的几点建议提高sql执行效率的几点建议
  • SQL艺术、提高SQL执行效率的方法总结SQL艺术、提高SQL执行效率的方法总结SQL艺术、提高SQL执行效率的方法总结SQL艺术、提高SQL执行效率的方法总结SQL艺术、提高SQL执行效率的方法总结SQL艺术、提高SQL执行效率的方法...
  • 提高SQL执行效率的16种方法,非常不错
  • sql执行效率一直都是为人所关注,那到底应该怎样提高呢?有什么比较好的方法,下面与大家分享下比较不错的建议,感兴趣的朋友可以参考下,希望对大家有所帮助
  • Oracle提供了多种方法在执行带有大量执行计划的复杂查询时剖析过程会拖累系统的性能,感兴趣的朋友可以了解下
  • 提高SQL执行效率的方法有哪些?

    千次阅读 2014-05-31 19:14:36
    2、JAVA中的存储过程?     3、解释select * from dual的执行计划?     4、提高SQL执行效率的方法有哪些?

     (1)SELECT子句中避免使用‘*’:

    Oracle在解析的过程中,会将‘*’依次转换成所有的列名, 这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。

    (2)尽量多使用COMMIT:

    只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少,COMMIT所释放的资源: 

    a. 回滚段上用于恢复数据的信息。

    b. 被程序语句获得的锁。

    c.redo log buffer 中的空间。

    (3)用Where子句替换HAVING子句:

    避免使用HAVING子句, HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序,总计等操作.

    如果能通过WHERE 子句限制记录的数目,那就能减少这方面的开销。(非oracle中)on、where、having这三个都可以加条件的子句中,on是最先执行,where次之,having最后,因为on是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的,where也应该比having快点的,因为它过滤数据后才进行sum,在两个表联接时才用on的,所以在一个表的时候,就剩下where跟having比较了。

    在这单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,只是where 可以使用rushmore技术,而having就不能,在速度上后者要慢如果要涉及到计算的字段,就表示在没计算之前,这个字段的值是不确定的,根据上篇写的工作流程,where的作用时间是在计算之前就完成的,而having就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。

    在多表联接查询时,on比where更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由where进行过滤,然后再计算,计算完后再由

    having进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条

    件应该在什么时候起作用,然后再决定放在那里。

    (4)用EXISTS替代IN、用NOTEXISTS替代NOT IN:

    在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。

    在这种情况下, 使用EXISTS(或NOTEXISTS)通常将提高查询的效率。在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下

    ,NOTIN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

    例子:

    (高效)

    SELECT* FROM EMP (基础表)WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHEREDEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

    (低效)

    SELECT* FROM EMP (基础表) WHERE EMPNO > 0  ANDDEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')

    (5)用索引提高效率:

    索引是表的一个概念部分,用来提高检索数据的效率,

    Oracle使用了一个复杂的自平衡B-tree结构。通常,通过索引查询数据比全表扫描要快。当Oracle找出执行查询和Update语句的最佳路径时, Oracle优化器将使用索引。同样在联结多个表时使用索引也可以提高效率。另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。

    那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列。通常,在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率。虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价。

    索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改。这意味着每条记录的INSERT, DELETE , UPDATE将为此多付出4, 5次的磁盘I/O 。因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是有必要的:

    ALTERINDEX <INDEXNAME> REBUILD <TABLESPACENAME>(18)用EXISTS替换DISTINCT:当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。

    例子:

     (低效):

    SELECTDISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO 

    (高效):

    SELECTDEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X' FROM EMP E WHEREE.DEPT_NO = D.DEPT_NO); 

    (6)避免在索引列上使用NOT

    通常,我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响。当Oracle“遇到”NOT,他就会停止使用索引转而执行全表扫描。

    (7)避免在索引列上使用计算。

    WHERE子句中,如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。

    举例: 

    低效:

    SELECT„ FROM DEPT WHERE SAL * 12 > 25000;

    高效: 

    SELECT„ FROM DEPT WHERE SAL > 25000/12;

    (8)总是使用索引的第一个列:

    如果索引是建立在多个列上, 只有在它的第一个列

    (leadingcolumn)

    被where子句引用时,优化器才会选择使用该索引。这也是一条简单而重要的规则,当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。

    (9)用UNION-ALL 替换UNION ( 如果有可能的话):

    当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序。如果用UNION ALL替代UNION, 这样排序就不是必要了。

    效率就会因此得到提高。需要注意的是,UNION ALL 将重复输出两个结果集合中相同记录。

    因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存。对于这块内存的优化也是相当重要的。下面的SQL可以用来查询排序的消耗量:

    低效:

    SELECTACCT_NUM, BALANCE_AMT  FROMDEBIT_TRANSACTIONS  WHERE TRAN_DATE ='31-DEC-95' 

    UNION 

    SELECTACCT_NUM, BALANCE_AMT  FROMDEBIT_TRANSACTIONS  WHERE TRAN_DATE ='31-DEC-95' 

    高效: 

    SELECTACCT_NUM, BALANCE_AMT  FROMDEBIT_TRANSACTIONS  WHERE TRAN_DATE ='31-DEC-95' 

    UNIONALL 

    SELECTACCT_NUM, BALANCE_AMT  FROMDEBIT_TRANSACTIONS  WHERE TRAN_DATE ='31-DEC-95' 

    (10)用WHERE替代ORDER BY:

    ORDERBY 子句只在两种严格的条件下使用索引。

    ORDERBY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。ORDER BY中所有的列必须定义为非空。WHERE子句使用的索引和ORDER BY

    子句中所使用的索引不能并列。

    例如:

    表DEPT包含以下列: 

    DEPT_CODEPK NOT NULL 

    DEPT_DESCNOT NULL 

    DEPT_TYPENULL 

    低效: (索引不被使用)

    SELECTDEPT_CODE FROM DEPT ORDER BY DEPT_TYPE 

    高效: (使用索引)

    SELECTDEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0 

    (11)避免改变索引列的类型:

    当比较不同数据类型的数据时,ORACLE自动对列进行简单的类型转换。假设

     EMPNO是一个数值类型的索引列:

    SELECT* FROM EMP  WHERE EMPNO = ‘123'。

    实际上,经过Oracle类型转换, 语句转化为

    SELECT* FROM EMP WHERE EMPNO = TO_NUMBER(‘123') 。

    幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变。现在,假设EMP_TYPE是一个字符类型的索引列:

    SELECT* FROM  EMP WHERE EMP_TYPE = 123 。

    这个语句被Oracle转换为:

    SELECT* FROM EMP WHERETO_NUMBER(EMP_TYPE)=123。因为内部发生的类型转换, 这个索引将不会被用到! 为了避免Oracle对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来。注意当字符和数值比较时, Oracle会优先转换数值类型到字符类型。

    (12)需要当心的WHERE子句: 

    某些SELECT 语句中的WHERE子句不使用索引。这里有一些例子:

    (a)‘!=' 将不使用索引。记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中。

    (b)‘||'是字符连接函数。就象其他函数那样, 停用了索引。

    (c)‘+'是数学函数。就象其他数学函数那样, 停用了索引。

    (d)相同的索引列不能互相比较,这将会启用全表扫描。

    (13)避免使用耗费资源的操作: 

    带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。

    DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS, INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强。

    (14)优化GROUP BY: 

    提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。下面两个查询返回相同结果但第二个明显就快了许多。

    低效: 

    SELECTJOB, AVG(SAL)  FROM EMP  GROUP JOB HAVING JOB = 'PRESIDENT'  OR JOB ='MANAGER' 

    高效: 

    SELECTJOB, AVG(SAL)  FROM EMP  WHERE JOB = 'PRESIDENT'  OR JOB = 'MANAGER'  GROUP JOB 

    (15)、尽量不要在where中包含子查询;

    (16)、采用绑定变量

    (17)、在WHERE中尽量不要使用OR

    (18)、用EXISTS替代IN、用NOT EXISTS替代NOTIN;

    (19)、用IN来替代OR: WHERE LOC_ID=10 OR LOC_ID=15 ORLOC_ID=20

    (20)、避免在索引列上使用ISNULL和IS NOT NULL;

    (21)、避免带有LIKE参数的通配符,LIKE ’4YE%’使用索引,但LIKE ‘%YE’不使用索引

    (22)、尽量明确的完成SQL语句,尽量少让数据库工作

    比如写SELECT语句时,需要把查询的字段明确指出表名。尽量不要使用SELECT *语句。组织SQL语句的时候,尽量按照数据库的习惯进行组织。

     

    展开全文
  • 详细介绍了使用 BULK COLLECT 进行批量操作 提高sql执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询
  • Oracle提高sql执行效率的建议(1)

    千次阅读 2013-12-21 00:55:50
    Oracle提高sql执行效率的建议   -->FROM子句中包含多个表的情况下,选择记录条数最少的表作为基础表    -->解析WHERE子句是自下而上的 过滤条件要有顺序    -->ORACLE会将'*'转换成列名    -->DELETE...

    Oracle提高sql执行效率的建议

     

    -->FROM子句中包含多个表的情况下,选择记录条数最少的表作为基础表  

      

    -->解析WHERE子句是自下而上的 过滤条件要有顺序  

      

    -->ORACLE会将'*'转换成列名  

      

    -->DELETE会在rollback segment中存放可恢复信息,可以试试TRUNCATE  

      

    -->COMMIT会释放:1.rollback segment  

                    2.被程序语句获得的锁  

                    3.redo log buffer  

      

    -->把Alias前缀于每个Column上可以减少解析的时间  

      

    -->ORACLE解析sql语句时会把小写的字母转换成大写的再执行  

      

    -->在索引列上使用NOT会停止使用索引转而执行全表扫描  

      

    -->在索引列上进行运算会停止使用索引转而执行全表扫描  

      

    -->使用'>=',而不是'>'  

      

    -->where子句中'!='、'||'、'+'等符号会破坏索引  

      

    -->IN的效率很低,IN子句将执行一个内部的排序和合并  

      

    -->EXIST查询更为迅速  

      

    -->对于两个索引列,UNION比OR效率更高  

      

    -->IS NULL会破坏索引  

      

    -->DISTINCT,UNION,MINUS,INTERSECT,ORDER BY等都带有SORT功能,会耗费资源  

      

    -->多个索引列时尽量使用第一个索引列  

      

    -->尽量用 UNION ALL代替UNION  

      

    -->不要用TO_NUMBER()之类的函数改变索引列的类型。  

      

    -->对于字符类型的索引列尽量写成:col = '123' 而不是 col = 123,后者会被解析为TO_NUMBER(EMP_TYPE)=123     

      

    -->适当的地方使用适当的关键字:HAVING会在检索出所有记录之后对结果集进行过滤 能使用WHERE解决时不要用HAVING  

      

    -->索引也需要维护,INSERT,DELETE,UPDATE会因索引而做出更多次I/O  

      

    -->重构索引是必要的:

    ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME> [ONLINE]  

     

    展开全文
  • 提高SQL执行效率的17个技巧

    千次阅读 2019-03-01 09:53:48
    由于数据库领域仍相对不成熟,每个平台上的 SQL 开发人员都在苦苦挣扎,一次又一次犯同样的错误。 当然,数据库厂商在取得一些进展,并继续在竭力处理较重大的问题。 无论 SQL 开发人员在 SQL Server、Oracle、DB2...

    由于数据库领域仍相对不成熟,每个平台上的 SQL 开发人员都在苦苦挣扎,一次又一次犯同样的错误。

    当然,数据库厂商在取得一些进展,并继续在竭力处理较重大的问题。

    无论 SQL 开发人员在 SQL Server、Oracle、DB2、Sybase、MySQL,还是在其他任何关系数据库平台上编写代码,并发性、资源管理、空间管理和运行速度都仍困扰着他们。

    问题的一方面是,不存在什么灵丹妙药;针对几乎每条最佳实践,我都可以举出至少一个例外。

    通常,开发人员找到自己青睐的方法,而懒得研究其他方法。这也许是缺乏教育的表现,或者开发人员没有认识到自己何时做错了。也许针对一组本地测试数据,查询运行起来顺畅,但是换成生产级系统,表现就差强人意。

    我没有期望 SQL 开发人员成为管理员,但他们在编写代码时必须考虑到生产级环境的问题。如果他们在开发初期不这么做,数据库管理员后期会让他们返工,遭殃的就是用户。

    我们说调优数据库既是门艺术,又是门科学,这是有道理的,因为很少有全面适用的硬性规则。你在一个系统上解决的问题在另一个系统上不是问题,反之亦然。

    说到调优查询,没有正确的答案,但这并不意味着就此应该放弃。你可以遵循以下17条原则,有望收到很好的效果。

    不要用 UPDATE 代替 CASE

    这个问题很常见,却很难发觉,许多开发人员常常忽视这个问题,原因是使用 UPDATE 再自然不过,这似乎合乎逻辑。

    以这个场景为例:你把数据插入一个临时表中,如果另一个值存在,需要它显示某个值。

    也许你从 Customer 表中提取记录,想把订单金额超过 100000 美元的客户标记为“Preferred”。

    因而,你将数据插入到表中,运行 UPDATE 语句,针对订单金额超过 100000 美元的任何客户,将 CustomerRank 这一列设为“Preferred”。

    问题是,UPDATE 语句记入日志,这就意味着每次写入到表中,要写入两次。

    解决办法:在 SQL 查询中使用内联 CASE 语句,这检验每一行的订单金额条件,并向表写入“Preferred”标记之前,设置该标记,这样处理性能提升幅度很惊人。

    不要盲目地重用代码

    这个问题也很常见,我们很容易拷贝别人编写的代码,因为你知道它能获取所需的数据。

    问题是,它常常获取过多你不需要的数据,而开发人员很少精简,因此到头来是一大堆数据。

    这通常表现为 WHERE 子句中的一个额外外连接或额外条件。如果你根据自己的确切要求精简重用的代码,就能大幅提升性能。

    需要几列,就提取几列

    这个问题类似第 2 个问题,但这是列所特有的。很容易用 SELECT* 来编写所有查询代码,而不是把列逐个列出来。

    问题同样是,它提取过多你不需要的数据,这个错误我见过无数次了。开发人员对一个有 120 列、数百万行的表执行 SELECT* 查询,但最后只用到其中的三五列。

    因此,你处理的数据比实际需要的多得多,查询返回结果是个奇迹。你不仅处理过多不需要的数据,还夺走了其他进程的资源。

    不要查询两次(double-dip)

    这是我看到好多人犯的另一个错误:写入存储过程,从一个有数亿行的表中提取数据。

    开发人员想提取住在加利福尼亚州,年收入高于 4 万美元的客户信息。于是,他查询住在加利福尼亚州的客户,把查询结果放到一个临时表中。

    然后再来查询年收入高于 4 万美元的客户,把那些结果放到另一个临时表中。最后他连接这两个表,获得最终结果。

    你是在逗我吧?这应该用一次查询来完成,相反你对一个超大表查询两次。别犯傻了:大表尽量只查询一次,你会发现存储过程执行起来快多了。

    一种略有不同的场景是,某个过程的几个步骤需要大表的一个子集时,这导致每次都要查询大表。

    想避免这个问题,只需查询这个子集,并将它持久化存储到别处,然后将后面的步骤指向这个比较小的数据集。

    知道何时使用临时表

    这个问题解决起来要麻烦一点,但效果显著。在许多情况下可以使用临时表,比如防止对大表查询两次。还可以使用临时表,大幅减少连接大表所需的处理能力。

    如果你必须将一个表连接到大表,该大表上又有条件,只需将大表中所需的那部分数据提取到临时表中,然后再与该临时表连接,就可以提升查询性能。

    如果存储过程中有几个查询需要对同一个表执行类似的连接,这同样大有帮助。

    预暂存数据

    这是我最爱聊的话题之一,因为这是一种经常被人忽视的老方法。

    如果你有一个报表或存储过程(或一组)要对大表执行类似的连接操作,通过提前连接表,并将它们持久化存储到一个表中来预暂存数据,就可以对你大有帮助。

    现在,报表可以针对该预暂存表来运行,避免大连接。你并非总是可以使用这个方法,但一旦用得上,你会发现这绝对是节省服务器资源的好方法。

    请注意:许多开发人员避开这个连接问题的做法是,将注意力集中在查询本身上,根据连接创建只读视图,那样就不必一次又一次键入连接条件。

    但这种方法的问题是,仍要为需要它的每个报表运行查询。如果预暂存数据,你只要运行一次连接(比如说报表前 10 分钟),别人就可以避免大连接了。

    你不知道我有多喜欢这一招,在大多数环境下,有些常用表一直被连接起来,所以没理由不能先预暂存起来。

    批量删除和更新

    这是另一个经常被忽视的技巧,如果你操作不当,删除或更新来自大表的大量数据可能是一场噩梦。

    问题是,这两种语句都作为单一事务来运行。如果你需要终结它们,或者它们在执行时系统遇到了问题,系统必须回滚(roll back)整个事务,这要花很长的时间。

    这些操作在持续期间还会阻塞其他事务,实际上给系统带来了瓶颈,解决办法就是,小批量删除或更新。

    这通过几个方法来解决问题:

    • 无论事务因什么原因而被终结,它只有少量的行需要回滚,那样数据库联机返回快得多。
    • 小批量事务被提交到磁盘时,其他事务可以进来处理一些工作,因而大大提高了并发性。

    同样,许多开发人员一直固执地认为:这些删除和更新操作必须在同一天完成。事实并非总是如此,如果你在归档更是如此。

    如果你需要延长该操作,可以这么做,小批量有助于实现这点;如果你花更长的时间来执行这些密集型操作,切忌拖慢系统的运行速度。

    使用临时表来提高游标性能

    如果可能的话,最好避免游标。游标不仅存在速度问题,而速度问题本身是许多操作的一大问题,还会导致你的操作长时间阻塞其他操作,这大大降低了系统的并发性。

    然而无法总是避免使用游标,避免不了使用游标时,可以改而对临时表执行游标操作,以此摆脱游标引发的性能问题。

    不妨以查阅一个表,基于一些比较结果来更新几个列的游标为例。你也许可以将该数据放入临时表中,然后针对临时表进行比较,而不是针对活动表进行比较。

    然后你可以针对小得多,锁定时间很短的活动表运行单一的 UPDATE 语句。

    进行这样的数据修改可大大提高并发性。最后我要说,你根本不需要使用游标,总是会有一种基于集合的解决方法。

    不要嵌套视图

    视图也许很方便,不过使用视图时要小心。

    虽然视图有助于将庞大查询遮掩起来、无须用户操心,并实现数据访问标准化,但你很容易发现自己陷入这种困境:视图 A 调用视图 B,视图 B 调用视图 C,视图 C 又调用视图 D,这就是所谓的嵌套视图。

    这会导致严重的性能问题,尤其是这两方面:

    • 返回的数据很有可能比你需要的多得多。
    • 查询优化器将放弃并返回一个糟糕的查询方案。

    我遇到过喜欢嵌套视图的客户,这个客户有一个视图用于几乎所有数据,因为它有两个重要的连接。

    问题是,视图返回的一个列里面居然有 2MB 大小的文档,有些文档甚至更大。

    在运行的几乎每一次查询中,这个客户要在网络上为每一行至少多推送 2MB 的数据。自然,查询性能糟糕透顶。

    没有一个查询实际使用该列!当然,该列被埋在七个视图的深处,要找出来都很难。我从视图中删除该文档列后,最大查询的时间从 2.5 小时缩短至 10 分钟。

    我最后层层解开了嵌套视图(有几个不必要的连接和列),并写了一个普通的查询,结果同样这个查询的时间缩短至不到 1 秒。

    使用表值函数

    这是一直以来我最爱用的技巧之一,因为它是只有专家才知道的那种秘诀。

    在查询的 SELECT 列表中使用标量函数时,该函数因结果集中的每一行而被调用,这会大幅降低大型查询的性能。

    然而可以将标量函数转换成表值函数,然后在查询中使用 CROSS APPLY,就可以大幅提升性能,这个奇妙的技巧可以显著提升性能。

    使用分区避免移动大型数据

    不是每个人都能利用依赖 SQL Server Enterprise 中分区的这个技巧,但是对于能利用它的人来说,这个技巧很棒。

    大多数人没有意识到 SQL Server 中的所有表都是分区的。如果你喜欢,可以把一个表分成多个分区,但即使简单的表也从创建那一刻起就分区了。

    然而,它们是作为单个分区创建的。如果你在运行 SQL Server Enterprise,已经可以随时享用分区表的优点了。

    这意味着你可以使用 SWITCH 之类的分区功能,归档来自仓库加载的大量数据。

    举个实际例子,去年我碰到过这样一个客户:该客户需要将数据从当日的表复制到归档表中;那样万一加载失败,公司可以迅速用当日的表来恢复。

    由于各种原因,无法每次将表的名称改来改去,所以公司每天在加载前将数据插入到归档表中,然后从活动表删除当日的数据。

    这个过程一开始很顺利,但一年后,复制每个表要花 1 个半小时,每天要复制几个表,问题只会越来越糟。

    解决办法是抛弃 INSERT 和 DELETE 进程,使用 SWITCH 命令。

    SWITCH 命令让该公司得以避免所有写入,因为它将页面分配给了归档表。

    这只是更改了元数据,SWITCH 运行平均只要两三秒钟,如果当前加载失败,你可以通过 SWTICH 将数据切换回到原始表。

    如果你非要用 ORM,请使用存储过程

    ORM 是我经常炮轰的对象之一。简而言之,别使用 ORM(对象关系映射器)。

    ORM 会生成世界上最糟糕的代码,我遇到的几乎每个性能问题都是由它引起的。

    相比知道自己在做什么的人,ORM 代码生成器不可能写出一样好的 SQL。但是如果你使用 ORM,那就编写自己的存储过程,让 ORM 调用存储过程,而不是写自己的查询。

    我知道使用 ORM 的种种理由,也知道开发人员和经理都喜欢 ORM,因为它们有助于产品迅速投向市场。但是如果你看一下查询对数据库做了什么,就会发现代价太高了。

    存储过程有许多优点,首先,你在网络上推送的数据少得多。如果有一个长查询,那么它可能在网络上要往返三四趟才能让整个查询到达数据库服务器。

    这不包括服务器将查询重新组合起来并运行所花的时间;另外考虑这点:查询可能每秒运行几次或几百次。

    使用存储过程可大大减少传输的流量,因为存储过程调用总是短得多。另外,存储过程在 Profiler 或其他任何工具中更容易追踪。

    存储过程是数据库中的实际对象,这意味着相比临时查询(ad-hoc query),获取存储过程的性能统计数字要容易得多,因而发现性能问题、查明异常情况也要容易得多。

    此外,存储过程参数化更一致,这意味着你更可能会重用执行方案,甚至处理缓存问题,要查明临时查询的缓存问题很难。

    有了存储过程,处理边界情况(edge case),甚至增加审计或变更锁定行为变得容易多了。存储过程可以处理困扰临时查询的许多任务。

    几年前,我妻子理清了 Entity Framework 的一个两页长的查询,该查询花了 25 分钟来运行。

    她化繁为简,将这个大型查询改写为 SELECT COUNT(*) fromT1,这不是开玩笑。

    那些只是要点,我知道,许多 .NET 程序员认为业务逻辑不适宜放在数据库中,这大错特错

    如果将业务逻辑放在应用程序的前端,仅仅为了比较就得将所有数据传送一遍,那样不会有好的性能。

    我有个客户将所有逻辑保存在数据库的外面,在前端处理一切。该公司将成千上万行数据发送到前端,以便能够运用业务逻辑,并显示所需的数据。

    这个过程花了 40 分钟,我把存储过程放在后端,让它从前端调用;页面在三秒钟内加载完毕。

    当然,有时逻辑适宜放在前端上,有时适宜放在数据库中,但是 ORM 总是让我上火。

    不要对同一批次的许多表执行大型操作

    这个似乎很明显,但实则不然。我会用另一个鲜活的例子,因为它更能说明问题。

    我有一个系统存在大量的阻塞,众多操作处于停滞状态。结果查明,每天运行几次的删除例程在删除显式事务中 14 个表的数据。处理一个事务中的所有 14 个表意味着,锁定每个表,直到所有删除完成。

    解决办法就是,将每个表的删除分解成单独的事务,以便每个删除事务只锁定一个表。

    这解放了其他表,缓解了阻塞,让其他操作得以继续运行。你总是应该把这样的大事务分解成单独的小事务,以防阻塞。

    不要使用触发器

    这个与前一个大体一样,但还是值得一提。触发器的问题:无论你希望触发器执行什么,都会在与原始操作同一个的事务中执行。

    如果你写一个触发器,以便更新 Orders 表中的行时将数据插入到另一个表中,会同时锁定这两个表,直到触发器执行完毕。

    如果你需要在更新后将数据插入到另一个表中,要将更新和插入放入到存储过程中,并在单独的事务中执行。

    如果你需要回滚,就很容易回滚,不必同时锁定这两个表。与往常一样,事务要尽量短小,每次不要锁定多个资源。

    不要在 GUID 上聚类

    这么多年后,我难以相信我们居然还在为这个问题而苦恼。但我仍然每年遇到至少两次聚类 GUID。

    GUID(全局唯一标识符)是一个 16 字节的随机生成的数字。相比使用一个稳定增加的值(比如 DATE 或 IDENTITY),按此列对你表中的数据进行排序导致表碎片化快得多。

    几年前我做过一项基准测试,我将一堆数据插入到一个带聚类 GUID 的表中,将同样的数据插入到另一个带 IDENTITY 列的表中。

    GUID 表碎片化极其严重,仅仅过了 15 分钟,性能就下降了几千个百分点。

    5 小时后,IDENTITY 表的性能才下降了几个百分点,这不仅仅适用于 GUID,它适用于任何易失性列。

    如果只需查看数据是否存在,就不要计数行

    这种情况很常见,你需要查看数据存在于表格中,根据这番检查的结果,你要执行某个操作。

    我经常见到有人执行 SELECT COUNT(*)FROMdbo.T1来检查该数据是否存在:

    1. SET @CT=(SELECT COUNT(*) FROM
    2. dbo.T1);
    3.    [email protected]>0
    4.    BEGIN
    5.    <Do something>
    6. END

    这完全没必要,如果你想检查数据是否存在,只要这么做:

    1. If EXISTS (SELECT 1 FROM dbo.T1)
    2.  BEGIN
    3.  <Do something>
    4.  END

    不要计数表中的一切,只要取回你找到的第一行。SQL Server 聪明得很,会正确使用 EXISTS,第二段代码返回结果超快。

    表越大,这方面的差距越明显。在你的数据变得太大之前做正确的事情。调优数据库永不嫌早。

    实际上,我只是在我的其中一个生产数据库上运行这个例子,针对一个有 2.7 亿行的表。

    第一次查询用时 15 秒,包含 456197 个逻辑读取,第二次查询不到 1 秒就返回结果,只包含 5 个逻辑读取。

    然而如果你确实需要计数表的行数,表又很大,另一种方法就是从系统表中提取,SELECT rows fromsysindexes 将为你获得所有索引的行数。

    又由于聚类索引代表数据本身,所以只要添加 WHERE indid = 1,就能获得表行,然后只需包含表名称即可。

    所以,最后的查询是:

    1. SELECT rows from sysindexes where object_name(id)=’T1’and indexid =1

    在我 2.7 亿行的表中,不到 1 秒就返回结果,只有 6 个逻辑读取,现在性能不一样了。

    不要进行逆向搜索

    以简单的查询 SELECT * FROMCustomers WHERE RegionID <> 3 为例。

    你不能将索引与该查询结合使用,因为它是逆向搜索,需要借助表扫描来逐行比较。

    如果你需要执行这样的任务,可能发现如果重写查询以使用索引,性能会好得多。

    该查询很容易重写,就像这样:

    1. SELECT * FROM Customers WHERE RegionID<3 UNION ALL SELECT * FROM Customers WHERE RegionID

    这个查询将使用索引,所以如果你的数据集很大,其性能会远胜过表扫描版本。

    当然,没有什么是那么容易的,也许性能更糟,所以使用之前先试一下。它百分之百管用,虽然涉及太多的因素。

    最后,我意识到这个查询违反了第 4 条规则:不要查询两次,但这也表明没有硬性规则。虽然我们在这里查询两次,但这么做是为了避免开销很大的表扫描。

    你无法一直运用所有这些技巧,但如果牢记它们,有一天你会用它们来解决一些大问题。

    要记住的最重要一点是,别将我说的话当成教条。在你的实际环境中试一下,同样的解决办法不是在每种情况下都管用,不过我排查糟糕的性能时一直使用这些方法,而且屡试不爽

    转自:https://www.tuicool.com/articles/ruqiQfi

    展开全文
  • Oracle提高SQL执行效率的三种方法

    千次阅读 2019-01-18 16:54:21
    提高SQL在Oracle里的执行效率,优化应用的业务逻辑是最主要的,从技术层面来讲Oracle也提供了一些方法,今天主要讲3种: Oracle提供了多种方法用于减少花在剖析OracleSQL表达式上的时间,在执行带有大量执行计划...

    要提高SQL在Oracle里的执行效率,优化应用的业务逻辑是最主要的,从技术层面来讲Oracle也提供了一些方法,今天主要讲3种:

    Oracle提供了多种方法用于减少花在剖析OracleSQL表达式上的时间,在执行带有大量执行计划的复杂查询时剖析过程会拖累系统的性能。现在我们来简要地看看这些方法中的几种。

    1、使用ordered提示

    Oracle必须花费大量的时间来剖析多表的合并,用以确定表合并的最佳顺序。如果SQL表达式涉及七个乃至更多的表合并,那么有时就会需要超过30分钟的时间来剖析,因为Oracle必须评估表合并所有可能的顺序。八个表就会有40,000多种顺序。Ordered这个提示(hint)和其他的提示一起使用能够产生合适的合并顺序。

    Ordered这个提示会要求列在SQL表达式FROM字句里的表按照指定的顺序进行合并,FROM字句里的第一个表会指定驱动表格(drivingtable)。驱动表格应该是返回最小行数的表格。使用ordered提示会跳过非常耗时和耗资源的剖析操作,并加快Oracle SQL的执行。

    Listing A如下:

    以下是引用片段:

    Listing A

    select /*+ ordered use_nl(b) parallel(e, 4) */ e.ename, hiredate, b.comm

    from emp e,bonus b

    where e.ename = b.ename ;

    ListingA里是一个复杂查询的例子,这个查询被强制进行一个嵌套循环,从而与对emp表格进行的并行查询合并。要注意,我已经使用ordered提示来引导Oracle去按照FROM子句所列出的顺序来评估表格。

    2、使用ordered_predicates

    ordered_predicates提示在查询的WHERE子句里指定的,并被用来指定布尔判断(Booleanpredicate)被评估的顺序。在没有ordered_predicates的情况下,Oracle会使用下面这些步骤来评估SQL判断的顺序:

    子查询的评估先于外层WHERE子句里的Boolean条件。

    所有没有内置函数或者子查询的布尔条件都按照其在WHERE子句里相反的顺序进行评估,即最后一条判断最先被评估。

    每个判断都带有内置函数的布尔判断都依据其预计的评估值按递增排列。

    你可以使用ordered_predicates提示来强制取代这些缺省的评估规则,那么你WHERE子句里的项目就会按照其在查询里出现的顺序被评估。在查询的WHERE子句里使用了PL/SQL函数的情况下,通常会使用ordered_predicates提示。如果你知道限制最多的判断并且希望Oracle最先评估这些判断的时候,在这种情况下,它也是非常有用的。用法提示:你不能使用ordered_predicates提示来保存对索引键进行判断评估的顺序。

    create table t1(v1,n1,n2) as select to_char(mod(rownum,20)),rownum,mod(rownum,20) from all_objects where rownum<=3000;

    SQL> execute dbms_stats.gather_tabLE_stats('HR','T1');

    SQL> select

    2 v1,n2,n1

    3 from t1

    4 where v1=1

    5 and n2=18

    6 and n1=998

    7 ;

    no rows selected

    Execution Plan

     

    Plan hash value: 3617692013

     

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

     

    | 0 | SELECT STATEMENT | | 1 | 10 | 4 (0)| 00:00:01 |

    |* 1 | TABLE ACCESS FULL| T1 | 1 | 10 | 4 (0)| 00:00:01 |

     

    Predicate Information (identified by operation id):

     

    1 - filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)

    SQL> select /*+ ordered_predicates */

    2 v1,n2,n1

    3 from t1

    4 where v1=1

    5 and n2=18

    6 and n1=998

    7 ;

    no rows selected

    Execution Plan

     

    Plan hash value: 3617692013

     

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

     

    | 0 | SELECT STATEMENT | | 1 | 48 | 4 (0)| 00:00:01 |

    |* 1 | TABLE ACCESS FULL| T1 | 1 | 48 | 4 (0)| 00:00:01 |

     

    Predicate Information (identified by operation id):

     

    1 - filter(TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998)

    3、限制表格合并评估的数量

    提高SQL剖析性能的最后一种方法是强制取代Oracle的一个参数,这个参数控制着在评估一个查询的时候,基于消耗的优化器所评估的可能合并数量。

    _optimizer_search_limit这个参数会指定表格合并组合的最大数量,后者将会在Oracle试图确定合并多表格最佳方式的时候被评估。这个参数有助于防止优化器花更多的时间来评估可能的合并顺序,而不是把时间花在寻找最佳合并顺序上。_optimizer_search_limit还控制着用于调用starjoin提示的阙值,当查询里的表格数量低于_optimizer_search_limit(其缺省的值是5)的时候,star提示就会被光顾。

    展开全文
  • 提高SQL执行效率的方法

    千次阅读 2013-09-19 02:02:01
    Oracle提供了多种方法用于减少花在剖析Oracle SQL表达式上的时间,在执行带有大量执行计划的复杂查询时剖析过程会拖累系统的性能。现在我们来简要地看看这些方法中的几种。  1、使用ordered提示  Oracle必须...
  • 如果服务器存在多个cpu的话,我们就可以使用parallel进行并行执行某个查询,插入操作的sql,这样可以大大提高sql执行效率,具体使用几个并行的进程,可以设置process count = cpu count;  执行并行查询,一种在直接在...
  • Oracle提高sql执行效率的建议(2)

    千次阅读 2013-12-21 00:57:05
    如果把语句改成select * from customer where zipcode>"98000",在执行查询时就会利用索引来查询,显然会大大提高速度; 尽量明确的完成SQL语句,尽量少让数据库工作。比如写SELECT语句时,需要把查询的字段明确...
  • 提高SQL执行效率的几点建议

    千次阅读 2007-08-29 18:40:00
    如果把语句改成select * from customer where zipcode>"98000",在执行查询时就会利用索引来查询,显然会大大提高速度; 尽量明确的完成SQL语句,尽量少让数据库工作。比如写SELECT语句时,需要把查询的字段明确...
  • sql执行效率提高

    2013-10-03 21:27:35
    使你的 SQL 语句完全优化,提高sql执行效率
  • 提高SQL执行效率的几点建议: ◆尽量不要在where中包含子查询; 关于时间的查询,尽量不要写成:where to_char(dif_date,yyyy-mm-dd)=to_char(2007-07-01,yyyy-mm-dd); ◆在过滤条件中,可以过滤掉最大数量记录的...
  • sql执行效率

    2008-06-14 10:48:01
    提高sql执行效率几种方法
  • 提高Oracle中SQL执行效率提高Oracle中SQL执行效率
  • 我们用开发sql的时候会发现,有时候asp执行比较慢,我们可以用下面的方法,来提高执行效率
  • SQL书写技巧-提高执行效率,有需要的可以直接下载
  • 提高oracle的sql执行效率,优化性能,通过oracle表分析功能进行调整,能加快sql查询效率2倍以上
  • 复杂SQL拆分优化  拆分SQL是性能优化一种非常有效的方法之一,  具体就是将复杂的SQL按照一定的逻辑逐步分解成简单的SQL... 我们来通过一个案例来分析,为什么拆分语句可以提高SQL执行效率,更重要的是弄清
  • 对于做管理系统和分析系统的程序员,复杂SQL语句是不可避免的,面对海量数据,有时候经过优化的某一条语句,可以提高执行效率和整体运行性能。 如何选择SQL语句,本文提供了两种方法,分别对多条SQL进行量化的分析...
  • SQL执行提高效率的多种方法 SQL执行提高效率的多种方法
  • 在平时的日常开发中,由于SQL语句使用不当,执行效率低下,现整理了文档供大家参考,尽可能帮忙新手,实现资源共享;
  • 提高sql语句执行效率及索引

    千次阅读 2015-09-23 15:16:54
    往往牺牲了执行效率 . 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的。  ( 14 ) 使用表的别名 (Alias) :  当在 SQL 语句中连接多个表时 , 请使用表的别名并把别名前缀于每个 ...
  • 优化sql语句提高oracle执行效率(34种方法) 优化sql语句提高oracle执行效率(34种方法)
  • 如何提高sql语句的执行效率

    千次阅读 2017-12-04 09:59:37
    1. SQL优化的原则是:将一次操作需要读取的BLOCK数减到最低,即在最短的时间达到最大的数据吞吐量。 调整不良SQL通常可以从以下几点切入: ? 检查不良的SQL,考虑其写法是否还有可优化内容 ? 检查子查询 考虑SQL子...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 213,312
精华内容 85,324
关键字:

提高sql执行效率