List:General Discussion« Previous MessageNext Message »
From:Douglas Sims Date:March 23 2006 7:23am
Subject:Re: database compatibility
View as plain text  
I think it's better to pick one database and try to design and code  
to its strengths and weaknesses.

I've worked on several projects in which the overall architectural  
plan involved writing a database abstraction layer to keep options  
open as to the specific relational db - MySQL, Oracle, MS SQL, etc.   
None of these projects has gone particularly well.  I've worked on  
other projects where the focus was on making things work well with  
whatever database was chosen and not worrying so much about keeping  
the options open, and these projects have gone much better.  And in  
neither case has the project actually ever switched databases, so the  
effort spent in trying to preserve compatibility was wasted.

If you stick to the lowest common denominator in your SQL you won't  
be taking advantage of any of the unique benefits which the different  
servers bring.  MySQL is my favorite database.  For web work it has  
several significant advantages over other databases - the LIMIT  
clause alone is unique to MySQL and it's worth using MySQL just for  
that.  For example, if you use MS SQL and .Net, the entire results of  
queries are sent from the DB server to the web server and pagination  
is done on the web server.  This moves a lot more data over network  
connections, ties up more memory in the db and web server, and slows  
things down.  With the LIMIT clause, only the rows to be displayed on  
the current page are sent over the wire.  If you try to use only the  
ANSI standard features of MySQL you will miss out on this advantage.

Here is an interesting article about Google's switch from MySQL to  
Oracle for AdWords: http://xooglers.blogspot.com/2005/12/lets-get- 
real-database.html   I believe (can anyone from the big G confirm or  
correct?) that AdWords has been moved back to MySQL.

Oracle has some neat features for handling trees and hierarchical  
data (CONNECT BY etc) which won't work in any other version of SQL.   
If you're going to use Oracle and you have data which is best  
represented in trees, it would be a mistake to not use Oracle's built- 
in tree features.  Oracle is very nice, but it doesn't give you any  
speed over MySQL and you can put a man on the moon for less money.

Microsoft SQL Server is very nice if you like that sort of thing and  
Sybase is pretty much the same thing only the port numbers have been  
changed to protect the innocent.  I'm sure DB2 and Postgres and  
Informix and all the others are very nice too.

Stored procedure syntax is significantly different among the major  
dbs, so if you're going to limit yourself to standard sql there's no  
point in using stored procedures.  Which isn't really a bad thing on  
the whole.

Good luck!



On Mar 22, 2006, at 11:30 PM, David Griffiths wrote:

> That's a pretty difficult request:
>
> 1) What versions? MySQL 4.0 has no stored procedures, functions,  
> views, subselects. 4.1 added subselects. 5.0 added the rest. Oracle  
> 8i doesn't support ANSI-style outer joins (they use a proprietary  
> format).
>
> 2) Different engines; MySQL supports federated, archive, MyISAM,  
> InnoDB in 5.0 (there are probably a few others). Each engine has  
> different DML (Data Manipulation Language - select, insert, update,  
> delete) and DDL options (ALTER TABLE, CREATE TABLE, etc).
>
> 3) Built-in funtions vary widely (though there are some common  
> ones, the format and structure can differ).
>
> That's just touching the surface (I have 5 minutes while a database  
> machine reboots, so I thought I'd post a reply).
>
> I am not sure what you are after, but you might want to consider an  
> existing ORM (Object-Relational) tool that does the SQL for you.  
> Hibernate for Java is amazing, and NHibernate is now out for .NET  
> (not sure if it's alpha, beta or production).
>
> If you are coding to experiment, I'd suggest you limit yourself to  
> a few (MySQL-InnoDB is very popular, and Postgres). Both free, with  
> lots of good online-documentation available.
>
> Check out this article:
>
> http://www.devx.com/dbzone/Article/20743
>
> David
>
> ChadDavis wrote:
>> Does anyone know of a resource ( on the web perhaps ) that  
>> discusses the
>> core differences between the different database's sql.  I'm trying to
>> write
>> code that produces the correct sql for a variety of databases.  Such
>> things
>> as Oracle's SEQUENCES versus mysql's AUTO_INCREMENT are of interest.
>> Maybe
>> I'm asking too much to find a summary of such differences.  But  
>> I'm only
>> interested in using mainstream sql functinality, nothing complicated.
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Douglas Sims
Doug@stripped



Thread
database compatibilityChadDavis23 Mar
  • Re: database compatibilityDavid Griffiths23 Mar
    • Re: database compatibilityDouglas Sims23 Mar