Tue Aug 21 23:16:39 HKT 2007 From /weblog/database/performance

JDBC performance tips

Some tips, like use of addBatch()

http://rgarg.blogspot.com/2005/10/jdbc-performance-pointers.html
http://www.theserverside.com[..]ent/JDBCPerformance_PartIII/article.html

Nice discussion about preparestatement caching

http://www.theserverside.com/news/thread.tss?thread_id=27528 http://www.ibm.com/developerworks/java/library/j-jdbcnew/

(google search) (amazon search)

Sat Mar 03 03:01:17 HKT 2007 From /weblog/database/performance

index

A simple example of how to use EXPLAIN to find out the bottleneck of the query and add suitable index to suitable column - http://hackmysql.com/case4

Bitmap Index vs. B-tree Index: Which and When? - http://www.oracle.com/technology/pub/articles/sharma_indexes.html http://publib.boulder.ibm.com[..]/v5r3/index.jsp?topic=/rzajq/perf21c.htm

(google search) (amazon search)

Mon Jun 12 19:54:27 HKT 2006 From /weblog/database/performance

Note of SQL performance tuning

Note of this book - http://www.ocelot.ca/tuning.htm

1) Order of "and" and "or" is matter, if no cost-base optimizer

where column1 = 'a' and column2 = 'b' -> where column2 = 'b' and column1 = 'a' if column2 'b' is less likely

2) <> is more expensive than =

3) Can force to use index if SELECT * FROM table WHERE indexed_column > 0

4) UPPER(column) might lose information, use LOWER is better

5) WHERE column = 'WORD' or column = 'word' is always faster than WHERE LOWER(column) = 'word' , even faster version is test if database if case-insensitive: WHERE column = 'WORD' or ('WORD' <> 'word' AND column = 'word')

6) 32bit integer is the fastest datatype for 32bit OS

7) column in (?, ?) is faster than column =? or column = ? for some database, and don't slow in any database

8) For aggregate functions, prevent using having, try to use fewer columns in group by and use subquery if possible

9) In fact, count(1) is not faster than count(*)

10) CAST before aggregate function: SELECT CAST(SUM(column) AS INT) -> SELECT SUM(CAST(column as INT))

11) Use constant as much as possible, e.g.: select * from t1, t2 where t1.c1 = t2.c1 and t1.c1 = 1 -> select * from t1, t2 where t1.c1=1 andn t2.c1=1

12) Information of Join vs Subqueries

13) Information of optimum column type (VARCHAR, TIMESTAMP, WORD size INT and CHAR(1) usually better), shift effect for page

14) prevent NULL for porting issue

15) Low level table physical information

(google search) (amazon search)