carfield.com.hk

Note of SQL performance tuning.txt

2023-02-01T02:37:46Z

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

16) Prevent database level transaction, prevent joining, prevent locking ( like auto increment key ), pretty good suggestion - http://www.aviransplace.com/2015/08/12/mysql-is-a-great-nosql/

17) Reduce dataset for aggregation - https://www.periscopedata.com[..]-subqueries-to-count-distinct-50x-faster

Few good tips, like avoid cursors - http://www.javaworld.com[..]ent/21-rules-for-faster-sql-queries.html

7 Tips for Query Optimization in SQL Servers - https://learningdaily.dev[..]optimization-in-sql-servers-fb3653c910f1

index.txt

2021-06-24T14:57:56Z

Case study about how to find out suitable index - http://samsaffron.com[..]e+life+of+a+slow+page+at+Stack+Overflow#

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

script to check if index is missing at foreign key


select table_name, constraint_name,
       cname1 || nvl2(cname2,','||cname2,null) ||
       nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
       nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
       nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
              columns
    from ( select b.table_name,
                  b.constraint_name,
                  max(decode( position, 1, column_name, null )) cname1,
                  max(decode( position, 2, column_name, null )) cname2,
                  max(decode( position, 3, column_name, null )) cname3,
                  max(decode( position, 4, column_name, null )) cname4,
                  max(decode( position, 5, column_name, null )) cname5,
                  max(decode( position, 6, column_name, null )) cname6,
                  max(decode( position, 7, column_name, null )) cname7,
                  max(decode( position, 8, column_name, null )) cname8,
                  count(*) col_cnt
             from (select substr(table_name,1,30) table_name,
                          substr(constraint_name,1,30) constraint_name,
                          substr(column_name,1,30) column_name,
                          position
                     from sys.user_cons_columns ) a,
                  sys.user_constraints b
            where a.constraint_name = b.constraint_name
              and b.constraint_type = 'R'
            group by b.table_name, b.constraint_name
         ) cons
   where col_cnt > ALL
           ( select count(*)
               from sys.user_ind_columns i
              where i.table_name = cons.table_name
                and i.column_name in (cname1, cname2, cname3, cname4,
                                      cname5, cname6, cname7, cname8 )
                and i.column_position <= cons.col_cnt
              group by i.index_name
           )

http://chriswongdevblog.blogspot.com.au[..]01/dont-forget-to-index-your-oracle.html

Explain how postgres indexes work, look like combine index is not that useful - http://dublintech.blogspot.com.au/2015/07/postgres-indexes.html

SQL Server JDBC – Set sendStringParametersAsUnicode to false - https://vladmihalcea.com[..]rver-jdbc-sendstringparametersasunicode/

numa.txt

2017-11-03T01:41:42Z

https://technet.microsoft.com[..]m/en-us/library/ms345403(v=sql.105).aspx

https://code.kx.com/q/cookbook/linux-production/

http://jcole.us[..]swap-insanity-and-the-numa-architecture/

architecture.txt

2017-09-22T07:29:28Z

IT Hare: Ultimate DB Heresy: Single Modifying DB Connection. Part I. Performanc - http://highscalability.com[..]y-single-modifying-db-connection-pa.html

How yandex.metrica improve DB reading - http://highscalability.com[..]of-data-structures-in-yandexmetrica.html

JDBC performance tips.txt

2016-02-01T04:26:28Z

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/

http://blog.jooq.org[..]iting-sql-you-wont-believe-the-last-one/

Statement.getWarnings() is expensive - https://plumbr.eu[..]-doubled-our-jdbc-traffic-with-hibernate

embedded.txt

2013-01-06T11:22:03Z

Compare the performance of LevelDB, BDB and BangDB, it sound like BangDB is best... but this article from BangDB - http://highscalability.com[..]ldb-berkley-db-and-bangdb-for-rando.html

parameter sniffing.txt

2011-06-30T16:34:29Z

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.

count.txt

2011-04-29T01:34:07Z

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

sharding.txt

2009-03-27T15:02:05Z

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/

MaterializedView.txt

2009-03-27T03:49:44Z

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