download zip of files only
Sun Oct 23 00:00:44 HKT 2011
From /weblog/database/vendor
A tool from M$ to help sql server user to manage change of database: http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/ . Other similar product is DBGhost, also only work for SQL server http://www.innovartis.co.uk/home.aspx There is a free version of SQL server 2005 call SQL server express, which just like MSDE http://www.microsoft.com/sql/editions/express/redistregister.mspx Here is a comparison: http://www.sqlmag.com[..]le/ArticleID/49618/sql_server_49618.html , 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? Import CSV to SQL server - http://www.sqlservercurry.com[..]oad-comma-delimited-file-csv-in-sql.html Shriking DB cause performance problem... not sure how long this finding is hold - http://blog.sqlauthority.com[..]eases-fragmentation-reduces-performance/ http://beyondrelational.com[..]ver-storage-internals-part-1-basics.aspx
(google search)
(amazon search)
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 Jun 17 00:44:14 HKT 2011
From /weblog/database/objective
A presentation about Politics issues of using ORM persistence - http://www.softwaresummit.com[..]ers/SmithDonaldPoliticsOfPersistence.pdf A checklist of features of ORM library, which, I think, may be too detail to consider that much? http://madgeek.com/Articles/ORMapping/EN/mapping.htm Some problem of ORM nowadays - http://blogs.ittoolbox.com[..]e-ubiquitous-bastardization-of-orm-17100 This post rise a good point, if the ORM tool doesn't provide a simpler solution, why bother to use it? Just because many people using it? http://en.newinstance.it/2006/10/23/trimming-jdbc/ We have been using hibernate for while, actually it is good. But LGPL block us from using it anymore... Here is some other experience about this - http://shinetech.com/pages/viewpage.action?pageId=649 and the discussion of this article - http://www.theserverside.com[..]m/news/thread.tss?thread_id=41174#212825 Some interestig ORM packages without XML http://www.jenkov.com/mrpersister/introduction.tmpl http://www.simpleorm.org/sorm/whitepaper.html http://www.h2database.com/html/jaqu.html And there is one try to use javadoc doclet approach to make all those SQL documented at javadoc also, look like an interesting idea - http://voruta.sourceforge.net/ Advantage of using ORM, but are those really so important? http://ayende.com[..]oWriteYourOwnObjectRelationalMapper.aspx Good source about various pattern and test on ORM - http://blog.xebia.com/category/jpa/implementation-patterns/ Analysis on ORM package - http://seldo.com/weblog/2011/06/15/orm_is_an_antipattern
(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)
Sat Mar 05 15:27:37 HKT 2011
From /weblog/database/vendor
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 - http://thedailywtf.com/forums/65743/ShowPost.aspx Oracle SQL links - http://mycodeblog.blogspot.com/2007/06/sql-cheat-sheet.html Pure java solution to full text search - http://www.infoq.com/news/2007/10/lucene-oracle http://marceloochoa.blogspot.com[..]nning-lucene-inside-your-oracle-jvm.html http://dbprism.cvs.sourceforge.net/dbprism/ojvm/ Oracle does allow definitions of user-defined types.Please see http://download.oracle.com[..]er.111/b28286/sql_elements001.htm#i46376 User-defined types can then be used for column definitions. See e.g. the fourth example given here http://download.oracle.com[..].111/b28286/statements_7002.htm#i2062833 Oracle also allows creation of a table based on one user-defined type, i.e. an "object table". See here http://download.oracle.com[..].111/b28286/statements_7002.htm#i2159410 User-defined types can be changed; see http://download.oracle.com[..].111/b28286/statements_4002.htm#i2057828 Oracle even offers an option as to whether existing data in tables based on previous type definition is converted or not; see http://download.oracle.com[..].111/b28286/statements_4002.htm#i2079300 There are a couple of pages in the Object-Relational Developer's Guide explaining the consequences, briefly... Note: all links above are to Oracle 11g documentation, but many, if not all, of these features have been available since 9i. row base permission - http://technotes.towardsjob.com[..]/oracle/virtual-private-database-oracle/ How fetch size can affect performance - http://www.skill-guru.com[..]jdbc-performance-tuning-with-fetch-size/
(google search)
(amazon search)
Tue Dec 21 23:03:41 HKT 2010
From /weblog/database
Will you get benefits from the brand new easy to get SQL server expert accreditation? In my opinion the most notable DB platforms are Microsoft and Oracle. I've come to find that MySQL is popular in start-up companies and DB people who know this platform might make higher salaries, but I honestly rarely ever come across MySQL in job searches. One reason I like MS SQL Server is because it also includes the SSRS BI platform that is powerful and popular. DB folks who know SSRS (SQL Server Reporting Services) have become very sought-after candidates in lots of companies. Generally, companies end up being forced to buy licenses for third-party BI (Business Intelligence) applications which often can cost a lot of extra money. With SQL Server, BI and ETL (extract, transform, load) tools come with the first license. I know that maybe the interest in databases is especially to learn DB construction, but you never know when or if you opt to branch out and learn more. An advantage of SQL is the language itself is fairly universal. I say fairly because each vendor has added their unique functions and syntax. Microsoft and Sybase both use T-SQL, Oracle uses PL/SQL and MySQL uses ANSI-SQL. Ultimately what you learn on one platform can be put on other platforms. To become a SQL Server Microsoft Certified Master (MCM) is a small task now. Under 1 percent of certified professionals hold a Master certification, and for good reason: In addition to having difficult minimum competencies (5 years SQL Server experience and deep knowledge in most aspects of SQL Server), training for a SQL Server MCM has always come at a significant cost. In fact, that cost has been about $18,500, when you accumulate the required three week training and four exams. The SQL Server MCM certification is the highest technical certification that Microsoft offers to SQL Server professionals. It's suitable for individuals with five or more years of hands-on SQL Server experience in critical environments. Competencies include designing and implementing high-performance, scalable enterprise environments and troubleshooting the most challenging SQL Server issues. Well, at PASS (The Professional Association for SQL Server) this week, Microsoft announced all that is beginning to change. According to the company, the changes have been in reaction to requests from experienced SQL Server experts who need to earn MCM's but (go figure) don't possess $18,500. Now, candidates can earn the certification by passing just two exams: the four-hour Knowledge Exam, and also a six-hour hands-on Lab Exam, which will be obtainable in early 2011. Fortunately, Microsoft has made some significant changes to the program, including: * The 3-week in-person training is not required. So, if you believe like you have the required steps, you are able to march right up and take the certification exams. You only pay the cost of the exams, that is about $2,500. * Nowadays there are only two exams rather than four: 88-970 (Knowledge Exam) and 88-971 (Lab Exam). * To aid SQL Server professionals plan the exams at a lower cost, Microsoft now offers some free MCM videos. So you? Do you consider the brand new MS SQL Certification can help you in your job? About the author: M. P. Rouse is writing for the sql certification path blog, her personal and non-commercial in nature hobby blog targeted at recommendations to supply free info for data base beginners/professionals to help them find a new profession.
(google search)
(amazon search)
Sun Feb 07 22:41:23 HKT 2010
From /weblog/database
A pointer to various database style discussion, like [application database], [integration database], [silly database], [smart database] ... http://www.pervasivecode.com[..]n-of-stupid-databases-being-a-good-idea/ BDD for database design Requirements are behavior, Provides "ubiquitous language" for analysis, Acceptance criteria should be executable. Design constraints should be made into executable tests. - http://www.methodsandtools.com/archive/archive.php?id=78
(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)
Fri Feb 13 23:05:40 HKT 2009
From /weblog/database
Difference database sort null in timestamp column in difference way: MySQL: NULL values are presented first, or last if you specify DESC to sort in descending order. Exception: In MySQL 4.0.2 through 4.0.10, NULL values sort first regardless of sort order. - http://dev.mysql.com/doc/mysql/en/problems-with-null.html SQL server 2000, Sybase 12.5 and hsql 1.8.0: NULL values are presented first, or last if you specify DESC to sort in descending order. Oracle 8: NULL values are presented last, or first if you specify DESC to sort in descending order. A more detail default sorting behaviour: {null, -1, +1} // ASC sort for informix, microsoft, mysql and sybase {-1, +1, null} // ASC sort for IBM, Ingres, InterBase, Oracle and PostgreSQL {+1, -1, null} // ASC sort for informix, InterBase, PostgreSQL, microsoft, mysql and sybase {null, +1, -1} // ASC sort for IBM, Ingres and Oracle A discussion about using NULL or not - http://discuss.joelonsoftware.com/default.asp?design.4.403282 http://discuss.joelonsoftware.com/default.asp?design.4.494249 One solution - http://web.onetel.com[..]Manifesto/Missing-info-without-nulls.pdf Null, mean not application or no value? - http://www.livejournal.com/users/djberg96/47074.html Nothing neither equals, nor unequals, to NULL - http://fishbowl.pastiche.org/2009/01/30/why_null_is_special/
(google search)
(amazon search)
Wed Jan 07 23:18:29 HKT 2009
From /weblog/database
List of Database Refactorings Pramod has been working on defining the various refactorings that he's used in the approach defined in our paper on evolutionary database design Here we keep a list of the refactorings that he has defined so far. http://martinfowler.com/dbrefact/ There is some tools support, but not sure if that really work... - http://www.simple-talk.com[..]-database-development-with-sql-refactor/ Video of tutorial presentation, very nice - http://www.infoq.com/presentations/ambler-database-refactoring Article show how driven DB design with test - http://www.methodsandtools.com/archive/archive.php?id=78
(google search)
(amazon search)
|