当前位置:首页 > 开发 > 数据库 > 正文

Sybase性能调优建议清单

发表于: 2015-03-17   作者:darrenzhu   来源:转载   浏览次数:
摘要: 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 m
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
  • 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?

Sybase性能调优建议清单

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
Sybase数据库性能调优:http://www.soft6.com/know/detail.asp?id=AJIDDA 广铁集团电算信息中心 王奇
本文主要分享自己在appstore项目中的性能调优点,包括同步改异步、缓存、Layout优化、数据库优化、
部分转自:http://www.cnblogs.com/luckybird/archive/2012/06/11/2544753.html 及http://www.cnblo
http://www.sina.com.cn 2009年10月09日 21:16 IT168.com 文本Tag: 性能测试   【IT168 评论】1.
关于性能优化这是一个比较大的话题,在《由12306.cn谈谈网站性能技术》中我从业务和设计上说过一些
转载自http://coolshell.cn/articles/7490.html 关于性能优化这是一个比较大的话题,在《由12306.cn
jvm性能调优 --------------------------------、 你们的堆栈是怎么记住的啊,stack是栈,栈比堆笔
转: 关于性能优化这是一个比较大的话题,在《由12306.cn谈谈网站性能技术》中我从业务和设计上说过
对于前端的性能优化涉及到QPS三要素、优化模版、优化大数据处理、优化JVM参数设置 一、QPS三要素 1
1 vmstat iostat netstat (监控工具) 2 JMeter(https://jmeter.apache.org/) Gatling(参考http://w
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号