Improving SQL efficiency using CASE
来源:互联网 发布:单片机温度传感器设计 编辑:程序博客网 时间:2024/06/05 05:12
Introduction
Some time ago I wrote "The Power of Decode", a paper on using the DECODE function to improve report performance. I was aware at the time that DECODE was being replaced by CASE but wanted to make sure that the paper applied to as many Oracle versions as possible. CASE was introduced in Oracle 8.1.6, however, and is a much better option because it is
1) More flexible than DECODE
2) Easier to read
3) ANSI-compatible (if that matters to you)
However, CASE is essentially a better implementation of DECODE so the reasons for using either are similar. In this article I'll focus on improving application performance by improving the efficiency of your code. One of the first and most valuable lessons I learnt about Oracle performance is to do as much work in as few steps as possible. The Oracle server engine is designed to handle large data sets efficiently but sometimes developers try to break them up into smaller discrete pieces of work (the row-by-row approach). I suspect that they feel they have more control this way and it maps on to a typical developer's procedural approach, but it normally isn't the most efficient way of accessing an Oracle database.
I often see reports developed using reporting tools or by embedding SQL in other languages, that include several SQL statements accessing the same tables in slightly different ways to retrieve individual pieces of data in the report layout. Each of the individual SQL statements is a separate request to the database and causes work at the server end.
To give you a trivial example, why do this?
SELECT deptno, SUM(sal)
FROM emp WHERE deptno = 10
GROUP BY deptno;
SELECT deptno, SUM(sal)
FROM emp WHERE deptno = 20
GROUP BY deptno;
When you could retrieve the same results using this.
SELECT deptno, SUM(sal)
FROM emp WHERE deptno IN (10,20)
GROUP BY deptno;
Any technique that offers the possibility of using fewer SQL statements to achieve the same end result may have a beneficial effect on performance. Analytic functions can be a big help in this area but CASE and DECODE have their place too.
- Improving SQL efficiency using CASE
- 【每周论文】Heracles: Improving Resource Efficiency at Scale
- Improving SQL Server Performance
- improving sql server performance
- Using the CASE Expression in SQL Queries @ JDJ
- Improving Software Security Analysis using Exploitation Properties
- Improving Software Security Analysis using Exploitation Properties
- SQL CASE
- Sql case
- sql case
- SQL case
- SQL Case
- sql case
- SQL-CASE
- sql case
- Improving Flex application performance using the Flash Player cache
- Beyond Frontal Faces: Improving Person Recognition Using Multiple Cues
- 《Improving Backgroud Subtraction using local binary similarity patterns》
- Win32汇编教程四
- 2006-11-23
- SQL Server in Windows 2003
- 让系统脱胎换骨 WinXP优化精湛10招
- 成功实施SOA的10大要素
- Improving SQL efficiency using CASE
- Rename DB with NID
- Win32汇编教程五
- C# 类中属性和成员变量的使用
- C++面试的一些总结
- Win32汇编教程六
- SQL Server2000 未公开的存储过程(1)
- SQL Server2000 未公开的存储过程(2)
- 开博宣言