Databases

[Grok]

Market leaders for PC RDBMS are Oracle and Microsoft SQL Server.

For mainframe, IBM DB2, DB/400, et al.

Generally Microsoft shops run SQL7/SQL2000.  IBM/Java shops show equal tendency to run Oracle or DB2/DB400.

In pure MIPS, PCs kill 400's.  I use an iSeries AS400 here, 8 cpu's, DB/400, and am not impressed.  Our MS SQL 7.0 running on dual P3 1Ghz, 1GB RAM, walks all over the 400's query performance.  We're upgrading the SQL Server to dual Xeon 2.0 Ghz, 2GB RAM, 800Mhz FSB, and seven 10k rpm 83gb u/w SCSI-3 drives.

To upgrade the 400 like that, it would cost $500K to a million.  To upgrade a PC, you can do it cheaply for $15000, or correctly for $25000+.

This is slightly off topic from PHP/MySQL tips eh?  Sorry.

[Banana fanna fo fanna]

Oh well. How does SQL server compare to oracle in terms of amount of data it can scale to? I always assumed Oracle kicked the shit out of everyone else, but then again, I have no real fact on which to base that on.

[Grok]

Oracle is the more mature and full-featured RDBMS.  I don't have any speed comparisons.  Scalability is nearly linear for both servers up to n processors and n clustering servers.  I think I read once that SQL 7.0 was linear up to about 32 processors, while Oracle stayed linear up to 64.  Then SQL 2000 came out and is apparently equally linear scalable.

Note that I'm recalling this from vague memory of articles I might have read from possibly inaccurate resources.  At work we run two AS/400's with DB/400, several SQL 7.0 Servers, and one Oracle server.

Because the production servers are DB400 and SQL7, and the Oracle has no performance requirements (other than it work continuously and not lose data), I don't have comparative information on it.

Oracle certification currently pays more than equal certification in Microsoft SQL admin or programming, especially programming.

[herzog_zwei]

Published DB benchmarks are harder to come by nowadays (fewer people seem to be willing to do it as well as NDAs that prevent the numbers from being published).  From what I hear, the only way to (legally) see published numbers from Oracle's DBs are if you sign an NDA (which also prevent you from disclosing those numbers).

Oracle is the leader in DB performance, but you pay the price as well.  MS SQL Server would be a cheaper alternative and you can throw more (cheap) hardware at it to get more performance and get more bang for your buck (assuming you can spare all the space to house the cheap hardware).  MS bought the code for Sybase a while back and turned it into MS SQL Server.  The two are closely related but have branched off for many years.  I haven't used Sybase for a long time but from what I hear, Sybase is still very fast for high traffic sites (which probably means MS SQL Server would perform about the same).

There was a benchmark done 2-4 years ago comparing Oracle, Informix, PostgreSQL, and I believe one other one (maybe Sybase?).  Oracle blew all of them away but PostgreSQL surprised people and performed better than expected (though it tended to be the worse performer out of those databases, it showed that PostgreSQL scaled decently).  These were for databases with GBs of data.  I couldn't find a link to that article with a quick search so you'd have to hunt around if you want more details.

You can find some (old) benchmarking results at:

http://jamesthornton.com/acs/benchmarks-ora817-pg703.html
http://www.mysql.com/information/benchmark-results/result-db2,informix,ms-sql,mysql,solid,sybase-relative.html
http://www.mysql.com/information/benchmarks.html

(BTW, one number that were removed from the first article was: Oracle 8.1.7: 34 seconds per 10K inserts, compared to PostgreSQL 7.0.3: 5 min 16 seconds per 10K inserts)

For most people, a cheap SQL server like MySQL or PostgreSQL is all they'll need.  PostgreSQL is the better general choice over MySQL.  For small DBs where you're okay with some potential data loss, MySQL would be better for its raw speed (when used with non-transactional tables).  I'm not sure how well MySQL scale these days, but 2 years ago, you wouldn't want to use it for big DBs.

[Noodlez]

What's your definition of a "big database?" I'm thinking >= 500MB.

[Grok]

Haha.  How things have changed.  If our database couldn't do 10k inserts in 34 seconds I'd be ordering faster hardware.  10k on our SQL Server takes about 8-12 seconds.

Noodlez, big database is multi GB.  Many phone companies have databases that are hundreds of GB.  At the insurance company where I work, one database is 1.5 TB, although it isn't an RDBMS, but a Btree type system for images.

EDIT:  I just ran a test.  33980 rows inserted in 29 seconds.  Each row was only 52 bytes.  Two unique indexes.

[Invert]

Just wondering how slow would a 1.5TB RDBMS be... lets say if you are using MSSQL 2k

[Grok]

Just wondering how slow would a 1.5TB RDBMS be... lets say if you are using MSSQL 2k

