which database is faster? MS-sql, oracle, mysql, ..

I'm trying to develope a HUGE database with lot of informations... like library book information store into... so people can search for the title of books, etc... via web-based search engine...

Which database would you recommend for faster searching/fetching from database?
MS-SQL Server, Oracle, MySQL, Access, etc...

I know MySQL is very slow when the cpu shoot up to 100% while searching...


is there MS-SQL forum where i can get more information and help just like this devshed forum?

MSSQL Server and Oracle are known good vldb database systems, along with db2, informix & a couple others.

MySQL also supports some vldb's

Access is more useful for small low-traffic db storage.

www.sqlteam.com is a good sql server site.

Not in your list, but I'd recommed also checking out Postgres and Firebird if you're going to do a comparison for your needs.

If you want large scale DB systems and your mangers/IT people want a company they can go to for tech support or to make them feel better I'd say you want to go with Oracle or MS SQL if you have the $$.

If this is a personal project and you don't have the money or don't need "official" tech support I'd say Postgres would suit the job well. Not only does it have a larger enterprise level feature set when compared to MySQL, and is more standards compliant, it is supposed to scale better under heavier loads as well.

One caveat about Postgres, it is NOT meant to run circles around other databases from a default installation. The default installation is meant for compatibility on a variety of systems and levels of hardware. If you don't want to have to tweak settings and learn a little about the internals of your DB, then Firebird or MySQL may be your best bet.

One of the things that some people who take a quick evaluation of PG do is to fire up a default install, run some prelim benchmarks and say it's slow without tweaking system settings or doing routine things like VACUUM ANALYZE.

HTH,

-b

thanks for info, i will go with MS SQL. $ is not a problem.

I'm trying to figure out how to design the database. This is going to be big database. I guess i would put all words start with letter A will be in tableA, then all B in tableB, then all C in tableC. The tableA will contain like able, apple, advanced, able, about, etc...
When user type the keyword "apple", it will search tableA and get the detail of "apple". What do you think of this method? If there better way, let me know.

Well, depending on how huge your database is going to be I'd say not to break it out into table partitions like that. Maybe it's just that I haven't worked with data on your scale, but it seems that the reason you're going to paying and searching for the best performing/scaling database would be so that it could handle the data the way it should be laid out.

Again, this is just my opinion, but it seems like joins over all those tables just for the names would be a nightmare, and searching for items without an exact match for the first letter in the title would yield less than optimal results and/or performance.

Hopefully rycamor, rod k, r937 or one of the other known gurus around here can add something.

-b

Sorry to "double post" and bring up Postgres again, but I found this thread from the Postgres mailling lists that might interest you. While you've already chosen MS SQL, I believe the logic should still apply.

PG mailing list thread

HTH,
-b

hey, bcyde, thanks for the kind words, but i'm not that kind of guru

there are data architects and there are database administrators, and the questions of database speed and performance are well within the realm of the latter

in my humble opinion, anyone worried about the performance and speed of a database just doesn't have enough hardware

that said, nobody has mentioned sybase

i saw someone elsewhere make a comment the other day about sybase having beaten all comers in some kind of linux benchmark

tommy, if you're using microsoft sql server, put all letters into one table, it can handle that

and that's the extent of my DBA skillz0rz

rudy

Bcyde and rudy as usual have some great advice.

I will have to make the same disclaimer as rudy. Not that I haven't spent some time thinking about very large databases, but I have not yet had the honor of administrating one. Anyway, in my (purely theoretical) experience, it's impossible to give better advice about table partitioning, etc... without knowing more about some things:

- when you say "HUGE", approximately how big do you mean? 15 GB? 300 GB, 1.5 Terabytes? 8 Petabytes ?

- how many concurrent users do you want to support?

- what is the logical structure of your database? (forget about performance, just describe ideal table layout, foreign keys, etc...)


Exactly right. The data architect should focus on logical design first. (Favorite quote from Donald Knuth: "Premature optimization is the root of all evil") Once the logical design is complete, get an experienced DBA to handle the physical storage implementation. There are plenty of ways with views, "materialized views" and stored procedures to break up tables in the back end, even storing on different physical disks, while leaving the front end looking logically intact.