I am looking at a dataware house with 300 gb initially and considering what to use Mysql vs SQL Server vs Oracle. We will be running many complex queries often reading the whole DB, logic based on date calcuations, and likely reloading the whole DB on a weekly basis. Which DB seems best and why?
My personal list is:
1. Oracle 9.2: excellent feature set (views, stored procedures, external tables, upserts, unix platform, OWB ...)
2. MS SQL: good feature set (views, stored procedures, DTS ...)
3. MySQL: not really suitable (no views, no stored procedures ...)
I say this because I've already built some DWHs on Oracle and Ms SQL.
What is your expriance on Oracle 9.2 vs MS SQL in this type of application.
Oracle has better features and handles larger database (Tb magnitude) and also runs on a more stable platform (Unix/Linux).
SQLServer is easier to use and comes with a whole built in ETL tool (DTS).
1.) oracle is the best choice (if you can afford it), no doubt.
2.) sql server might be o.k.
3.) mysql is something my children will play with as soon as they learn to read.
Wolfpacker94 - can you point me to some imperical data on this? I would really appricate any information.
first of all: excuse may english, it's not my mothertounge
oracle best choice: as pabloj mentioned before, oracle gives you many possiblies (functional and for performance) and can be VERY fast. It's impossible for me to discuss all this features (bitmap index, functional index, materialzed views, top n values ...etc.), it would take hours to explain all that.
if you can afford it: oracle licences are quite expensive and oracle needs permanent monitoring (my experience, the oracle salesman told us other things...) by a DBA.
sql server might be o.k.: i haven't much information about sql server, but all (serious) guys i talked to had been quite satisfied. sorry, i can't tell you more about that.
mysql: we use mysql as well, but just for smaller projects. we ran into many problems (performace) with complex applications.
why don't you try to get professional help by a serious company (don't go to oracle or ms, they will always tell you that their product is the best)? the bucks you invest now, will pay back soon.
greetings
w.
wolfpacker94 has added a very important thing:
oracle needs permanent monitoring
that's true, you'll need an experienced DBA, possibly have him review the dwh before implementation, design for performance because datawarehousing really stresses the db.
MS SQL is very good, it will also need good tuning and monitoring, probably not as much as oracle, but I dont' think that it can scale up to Oracle's level.
But again, not every dwh goes into the terabyte range.
Hmm, to revive this topic -- I'm looking for a good SQL with fast indexing -- so which one is better? I only need a fast indexing for my application...
SQL Server works pretty well for a Data Warehouse. We have a 2-3 TB DWH and have not had any issues with performance. DTS with stored procedures make the ETL jobs easy to maintain.
I've not used Oracle so I cannot speak to it's capabilities.
mySQL is great for smaller apps. Perhaps the next version will be suitable for more robust applications.
Cliffb, I'd like to know more about your environment, I've ventured in the Tb range with Oracle only and it would be great to hear some hands on experience with SQLServer.
Thanks in advance for any insight