www.carfield.com.hkmisc.txt2024-09-14T13:01:40Z2024-09-14T13:01:40Z<br/>How to do limit resultset correctly - <a href="http://blog.jooq.org/2014/06/09/stop-trying-to-emulate-sql-offset-pagination-with-your-in-house-db-framework/">http://blog.jooq.org[..]ination-with-your-in-house-db-framework/</a> <br/><br/>Using function result as table - <a href="http://blog.jooq.org/2014/07/07/postgresqls-table-valued-functions/">http://blog.jooq.org[..]7/07/postgresqls-table-valued-functions/</a> <br/><br/>Sample of using pivot - <a href="http://blog.jooq.org/2014/08/05/are-you-using-sql-pivot-yet-you-should/">http://blog.jooq.org[..]/are-you-using-sql-pivot-yet-you-should/</a> and unpivot - <a href="http://blog.jooq.org/2016/01/18/impress-your-coworkers-by-using-sql-unpivot/">http://blog.jooq.org[..]ess-your-coworkers-by-using-sql-unpivot/</a> <br/><br/>Sample of using intersect - <a href="http://blog.jooq.org/2015/08/04/intersect-the-underestimated-two-way-in-predicate/">http://blog.jooq.org[..]the-underestimated-two-way-in-predicate/</a> <br/><br/>Using temp table, rownum, and CTE - <a href="http://javarevisited.blogspot.hk/2016/07/how-to-remove-duplicate-rows-from-table-SQL-database.html">http://javarevisited.blogspot.hk[..]licate-rows-from-table-SQL-database.html</a> <br/><br/><a href="http://www.sohamkamani.com/blog/2016/07/07/a-beginners-guide-to-sql/">http://www.sohamkamani.com[..]log/2016/07/07/a-beginners-guide-to-sql/</a> <br/><br/>10 Quick SQL Tips After Writing Daily in SQL for 3 Years - <a href="https://towardsdatascience.com/10-quick-sql-tips-after-writing-daily-in-sql-for-3-years-37bdba0637d0">https://towardsdatascience.com[..]ng-daily-in-sql-for-3-years-37bdba0637d0</a> <br/><br/>Free books - <a href="http://www.java67.com/2017/08/5-free-sql-books-for-beginners-and-experienced-pdf-download.html">http://www.java67.com[..]inners-and-experienced-pdf-download.html</a> <br/><br/>9 SQL tricks you may not know about - <a href="https://meganlieu.substack.com/p/9-sql-tricks-you-may-not-know-about">https://meganlieu.substack.com[..]om/p/9-sql-tricks-you-may-not-know-about</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2024-09-14T13:01:40Zredshift.txt2024-05-03T13:19:43Z2024-05-03T13:19:43Z<br/>All insights from Redshift academic paper: Amazon Redshift re-invented in 2022 - <a href="https://blog.det.life/i-spent-another-8-hours-understanding-the-design-of-amazon-redshift-heres-what-i-found-85c31a59fd19">https://blog.det.life[..]redshift-heres-what-i-found-85c31a59fd19</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2024-05-03T13:19:43Ztransaction.txt2024-03-17T13:03:38Z2024-03-17T13:03:38Z<br/>Transaction, same as all the concept in computing, is an approach and trade-off - <a href="http://www.allthingsdistributed.com/2007/12/eventually_consistent.html">http://www.allthingsdistributed.com[..]d.com/2007/12/eventually_consistent.html</a> <br/><br/>Transaction file system - <a href="http://www.infoq.com/news/2008/01/file-systems-transactions">http://www.infoq.com/news/2008/01/file-systems-transactions</a> <a href="http://myjavatricks.com/jtfs.aspx">http://myjavatricks.com/jtfs.aspx</a> <br/><br/>Explanation about database isolation level - <a href="http://highscalability.com/blog/2011/2/10/database-isolation-levels-and-their-effects-on-performance-a.html">http://highscalability.com[..]-and-their-effects-on-performance-a.html</a> <a href="https://begriffs.com/posts/2017-08-01-practical-guide-sql-isolation.html">https://begriffs.com[..]08-01-practical-guide-sql-isolation.html</a> <br/><br/>A nice article explain about transaction process - <a href="http://www.theserverside.com/articles/article.tss?l=Nuts-and-Bolts-of-Transaction-Processing">http://www.theserverside.com[..]Nuts-and-Bolts-of-Transaction-Processing</a> <br/><br/><a href="http://vladmihalcea.com/2014/01/05/a-beginners-guide-to-acid-and-database-transactions/">http://vladmihalcea.com[..]guide-to-acid-and-database-transactions/</a> <br/><br/>SQL Transactions and ACID Properties - <a href="https://levelup.gitconnected.com/sql-transactions-and-acid-properties-bb5b670538f8">https://levelup.gitconnected.com[..]actions-and-acid-properties-bb5b670538f8</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2024-03-17T13:03:38ZNote of SQL performance tuning.txt2023-02-01T02:37:46Z2023-02-01T02:37:46Z<br/>Note of this book - <a href="http://www.ocelot.ca/tuning.htm">http://www.ocelot.ca/tuning.htm</a> <br/><br/>1) Order of "and" and "or" is matter, if no cost-base optimizer<br/><br/>where column1 = 'a' and column2 = 'b' -> where column2 = 'b' and column1 = 'a' if column2 'b' is less likely<br/><br/>2) <> is more expensive than =<br/><br/>3) Can force to use index if SELECT * FROM table WHERE indexed_column > 0<br/><br/>4) UPPER(column) might lose information, use LOWER is better<br/><br/>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')<br/><br/>6) 32bit integer is the fastest datatype for 32bit OS<br/><br/>7) column in (?, ?) is faster than column =? or column = ? for some database, and don't slow in any database<br/><br/>8) For aggregate functions, prevent using having, try to use fewer columns in group by and use subquery if possible<br/><br/>9) In fact, count(1) is not faster than count(*)<br/><br/>10) CAST before aggregate function: SELECT CAST(SUM(column) AS INT) -> SELECT SUM(CAST(column as INT))<br/><br/>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<br/><br/>12) Information of Join vs Subqueries<br/><br/>13) Information of optimum column type (VARCHAR, TIMESTAMP, WORD size INT and CHAR(1) usually better), shift effect for page<br/><br/>14) prevent NULL for porting issue<br/><br/>15) Low level table physical information<br/><br/>Some related links - <a href="http://www.techartifact.com/blogs/2009/12/sql-optimization-tipsquestions.html">http://www.techartifact.com[..]9/12/sql-optimization-tipsquestions.html</a> <br/><br/>16) Prevent database level transaction, prevent joining, prevent locking ( like auto increment key ), pretty good suggestion - <a href="http://www.aviransplace.com/2015/08/12/mysql-is-a-great-nosql/">http://www.aviransplace.com/2015/08/12/mysql-is-a-great-nosql/</a> <br/><br/>17) Reduce dataset for aggregation - <a href="https://www.periscopedata.com/blog/use-subqueries-to-count-distinct-50x-faster">https://www.periscopedata.com[..]-subqueries-to-count-distinct-50x-faster</a> <br/><br/>Few good tips, like avoid cursors - <a href="http://www.javaworld.com/article/3209906/application-development/21-rules-for-faster-sql-queries.html">http://www.javaworld.com[..]ent/21-rules-for-faster-sql-queries.html</a> <br/><br/>7 Tips for Query Optimization in SQL Servers - <a href="https://learningdaily.dev/7-tips-for-query-optimization-in-sql-servers-fb3653c910f1">https://learningdaily.dev[..]optimization-in-sql-servers-fb3653c910f1</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2023-02-01T02:37:46Zmysql.txt2022-08-11T13:31:24Z2022-08-11T13:31:24Z<br/>It is surprising for me that it is this easy - <a href="http://alan.blog-city.com/read/1204253.htm">http://alan.blog-city.com/read/1204253.htm</a> <br/><br/>MySQL Proxy for table partition - <a href="http://www.hscale.org/display/HSCALE/Home">http://www.hscale.org/display/HSCALE/Home</a> <a href="http://pero.blogs.aprilmayjune.org/">http://pero.blogs.aprilmayjune.org/</a> <a href="http://forge.mysql.com/wiki/MySQL_Proxy">http://forge.mysql.com/wiki/MySQL_Proxy</a> <br/><br/>NoSQL way to access data in MySQL - <a href="http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html">http://yoshinorimatsunobu.blogspot.com[..]0/10/using-mysql-as-nosql-story-for.html</a> <br/><br/>The lock level of mysql - <a href="http://sgdev-blog.blogspot.com.au/2014/05/mysql-transaction-isolation-levels-and.html">http://sgdev-blog.blogspot.com.au[..]ql-transaction-isolation-levels-and.html</a> <br/><br/>MySQL rewriteBatchedStatements configuration property - <a href="https://vladmihalcea.com/mysql-rewritebatchedstatements">https://vladmihalcea.com/mysql-rewritebatchedstatements</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2022-08-11T13:31:24Zconnection.txt2022-08-02T13:18:23Z2022-08-02T13:18:23Z<br/>Maximum number of database connections - <a href="https://vladmihalcea.com/maximum-database-connections/">https://vladmihalcea.com/maximum-database-connections/</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2022-08-02T13:18:23Zindex.txt2021-06-24T14:57:56Z2021-06-24T14:57:56Z<br/>Case study about how to find out suitable index - <a href="http://samsaffron.com/archive/2011/05/02/A+day+in+the+life+of+a+slow+page+at+Stack+Overflow#">http://samsaffron.com[..]e+life+of+a+slow+page+at+Stack+Overflow#</a> <br/><br/>A simple example of how to use EXPLAIN to find out the bottleneck of the query and add suitable index to suitable column - <a href="http://hackmysql.com/case4">http://hackmysql.com/case4</a> <br/><br/>Bitmap Index vs. B-tree Index: Which and When? - <a href="http://www.oracle.com/technology/pub/articles/sharma_indexes.html">http://www.oracle.com/technology/pub/articles/sharma_indexes.html</a> <a href="http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzajq/perf21c.htm">http://publib.boulder.ibm.com[..]/v5r3/index.jsp?topic=/rzajq/perf21c.htm</a> <br/><br/>script to check if index is missing at foreign key<br/><pre><br/>select table_name, constraint_name,<br/> cname1 || nvl2(cname2,','||cname2,null) ||<br/> nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||<br/> nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||<br/> nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)<br/> columns<br/> from ( select b.table_name,<br/> b.constraint_name,<br/> max(decode( position, 1, column_name, null )) cname1,<br/> max(decode( position, 2, column_name, null )) cname2,<br/> max(decode( position, 3, column_name, null )) cname3,<br/> max(decode( position, 4, column_name, null )) cname4,<br/> max(decode( position, 5, column_name, null )) cname5,<br/> max(decode( position, 6, column_name, null )) cname6,<br/> max(decode( position, 7, column_name, null )) cname7,<br/> max(decode( position, 8, column_name, null )) cname8,<br/> count(*) col_cnt<br/> from (select substr(table_name,1,30) table_name,<br/> substr(constraint_name,1,30) constraint_name,<br/> substr(column_name,1,30) column_name,<br/> position<br/> from sys.user_cons_columns ) a,<br/> sys.user_constraints b<br/> where a.constraint_name = b.constraint_name<br/> and b.constraint_type = 'R'<br/> group by b.table_name, b.constraint_name<br/> ) cons<br/> where col_cnt > ALL<br/> ( select count(*)<br/> from sys.user_ind_columns i<br/> where i.table_name = cons.table_name<br/> and i.column_name in (cname1, cname2, cname3, cname4,<br/> cname5, cname6, cname7, cname8 )<br/> and i.column_position <= cons.col_cnt<br/> group by i.index_name<br/> ) <br/></pre><br/><a href="http://chriswongdevblog.blogspot.com.au/2013/01/dont-forget-to-index-your-oracle.html">http://chriswongdevblog.blogspot.com.au[..]01/dont-forget-to-index-your-oracle.html</a> <br/><br/>Explain how postgres indexes work, look like combine index is not that useful - <a href="http://dublintech.blogspot.com.au/2015/07/postgres-indexes.html">http://dublintech.blogspot.com.au/2015/07/postgres-indexes.html</a> <br/><br/>SQL Server JDBC – Set sendStringParametersAsUnicode to false - <a href="https://vladmihalcea.com/sql-server-jdbc-sendstringparametersasunicode/">https://vladmihalcea.com[..]rver-jdbc-sendstringparametersasunicode/</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2021-06-24T14:57:56Zlock.txt2021-06-21T13:42:47Z2021-06-21T13:42:47Z<br/>Look like using "select XXX for update" is widely supported<br/><br/><a href="http://www.unix.org.ua/orelly/oracle/prog2/ch06_11.htm">http://www.unix.org.ua/orelly/oracle/prog2/ch06_11.htm</a> <br/><a href="http://www.google.com/search?q=select+FOR+UPDATE">http://www.google.com/search?q=select+FOR+UPDATE</a> <br/><br/>BTW, someone suggest using it probably ok for using (nolock) at all - <a href="http://www.codinghorror.com/blog/archives/001166.html">http://www.codinghorror.com/blog/archives/001166.html</a> <br/><br/>Lock in mysql - <a href="https://www.infoq.cn/article/ZAu0eWzsdTX9zoFr6C8W">https://www.infoq.cn/article/ZAu0eWzsdTX9zoFr6C8W</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2021-06-21T13:42:47Zmisc.txt2019-10-15T04:06:14Z2019-10-15T04:06:14Z<br/>Few distributed SQL DB vendor information - <a href="http://www.infoq.com/news/2013/11/sql-newsql-nosql">http://www.infoq.com/news/2013/11/sql-newsql-nosql</a> <br/><br/>Approximation DB - <a href="http://highscalability.com/blog/2016/11/28/how-to-make-your-database-200x-faster-without-having-to-pay.html">http://highscalability.com[..]e-200x-faster-without-having-to-pay.html</a> <br/><br/>Discuss and compare difference technology of querying journey(log) data - <a href="http://www.infoq.com/cn/articles/trillion-log-and-data-storage-query-techniques">http://www.infoq.com[..]on-log-and-data-storage-query-techniques</a> <br/><br/>SAP HANA - <a href="https://www.google.com/search?q=sap+hana">https://www.google.com/search?q=sap+hana</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2019-10-15T04:06:14Zjoin.txt2019-06-17T00:30:14Z2019-06-17T00:30:14Z<br/><a href="http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins">http://www.codeproject.com[..]33052/Visual-Representation-of-SQL-Joins</a> <a href="http://nosql.mypopescu.com/post/51770099382/sql-joins-visualized">http://nosql.mypopescu.com/post/51770099382/sql-joins-visualized</a> <br/><br/>With IN and subquery / or "top" like keywords - <a href="http://javarevisited.blogspot.com.au/2012/12/how-to-find-second-highest-or-maximum-salary-sql.html">http://javarevisited.blogspot.com.au[..]econd-highest-or-maximum-salary-sql.html</a> <br/><br/>Top 5 Online Courses to Learn SQL and Database for Beginners - Best of Lot <br/>- <a href="https://dev.to/javinpaul/5-online-courses-to-learn-sql-and-database-for-beginners-best-of-lot-5533">https://dev.to[..]-database-for-beginners-best-of-lot-5533</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2019-06-17T00:30:14Zmssql.txt2018-03-31T08:14:54Z2018-03-31T08:14:54Z<br/>SQL 2005 JDBC fix the resultset re-read throws exception problem. However, you need to use executeUpdate() for insert,update and delete instead of execute() for SQL 2005 JDBC, otherwise will throw exception on this.<br/><br/>Other that, statement.executeBatch() will throws exception: <a href="http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=e13a8009-7466-4803-ba19-6bfd4b5f8966">http://lab.msdn.microsoft.com[..]kid=e13a8009-7466-4803-ba19-6bfd4b5f8966</a> <br/><br/>New function of SQL server 2014, In-Memory Optimization tables, suppose to be faster? - <a href="http://www.infoq.com/news/2013/09/MOT-Indexes">http://www.infoq.com/news/2013/09/MOT-Indexes</a> MOT table also support native compiled query - <a href="http://www.infoq.com/news/2013/09/Compiled-Queries">http://www.infoq.com/news/2013/09/Compiled-Queries</a> , and lock free write? <a href="http://www.infoq.com/news/2013/09/Lock-Free-Writes">http://www.infoq.com/news/2013/09/Lock-Free-Writes</a> <br/><br/>From microsoft, Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size. - <a href="http://www.infoq.com/news/2013/09/Clustered-Columnstore">http://www.infoq.com/news/2013/09/Clustered-Columnstore</a> <br/><br/>A tool from M$ to help sql server user to manage change of database: <a href="http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/">http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/</a> . Other similar product is DBGhost, also only work for SQL server <a href="http://www.innovartis.co.uk/home.aspx">http://www.innovartis.co.uk/home.aspx</a> <br/><br/>There is a free version of SQL server 2005 call SQL server express, which just like MSDE <a href="http://www.microsoft.com/sql/editions/express/redistregister.mspx">http://www.microsoft.com/sql/editions/express/redistregister.mspx</a> <br/><br/>Here is a comparison: <a href="http://www.sqlmag.com/Article/ArticleID/49618/sql_server_49618.html">http://www.sqlmag.com[..]le/ArticleID/49618/sql_server_49618.html</a> , may be we can use that instead of MSDE 2005 and discoutinue support of SQL server 2000 so that we can test one thing less? <br/><br/>Import CSV to SQL server - <a href="http://www.sqlservercurry.com/2011/01/load-comma-delimited-file-csv-in-sql.html">http://www.sqlservercurry.com[..]oad-comma-delimited-file-csv-in-sql.html</a> <br/><br/>Shriking DB cause performance problem... not sure how long this finding is hold - <a href="http://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/">http://blog.sqlauthority.com[..]eases-fragmentation-reduces-performance/</a> <br/><br/><a href="http://beyondrelational.com/blogs/livingforsqlserver/archive/2011/08/08/sql-server-storage-internals-part-1-basics.aspx">http://beyondrelational.com[..]ver-storage-internals-part-1-basics.aspx</a> <br/><br/>Get the metadata - <a href="http://gigi.nullneuron.net/gigilabs/retrieving-table-metadata-from-sql-server-catalog-views/">http://gigi.nullneuron.net[..]-metadata-from-sql-server-catalog-views/</a> <a href="https://simpleprogrammer.com/microsoft-sql-server-metadata-developers/">https://simpleprogrammer.com[..]icrosoft-sql-server-metadata-developers/</a> <br/><br/>Date functions - <a href="http://www.essentialsql.com/introduction-to-sql-servers-date-functions/">http://www.essentialsql.com[..]roduction-to-sql-servers-date-functions/</a> <br/><br/>Enhancement of 2016 - <a href="https://www.infoq.com/news/2016/06/SQl-Server-2016-Performance">https://www.infoq.com/news/2016/06/SQl-Server-2016-Performance</a> <br/><br/>Tool to rescue sql server - <a href="https://www.infoq.com/news/2017/02/SQL-First-Responder">https://www.infoq.com/news/2017/02/SQL-First-Responder</a> <a href="https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit">https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit</a> <a href="http://firstresponderkit.org/">http://firstresponderkit.org/</a> <br/><br/>Prevent locking with NOLOCK / READPAST - <a href="https://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/">https://www.techrepublic.com[..]-and-readpast-table-hints-in-sql-server/</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2018-03-31T08:14:54Znuma.txt2017-11-03T01:41:42Z2017-11-03T01:41:42Z<br/><a href="https://technet.microsoft.com/en-us/library/ms345403(v=sql.105).aspx">https://technet.microsoft.com[..]m/en-us/library/ms345403(v=sql.105).aspx</a> <br/><br/><a href="https://code.kx.com/q/cookbook/linux-production/">https://code.kx.com/q/cookbook/linux-production/</a> <br/><br/><a href="http://jcole.us/blog/archives/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/">http://jcole.us[..]swap-insanity-and-the-numa-architecture/</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2017-11-03T01:41:42Zarchitecture.txt2017-09-22T07:29:28Z2017-09-22T07:29:28Z<br/>IT Hare: Ultimate DB Heresy: Single Modifying DB Connection. Part I. Performanc - <a href="http://highscalability.com/blog/2016/11/22/it-hare-ultimate-db-heresy-single-modifying-db-connection-pa.html">http://highscalability.com[..]y-single-modifying-db-connection-pa.html</a> <br/><br/>How yandex.metrica improve DB reading - <a href="http://highscalability.com/blog/2017/9/18/evolution-of-data-structures-in-yandexmetrica.html">http://highscalability.com[..]of-data-structures-in-yandexmetrica.html</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2017-09-22T07:29:28Zuuid.txt2017-09-08T16:15:42Z2017-09-08T16:15:42Z<br/><a href="https://segment.com/blog/a-brief-history-of-the-uuid/">https://segment.com/blog/a-brief-history-of-the-uuid/</a> <br/><a href="http://www.infoq.com/cn/articles/talk-about-the-history-of-uuid">http://www.infoq.com/cn/articles/talk-about-the-history-of-uuid</a> <br/><br/>If using uuid as PK is good idea? - <a href="https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-careful-7b2aa3dcb439">https://tomharrisonjr.com[..]-as-primary-keys-be-careful-7b2aa3dcb439</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2017-09-08T16:15:42Ztime series.txt2017-07-26T02:57:03Z2017-07-26T02:57:03Z<br/>Some information about Time Series:<br/><br/><a href="http://en.wikipedia.org/wiki/S-PLUS">http://en.wikipedia.org/wiki/S-PLUS</a> <br/><a href="http://download-west.oracle.com/docs/cd/A87860_01/doc/inter.817/index.htm">http://download-west.oracle.com[..]ocs/cd/A87860_01/doc/inter.817/index.htm</a> <br/><a href="http://www.sas.com/">http://www.sas.com/</a> <br/><a href="http://oracledmt.blogspot.com/2006/10/time-series-revisited.html">http://oracledmt.blogspot.com/2006/10/time-series-revisited.html</a> <br/><a href="http://oracledmt.blogspot.com/2006/01/time-series-forecasting-part-1_23.html">http://oracledmt.blogspot.com[..]1/time-series-forecasting-part-1_23.html</a> <br/><br/>Discussion of time in time series database - UTC or local - <a href="http://quant.stackexchange.com/questions/7229/time-in-time-series-database-utc-or-local">http://quant.stackexchange.com[..]ime-in-time-series-database-utc-or-local</a> <br/><br/>Market data:<br/><a href="http://cs.nyu.edu/cs/faculty/shasha/fintime.html">http://cs.nyu.edu/cs/faculty/shasha/fintime.html</a> <br/><br/>and a book:<br/><a href="http://sql-info.de/sql-notes/developing-time-oriented-database-applications-in-sql.html">http://sql-info.de[..]iented-database-applications-in-sql.html</a> <br/><a href="http://www.cs.arizona.edu/people/rts/tdbbook.pdf">http://www.cs.arizona.edu/people/rts/tdbbook.pdf</a> <br/><br/>Should we use DATE as key or INT as key? <a href="http://quant.stackexchange.com/questions/613/representing-time-series-implementation-level">http://quant.stackexchange.com[..]senting-time-series-implementation-level</a> <br/><br/>Collections of column-oriented stores - <a href="http://quant.stackexchange.com/questions/1392/usage-of-nosql-storage-in-finance">http://quant.stackexchange.com[..]s/1392/usage-of-nosql-storage-in-finance</a> <br/><br/>时序数据库深入浅出之存储篇 - <a href="http://www.infoq.com/cn/articles/storage-in-sequential-databases">http://www.infoq.com/cn/articles/storage-in-sequential-databases</a> <br/><br/>深入浅出时序数据库之压缩篇 - <a href="http://www.infoq.com/cn/articles/condense-in-sequential-databases">http://www.infoq.com/cn/articles/condense-in-sequential-databases</a> <br/><br/>pretreatment - <a href="http://www.infoq.com/cn/articles/pretreatment-in-sequential-databases">http://www.infoq.com[..]les/pretreatment-in-sequential-databases</a> <br/><br/>Why - <a href="http://www.infoq.com/cn/news/2017/07/Why-time-series-database">http://www.infoq.com/cn/news/2017/07/Why-time-series-database</a> <br/><br/>分级存储 - <a href="http://www.infoq.com/cn/articles/hierarchical-storage-of-sequential-databases">http://www.infoq.com[..]archical-storage-of-sequential-databases</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2017-07-26T02:57:03Zdatabase comparison.txt2017-01-03T01:58:28Z2017-01-03T01:58:28Z<br/>Wiki of common DBMS comparison - <a href="http://en.wikipedia.org/wiki/Comparison_of_SQL_database_management_systems">http://en.wikipedia.org[..]rison_of_SQL_database_management_systems</a> <br/><br/>Somebody comment that postgresql is less competency than MSSQL, see the comment<br/><a href="http://www.baubels.net/blog/?p=88">http://www.baubels.net/blog/?p=88</a> <br/><br/>Alternative DBMS - <a href="http://www.webresourcesdepot.com/25-alternative-open-source-databases-engines/">http://www.webresourcesdepot.com[..]ternative-open-source-databases-engines/</a> <br/><br/><a href="http://blog.jooq.org/2013/10/03/the-10-most-popular-db-engines-sql-and-nosql/">http://blog.jooq.org[..]0-most-popular-db-engines-sql-and-nosql/</a> <br/><br/><a href="http://www.xaprb.com/blog/2015/05/25/what-makes-a-solution-mature/">http://www.xaprb.com[..]2015/05/25/what-makes-a-solution-mature/</a> <br/><br/><a href="http://www.javacodegeeks.com/2015/07/mysql-vs-mongodb.html">http://www.javacodegeeks.com/2015/07/mysql-vs-mongodb.html</a> <br/><br/>Comparing major RDBMS - <a href="http://www.infoq.com/cn/articles/select-the-appropriate-rdmbs-for-the-oltp">http://www.infoq.com[..]elect-the-appropriate-rdmbs-for-the-oltp</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2017-01-03T01:58:28Zpgsql.txt2016-10-18T17:41:16Z2016-10-18T17:41:16Z<br/><a href="http://www.javacodegeeks.com/2012/10/introduction-to-postgresql-pljava.html">http://www.javacodegeeks.com[..]0/introduction-to-postgresql-pljava.html</a> <br/><br/>PostgreSQL schema is way fast and easy to change than MySQL, although maybe slower - <a href="https://www.pgrs.net/2011/03/25/migrating-from-mysql-to-postgresql-slides/">https://www.pgrs.net[..]grating-from-mysql-to-postgresql-slides/</a> <br/><br/> Subindexes that can save you space and make updates faster by reusing an existing multi-column index.<br/> Expression indexes that speed up queries with WHERE clauses matching a function of the columns instead of raw values.<br/> Partial indexes that are much smaller than a complete index because they don't contain data that the app doesn't care about.<br/><a href="http://blog.scoutapp.com/articles/2016/05/31/3-postgresql-indexing-tricks-that-surprise-everyone">http://blog.scoutapp.com[..]l-indexing-tricks-that-surprise-everyone</a> <br/><br/>Experience sharing, stored proc is better? <a href="http://www.infoq.com/cn/news/2016/10/postgres-and-financial-arch">http://www.infoq.com/cn/news/2016/10/postgres-and-financial-arch</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2016-10-18T17:41:16Zreading.txt2016-10-13T00:33:54Z2016-10-13T00:33:54Z<br/>Berkeley CS286: Implementation of Database Systems, Fall 2014 > <a href="http://www.cs286.net/home/reading-list">http://www.cs286.net/home/reading-list</a> <br/><br/>Disambiguating Databases, explaining various aspects of difference datastore approach - <a href="http://queue.acm.org/detail.cfm?ref=rss&id=2696453">http://queue.acm.org/detail.cfm?ref=rss&id=2696453</a> <br/><br/>Basic - <a href="http://ithare.com/databases-101-acid-mvcc-vs-locks-transaction-isolation-levels-and-concurrency/">http://ithare.com[..]action-isolation-levels-and-concurrency/</a> <br/><br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2016-10-13T00:33:54Zoracle.txt2016-06-22T23:23:32Z2016-06-22T23:23:32Z<br/>Some comment saying that you cannot complain oracle for non-standard setup... However, should I break anything else just to install oracle? Anyway, may be a useful resource of installing oracle cluster - <a href="http://thedailywtf.com/forums/65743/ShowPost.aspx">http://thedailywtf.com/forums/65743/ShowPost.aspx</a> <br/><br/>Oracle SQL links - <a href="http://mycodeblog.blogspot.com/2007/06/sql-cheat-sheet.html">http://mycodeblog.blogspot.com/2007/06/sql-cheat-sheet.html</a> <br/><br/>Pure java solution to full text search - <a href="http://www.infoq.com/news/2007/10/lucene-oracle">http://www.infoq.com/news/2007/10/lucene-oracle</a> <a href="http://marceloochoa.blogspot.com/2007/09/running-lucene-inside-your-oracle-jvm.html">http://marceloochoa.blogspot.com[..]nning-lucene-inside-your-oracle-jvm.html</a> <a href="http://dbprism.cvs.sourceforge.net/dbprism/ojvm/">http://dbprism.cvs.sourceforge.net/dbprism/ojvm/</a> <br/><br/>Oracle does allow definitions of user-defined types.Please see <br/><a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements001.htm#i46376">http://download.oracle.com[..]er.111/b28286/sql_elements001.htm#i46376</a> <br/><br/>User-defined types can then be used for column definitions. See e.g. <br/>the fourth example given here <br/><a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#i2062833">http://download.oracle.com[..].111/b28286/statements_7002.htm#i2062833</a> <br/><br/>Oracle also allows creation of a table based on one user-defined type, <br/>i.e. an "object table".<br/>See here <br/><a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#i2159410">http://download.oracle.com[..].111/b28286/statements_7002.htm#i2159410</a> <br/><br/>User-defined types can be changed; see <br/><a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4002.htm#i2057828">http://download.oracle.com[..].111/b28286/statements_4002.htm#i2057828</a> <br/>Oracle even offers an option as to whether existing data in tables <br/>based on previous type definition is converted or not; see <br/><a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4002.htm#i2079300">http://download.oracle.com[..].111/b28286/statements_4002.htm#i2079300</a> <br/>There are a couple of pages in the Object-Relational Developer's Guide explaining the consequences, briefly...<br/><br/>Note: all links above are to Oracle 11g documentation, but many, if not all, of these features have been available since 9i.<br/><br/>row base permission - <a href="http://technotes.towardsjob.com/oracle/virtual-private-database-oracle/">http://technotes.towardsjob.com[..]/oracle/virtual-private-database-oracle/</a> <br/><br/>How fetch size can affect performance - <a href="http://www.skill-guru.com/blog/2010/07/25/jdbc-performance-tuning-with-fetch-size/">http://www.skill-guru.com[..]jdbc-performance-tuning-with-fetch-size/</a> <br/><br/>Recycle bin for Oracle 10g - <a href="http://mahmoudoracle.blogspot.gr/2012/11/recycle-bin-in-database.html#.UNXprSUqthH">http://mahmoudoracle.blogspot.gr[..]ecycle-bin-in-database.html#.UNXprSUqthH</a> <br/><br/>Oracle won't put null in index... how can we find the column with issue out? - <a href="http://blog.jooq.org/2014/05/12/the-index-youve-added-is-useless-why/">http://blog.jooq.org[..]12/the-index-youve-added-is-useless-why/</a> <br/><br/>You can have java like stacetrace at PLSQL - <a href="http://blog.jooq.org/2014/08/29/plsql-backtraces-for-debugging/">http://blog.jooq.org/2014/08/29/plsql-backtraces-for-debugging/</a> <br/><br/>Need to be careful about using rownum to limit the result in oracle - <a href="http://www.monkeycancode.com/oracle-limit-returned-records-with-rownum">http://www.monkeycancode.com[..]racle-limit-returned-records-with-rownum</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2016-06-22T23:23:32Zdatabase style.txt2016-06-06T08:09:19Z2016-06-06T08:09:19Z<br/>A pointer to various database style discussion, like [application database], [integration database], [silly database], [smart database] ...<br/><br/><a href="http://www.pervasivecode.com/blog/2007/08/02/rails-and-the-notion-of-stupid-databases-being-a-good-idea/">http://www.pervasivecode.com[..]n-of-stupid-databases-being-a-good-idea/</a> <br/><br/>BDD for database design <br/><br/>Requirements are behavior,<br/>Provides "ubiquitous language" for analysis,<br/>Acceptance criteria should be executable.<br/>Design constraints should be made into executable tests.<br/><br/>- <a href="http://www.methodsandtools.com/archive/archive.php?id=78">http://www.methodsandtools.com/archive/archive.php?id=78</a> <br/><br/><a href="http://dublintech.blogspot.hk/2016/06/agile-databases.html">http://dublintech.blogspot.hk/2016/06/agile-databases.html</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2016-06-06T08:09:19ZJDBC performance tips.txt2016-02-01T04:26:28Z2016-02-01T04:26:28Z<br/>Some tips, like use of addBatch()<br/><br/><a href="http://rgarg.blogspot.com/2005/10/jdbc-performance-pointers.html">http://rgarg.blogspot.com/2005/10/jdbc-performance-pointers.html</a> <br/><a href="http://www.theserverside.com/articles/content/JDBCPerformance_PartIII/article.html">http://www.theserverside.com[..]ent/JDBCPerformance_PartIII/article.html</a> <br/><br/>Nice discussion about preparestatement caching<br/><br/><a href="http://www.theserverside.com/news/thread.tss?thread_id=27528">http://www.theserverside.com/news/thread.tss?thread_id=27528</a> <a href="http://www.ibm.com/developerworks/java/library/j-jdbcnew/">http://www.ibm.com/developerworks/java/library/j-jdbcnew/</a> <br/><br/><a href="http://blog.jooq.org/2014/05/26/yet-another-10-common-mistakes-java-developer-make-when-writing-sql-you-wont-believe-the-last-one/">http://blog.jooq.org[..]iting-sql-you-wont-believe-the-last-one/</a> <br/><br/>Statement.getWarnings() is expensive - <a href="https://plumbr.eu/blog/io/how-we-accidentally-doubled-our-jdbc-traffic-with-hibernate">https://plumbr.eu[..]-doubled-our-jdbc-traffic-with-hibernate</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2016-02-01T04:26:28Zgrouping.txt2016-01-22T03:39:27Z2016-01-22T03:39:27Z<br/>There is way to drop the dummy aggregate function in group by - <a href="http://blog.jooq.org/2015/12/10/sql-group-by-and-functional-dependencies-a-very-useful-feature/">http://blog.jooq.org[..]onal-dependencies-a-very-useful-feature/</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2016-01-22T03:39:27Zsybase.txt2015-08-28T10:19:53Z2015-08-28T10:19:53Z<br/>If you get following exception with Sybase ASE drive:<br/><br/>'System.Reflection.TargetInvocationException, Msg=Exception has been thrown by the target of an invocation.<br/>Exception details: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.TypeInitializationException: The type initializer for 'Sybase.Data.AseClient.AseConnection' threw an exception. ---> System.DllNotFoundException: Unable to load DLL 'sybdrvado115a.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)<br/> at ?.AseGetDriverVersion(StringBuilder A_0, Int32 A_1)<br/> at Sybase.Data.AseClient.AseConnection.?()<br/> at Sybase.Data.AseClient.AseConnection..cctor()<br/> --- End of inner exception stack trace ---<br/> at Sybase.Data.AseClient.AseConnection..ctor(String connectionString)<br/><br/>Please make sure the program can see sybdrvado115.dll, msvcp71.dll and msvcr71.dll in that same directory<br/><br/><a href="http://www.elsasoft.org/SqlSpec.htm">http://www.elsasoft.org/SqlSpec.htm</a> <br/><br/>===================<br/><br/>Query to check blocking at sybase: <br/>select spid,suser_name(suid),cmd,status,blocked,time_blocked,physical_io from master..sysprocesses where blocked <> 0<br/><br/><br/><br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2015-08-28T10:19:53Zdata.txt2015-07-05T00:25:32Z2015-07-05T00:25:32Z<br/>The Data Analytics Handbook - <a href="https://www.teamleada.com/handbook">https://www.teamleada.com/handbook</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2015-07-05T00:25:32Zview.txt2015-06-09T09:22:45Z2015-06-09T09:22:45Z<br/><a href="http://blog.jooq.org/2015/06/02/what-exactly-are-sql-views/">http://blog.jooq.org/2015/06/02/what-exactly-are-sql-views/</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>2015-06-09T09:22:45Z