Fri Jul 01 00:34:29 HKT 2011
From
/weblog/database/performance
When the query plan is compiled on first run of the procedure after a SQL Server restart, the query is optimised for those initial variables in that initial call.
There’s ways to write the procedure to have a more representative query plan chosen on initial compile each time:
http://blogs.msdn.com[..]ryoptteam/archive/2006/03/31/565991.aspx This is what is known as parameter sniffing.
(google search)
(amazon search)
Fri Apr 29 09:34:07 HKT 2011
From
/weblog/database/performance
Tip of having faster 'select count(*) from table' of sql server, probably can use this as reference to check similar feature on other DB, if that hurt something -
http://beyondrelational.com[..]are-there-any-alternatives-to-count.aspx
(google search)
(amazon search)
Mon Dec 07 12:01:15 HKT 2009
From
/weblog/database/performance
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
Some related links -
http://www.techartifact.com[..]9/12/sql-optimization-tipsquestions.html
(google search)
(amazon search)
Fri Mar 27 23:02:05 HKT 2009
From
/weblog/database/performance
An idea of database sharding, basically replicate a unified version for operation required global access -
http://itsfrosty.wordpress.com[..]com/2009/03/20/database-sharding-basics/
(google search)
(amazon search)
Fri Mar 27 11:49:44 HKT 2009
From
/weblog/database/performance
Oracle can help us to denormalize tables according to query via materialized view, should help performance a lot, probably there is similar feature in other DBMS -
http://www.oracle.com[..]ology/products/oracle9i/daily/jul05.html
(google search)
(amazon search)