It depends on what you're trying to do.  If you're wanting to read back the content of all 1.5TB of data, well you are probably going to have memory size and network speed issues.

Otherwise it becomes just like any other sized database -- the choices and sizes of the indexes, whether they're clustered or not, the fill factor, how your file groups are organized, the speed of the DASD subsystem and other hardware, all factors.  It also depends on the types of queries you run and how suited your database design is for it.  Are there many triggers?  What types of constraints and how many?  Everything you do has a cost.  SQL Server will compute and tell you the cost of everything, even suggest optimizations, or do the optimizations automatically, but its all about choices and being a good architect.

[Tuberload]

Oracle certification currently pays more than equal certification in Microsoft SQL admin or programming, especially programming.

This is off topic so I'll understand if it's deleted.

I have the Oracle Java 2 Certification and was just wondering if you had any idea how beneficial it is to have for resume purpose? I have also been planning on getting the Sun Java Certification + any of their Web Development related certs. Now the question here is are they even worth their money? From what I understand they are. Any outside opinions, preferably from people already in the IT Industry, would be appreciated.

[Grok]

I have the Oracle Java 2 Certification and was just wondering if you had any idea how beneficial it is to have for resume purpose? I have also been planning on getting the Sun Java Certification + any of their Web Development related certs. Now the question here is are they even worth their money? From what I understand they are. Any outside opinions, preferably from people already in the IT Industry, would be appreciated.

The best answer to your question can be found by searching www.computerjobs.com!  Type in the keywords for the skill you have and look at the number of jobs available.  == DEMAND.   Demand determines the amount of pay, except for specialties.  If there are more Java programmers than jobs, you won't see many jobs available, and the pay will be low.

The tech market sucks worse now than in the last 10 years.  It's flooded with people who are clueless but call themselves experts and can't do a damn thing right.  Companies are shipping many coding jobs overseas where the rate is $15/hour or less.

If you aren't settled on a career, I'd suggest doing programming for fun, but do enterprise network architecture/engineering as a career.  This involves designing and building computer rooms, networks on a large scale.  This is the one consistent growth field over the last 20 years.  Look at Cisco, nearly the only player in the game anymore.

Then go home and program for fun or use your programming skills to make you the top network architect.

[Tuberload]

Thank you Grok for the information.

[WinSocks]

Like most Databases it's not wise to substane such a high volume database, especially in MSSQL2k, a 500mb db would just be utterly retarded to have on a network. The Transfer rate and the traces would take too long with the vast amount of connecitons to that  large database.

I never gotten into Oracle that much, but i just got my MCSE Certification for SQL Server 2000 Database Design and Implementation. Exam wasn't the funnest thing in the world, but people told me that Oracle can be Realted to SQL in some forms... would someone care to elabirate on that, or is what i hear is false?

[Grok]

Like most Databases it's not wise to substane such a high volume database, especially in MSSQL2k, a 500mb db would just be utterly retarded to have on a network. The Transfer rate and the traces would take too long with the vast amount of connecitons to that  large database.

I never gotten into Oracle that much, but i just got my MCSE Certification for SQL Server 2000 Database Design and Implementation. Exam wasn't the funnest thing in the world, but people told me that Oracle can be Realted to SQL in some forms... would someone care to elabirate on that, or is what i hear is false?

No offense, but this is why certifications really don't mean diddly unless accompanied by plenty of experience.  Your first paragraph demonstrates a complete lack of understanding of how a RDBMS server is positioned in a networked environment.  By the way, what was your score on 70-229 and which questions did you have the most trouble with?

MSSQL and Oracle are both RDBMS servers.  A server database differs from a personal database like MS Access, in that the application running on the server processes the query and sends back just the results.  A personal database application runs on your PC, and even if the database files (*.mdb in the case of MS-Access) are on the network share, the entire file must be opened and mostly copied to the client for processing.  It has gotten better in that the application can attempt to just read the indexes portion of the mdb file, but in no case is this ever faster than a true client-server relationship of a server-based RDBMS.

Depending on hardware, network, memory, and application requirements, a reasonable maximum for a reasonably busy MS-Access database on a network share would be 25 users and a 250mb database.  We have very busy MS-Access databases at our location that run 800mb, about 6 users, on a switched 100mb ethernet network, with 1gb fiber on the server backbone.

Our MSSQL7 database server instance runs on a dual 1ghz Xeon system with 1gb RAM, 200gb RAID 5 10krpm SCSI, and handles 400 nonconcurrent users hitting about 35 databases most of the day.  If you make an average assessment of CPU "business" as judged by the primary performance counters, it stays about 30% busy during normal working hours.  During batch jobs it might jump to 60%-80% but rarely is it 100% for more than a few seconds.  When it is 100%, it is only on specific counters, not overall utilization.

Hope this helps.