- 浏览: 802401 次
- 性别:
- 来自: 上海
文章分类
- 全部博客 (360)
- Java (101)
- JPA/Hibernate (10)
- Spring (14)
- Flex/BlazeDS (37)
- Database (30)
- Lucene/Solr/Nutch (0)
- Maven/Ant (25)
- CXF/WebService (3)
- RPC/RMI/SOAP/WSDL (1)
- REST (6)
- TDD/BDD/JUnit (1)
- Servlet/JSP (2)
- AI/MachineLearning (3)
- Resource (1)
- 字符编码 (2)
- OOA/OOPS/UML (5)
- DesignPattern (8)
- 算法与数据结构 (11)
- Web&App Server (13)
- 并发&异步&无阻塞 (7)
- Entertainment (4)
- JavaScript/ExtJS (45)
- CodeStyle&Quality (1)
- svn/git/perforce (8)
- JSON (2)
- JavaScriptTesting (4)
- Others (6)
- RegularExpression (2)
- Linux/Windows (12)
- Protocal (2)
- Celebrities (1)
- Interview (1)
- 计算机语言 (1)
- English (2)
- Eclipse (5)
- TimeZone/时区 (1)
- Finance (1)
- 信息安全 (1)
- JMS/MQ (2)
- XSD/XML/DTD (3)
- Android (4)
- 投资 (3)
- Distribution (3)
- Excel (1)
最新评论
-
qdujunjie:
如果把m换成具体的数字,比如4或者5,会让读者更明白
m阶B树中“阶”的含义 -
java-admin:
不错,加油,多写点文章
关于Extjs的mixins和plugin -
xiehuaidong880827:
你好,我用sencha cmd打包完本地工程后,把app.js ...
ExtJS使用Sencha Cmd合并javascript文件为一个文件 -
KIWIFLY:
lwpan 写道inverse = "true&qu ...
Hibernate中什么时候使用inverse=true -
luedipiaofeng:
good
消除IE stop running this script弹出框
http://stackoverflow.com/questions/18783/favourite-performance-tuning-tricks
Here is the handy-dandy list of things I always give to someone asking me about optimisation.
We mainly use Sybase, but most of the advice will apply across the board.
SQL Server, for example, comes with a host of performance monitoring / tuning bits, but if you don't have anything like that (and maybe even if you do) then I would consider the following...
99% of problems I have seen are caused by putting too many tables in a join. The fix for this is to do half the join (with some of the tables) and cache the results in a temporary table. Then do the rest of the query joining on that temporary table.
Query Optimisation Checklist
Here is the handy-dandy list of things I always give to someone asking me about optimisation.
We mainly use Sybase, but most of the advice will apply across the board.
SQL Server, for example, comes with a host of performance monitoring / tuning bits, but if you don't have anything like that (and maybe even if you do) then I would consider the following...
99% of problems I have seen are caused by putting too many tables in a join. The fix for this is to do half the join (with some of the tables) and cache the results in a temporary table. Then do the rest of the query joining on that temporary table.
Query Optimisation Checklist
- Run UPDATE STATISTICS on the underlying tables
- Many systems run this as a scheduled weekly job
- Delete records from underlying tables (possibly archive the deleted records)
- Consider doing this automatically once a day or once a week.
- Rebuild Indexes
- Rebuild Tables (bcp data out/in)
- Dump / Reload the database (drastic, but might fix corruption)
- Build new, more appropriate index
- Run DBCC to see if there is possible corruption in the database
- Locks / Deadlocks
- Ensure no other processes running in database
- Especially DBCC
- Are you using row or page level locking?
- Lock the tables exclusively before starting the query
- Check that all processes are accessing tables in the same order
- Are indices being used appropriately?
- Joins will only use index if both expressions are exactly the same data type
- Index will only be used if the first field(s) on the index are matched in the query
- Are clustered indices used where appropriate?
- range data
- WHERE field between value1 and value2
- Small Joins are Nice Joins
- By default the optimiser will only consider the tables 4 at a time.
- This means that in joins with more than 4 tables, it has a good chance of choosing a non-optimal query plan
- Break up the Join
- Can you break up the join?
- Pre-select foreign keys into a temporary table
- Do half the join and put results in a temporary table
- Are you using the right kind of temporary table?
- #temp tables may perform much better than @table variables with large volumes (thousands of rows).
- Maintain Summary Tables
- Build with triggers on the underlying tables
- Build daily / hourly / etc.
- Build ad-hoc
- Build incrementally or teardown / rebuild
- See what the query plan is with SET SHOWPLAN ON
- See what’s actually happenning with SET STATS IO ON
- Force an index using the pragma: (index: myindex)
- Force the table order using SET FORCEPLAN ON
- Parameter Sniffing:
- Break Stored Procedure into 2
- call proc2 from proc1
- allows optimiser to choose index in proc2 if @parameter has been changed by proc1
- Can you improve your hardware?
- What time are you running? Is there a quieter time?
- Is Replication Server (or other non-stop process) running? Can you suspend it? Run it eg. hourly?
发表评论
-
mysql,oracle,sql server中的默认事务隔离级别
2018-08-02 17:03 696mysql,oracle,sql server中的默认事务隔离 ... -
创建前缀索引报长度超出错误
2018-07-25 15:44 1723表结构定义如下: CREATE TABLE `sku` ( ` ... -
Mysql Varchar字符长度
2018-07-25 15:23 1348`sku_name` VARCHAR(200) NOT NUL ... -
Mysql分表和分区的区别、分库分表介绍与区别
2017-06-14 20:32 1841Mysql分表和分区的区别、分库分表介绍与区别 http:// ... -
Nested Loop Join和子查询
2017-06-03 20:56 727这2个是不同的概念,不要混淆在一起了 Nested Loop ... -
oracle中rownum和rowid的区别
2017-06-03 20:55 1000oracle中rownum和rowid的区 ... -
分布式事务XA,JTA,两阶段提交,BASE
2016-06-27 21:45 5674关于分布式事务、两阶段提交、一阶段提交、Best Effort ... -
大数据技能图谱
2016-03-24 13:33 861http://mp.weixin.qq.com/s?__biz ... -
Java 方法(JdbcTransactionTemplate)与存储过程共享同一个事务
2016-01-12 19:24 1706如果要让java来管理事务,那么在存储过程里不能写提交(com ... -
Java 平台开发有效事务策略,数据库事务性能
2016-01-11 15:42 868Java 平台开发有效事务策略系列文章 http://www. ... -
SQL语句的where字句表达式顺序影响性能吗?
2015-09-09 13:45 1947比如下面的SQL语句性能有区别吗? select * from ... -
Sybase database Transaction mode: chained and unchained
2015-09-02 10:15 1444Support for Sybase database Tra ... -
聚集索引,非聚集索引,主键,索引类型及实现方式
2015-08-06 11:31 813聚集索引和非聚集索引(整理) http://www.cnblo ... -
Sybase SQL性能诊断
2015-03-17 11:19 1696Performance and Tuning Series: ... -
left join时on条件与where条件的区别
2015-03-09 17:46 3427参考文章:http://cqujsjcyj.iteye.com ... -
Sybase常用命令,SQL语句
2014-10-22 16:44 4992SQL online sample http://sqlzoo ... -
数据库系统基本概念
2014-07-02 18:47 1372数据模型(Data Model)是描述数据、数据联系、数据语义 ... -
乐观锁与悲观锁
2014-06-09 11:57 658Key points: 悲观锁的实现,往往依靠数据库提供的锁机 ... -
数据库事务,锁,隔离级别(Isolation Level)
2014-06-09 11:44 2189数据库的隔离级别2(repeaable read)可实现重 ... -
我的Oracle学习笔记
2014-05-18 13:31 0以前工作中整理的oracle学习笔记,虽然有些凌乱,但是时候自 ...
相关推荐
### Sybase性能调优知识点详解 #### 一、概述 Sybase是一种高性能的企业级数据库管理系统,在许多企业级应用中有着广泛的应用。随着业务量的增长,系统可能会遇到性能瓶颈,因此进行有效的性能调优是非常必要的。...
《Sybase性能调优手册》是一份针对Sybase 12.5版本的数据库系统进行性能优化的专业指导手册。Sybase Adaptive Server Enterprise (ASE),作为一款高性能的企业级数据库管理系统,其性能调优对于确保数据库系统的稳定...
下面,我们将深入探讨“sybaseIQ性能调优”这一主题,主要基于提供的文档名称,如“sybaseIQ性能优化.doc”和“SybaseIQ性能调优.doc”。 1. **理解Sybase IQ架构**:Sybase IQ采用列式存储方式,这使得对大量数据...
数据库性能一般用两个方面的指标来衡量:响应时间和吞吐量。响应越快,吞吐量越大,数据库性能越好。响应时间和吞吐量有些情况下不能一起得到改善。
调优性能 .......................................................................................... 3 调优级别 ................................................................................... 4 ...
详细介绍sybase调优的方法。从内存(cache)配置和锁机制详细阐述性能调优的原理、方法和步骤。
### SYBASE ASE 性能调优详解 #### 一、性能定义及度量方法 **性能**是指在特定环境中衡量应用程序或系统的效率。对于Sybase Adaptive Server Enterprise (ASE) 来说,性能主要包括两个方面: 1. **响应时间**:...
sybase数据库性能调优原理介绍 系列ppt之一
### Sybase数据库系统性能调优分析 在当前信息化高速发展的时代背景下,数据库系统作为企业核心应用的基础支撑之一,其性能的高低直接影响到企业的业务效率与用户体验。Sybase数据库作为一种广泛应用于金融、电信等...
《Sybase ASE 调优手册3》是针对Sybase Adaptive Server Enterprise(ASE)数据库系统进行性能优化的专业指南。ASE是Sybase公司的一款高性能的关系型数据库管理系统,广泛应用于企业级应用,尤其在金融、电信等领域...
《Sybase ASE 调优手册2》是针对Sybase Adaptive Server Enterprise(ASE)数据库系统的一本专业参考资料,旨在帮助数据库管理员和开发人员优化数据库性能,提升系统效率。ASE是Sybase公司推出的企业级关系型数据库...
《Sybase数据库系统性能调优》 Sybase数据库性能调优是提升系统效率的关键环节,其目标在于降低对系统公共资源的争夺,确保系统的稳定高效运行。优化工作主要涉及四个层次:服务器层、数据库层、应用层和运行环境层...
Sybase数据库系统的性能调优是一个复杂而重要的任务,旨在减少系统资源的争用,提升整体运行效率。这个过程涵盖了多个层次,包括服务器层、数据库层、应用层以及运行环境层。 1. **服务器层优化** - **内存分配**...
### SYBASE性能及调优指南 Volume 3 #### 第三卷:监控和分析性能工具 在本卷中,我们将深入探讨SYBASE数据库系统的性能监控与分析技术。这不仅包括了如何获取关键的统计信息,还涵盖了如何利用这些信息来监控并...
### SYBASE性能及调优指南 Volume 1 #### 基础知识篇 在深入了解Adaptive Server中的性能问题之前,我们需要掌握一系列的基础知识。这部分内容将帮助我们更好地理解数据库性能优化的基本概念和技术。 ##### 一、...
### Sybase IQ 12.6 性能调优手册知识点详析 #### 一、引言 在数据库管理系统(DBMS)领域中,Sybase IQ 是一款高性能、面向列的数据库系统,以其卓越的数据处理能力和高效的数据压缩技术著称。Sybase IQ 12.6 ...
### Sybase ASE15 性能调优基本概念与实践 #### 一、数据库性能调优的基本概念 ##### 1.1 什么是性能 - **定义**:性能是指在特定环境下,应用程序或系统的运行效率。这包括单个应用程序的运行效率,以及多个应用...
### Sybase ASE数据库性能调优知识点详解 #### 一、概览 - **标题与描述**: “Sybase ASE数据库性能调优”旨在介绍如何优化Sybase Adaptive Server Enterprise (ASE) 数据库系统的性能,以实现更高效的数据管理和...
【Sybase IQ 性能调优】 Sybase IQ 是一种高效的数据仓库和分析数据库系统,其性能调优是确保系统高效运行的关键环节。本教程主要涵盖以下几个方面: 1. **性能调优的基本理论**: - 理解性能调优的重要性,包括...