|
I have worked with 300 million rows in an Oracle database and yes it handles them well however... so will a SQL Server database.
What matters is how the indexes and partitions are set up on tables, as access to tables with this number of rows will be considerably affected by these decisions.
With 100+ million rows you are most likely going to need to partition tables.
[edit]grammar correction[/edit]
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 11-Feb-14 13:24pm.
|
|
|
|
|
GuyThiebaut wrote: partition tables
That could be a good idea, especially if you have much to read - however it can work without...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Oracle because it has more security than SQL.
|
|
|
|
|
I'm not familiar with Oracle. Would you care to expand on that statement? What security features does Oracle provide that are missing from MS SQL?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
What's funny but totally expected is that both Oracle and Microsoft claim that their database has the better security.
I'm of the opinion that the safer product is the one with the better DBA.
And since SQLServer is easier to run out of the box...
|
|
|
|
|
Really? Can you elaborate on that?
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Agent_Spock wrote: i am not sure that it can manage 100 million record
According to the Microsoft document "Maximum Capacity Specifications for SQL Server":
Rows per table: Limited by available storage
Database size: 524,272 terabytes
So unless each of your rows is going to exceed 5.36 gigabytes, you should be fine. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Agent_Spock wrote: sql but i am not sure that it can manage 100 million record.
I've worked with a SQL database that had a single table with a few billion rows (Inventory type stuff). So, yea SQL can scale as far as you need it too.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
and sir, is the searching good (fast).
|
|
|
|
|
Yup, never a problem. The table was indexed for the usage.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Does "Indexed" mean using primary key as filter or something else?
|
|
|
|
|
We work with both Oracle and SQL Server in those sort of volumes and I will reluctantly state that Oracle handles the volume better than SQL Server. However the reason may well be that for Oracle you generally need a dedicated DBA to drive the bastard. Also look into the comparative costs of purchase and support of the 2 systems, Oracle is substantially more expensive than SQL Server.
We have a rule of thumb that Oracle is used for enterprise apps and SQL Server for departmental apps.
[pet hate] Oracle also forces you to use UPPER case naming [/pet hate]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: However the reason may well be that for Oracle you generally need a dedicated DBA to drive the bastard
I believe you're onto something there.
Mycroft Holmes wrote: Oracle is substantially more expensive than SQL Server
I wonder how much of that's a myth? For us it was a little bit cheaper actually.
Mycroft Holmes wrote: [pet hate] Oracle also forces you to use UPPER case naming [/pet hate]
Grumble grumble grumble.
|
|
|
|
|
Jörgen Andersson wrote: I wonder how much of that's a myth? For us it was a little bit cheaper actually.
But don't you have to pay something like 22% of the original cost every year in "maintenance charges"? Whereas, once you've bought SQL Server, you only have to pay if you want to upgrade.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
You don't need to pay the maintenance fee. You can keep on using it as long as you want to. What you're missing out on is support and "windows update" aswell as upgrades.
And that support is of a wildly varying quality. If you call in the morning, someone with a very strong Indian accent called "Kevin" answers, and if he understands my problem, I won't understand his answer.
Anyway, if you have SQLServer you still have to pay for support and upgrades, don't you?
It's not an apple to apple comparison.
What you can see is that Oracle is trying to price themselves into smaller systems while MS is trying to gain grounds among larger systems. Because when you have a large server farm Oracle is considerably more expensive.
|
|
|
|
|
Jörgen Andersson wrote: Anyway, if you have SQLServer you still have to pay for support and upgrades, don't you?
You have to pay for upgrades to a new version, or to a higher edition, and you have to pay if you want someone from Microsoft on the phone helping to diagnose a problem. However, service pack (which now seem to be a thing of the past) and cumulative updates are free.
Jörgen Andersson wrote: Oracle is trying to price themselves into smaller systems
I'd have thought that most small systems would be using the Express edition of SQL or Oracle, which is free.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yes, but the express editions only use 1 GB of memory. It's good enough for CRUD, but very fast growing to small when you need to analyse data.
That's when the standard edition comes into play. That's competitively priced, in contrast to the Enterprice edition which is a class of its own.
|
|
|
|
|
I agree with them who said that both Microsoft SQL Server and Oracle can do so.
When you decide for one of it, note that Oracle is more complicated. SQL Server will handle many things for you automatically - like autoincrement values for keys, while with Oracle you have to setup a sequence, and a trigger for linking your table to the sequence on the appropriate events. In .Net languages, an empty string is not null, but for Oracle there is no difference - and meanwhile I say: Oracle is right from the view point of requirements. But it may cause problems for the programmer. And there are several more such oddities.
|
|
|
|
|
Bernhard Hiller wrote: In .Net languages, an empty string is not null, but for Oracle there is no difference - and meanwhile I say: Oracle is right from the view point of requirements.
I think you'll find that a lot of people disagree with you on that one - including the ANSI SQL 1992 standard.
Of course, Oracle's weird behaviour pre-dates the standard, and can't be changed for backwards-compatibility reasons.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard Deeming wrote: Oracle's weird behaviour pre-dates the standard, and can't be changed for backwards-compatibility reasons.
Could be fixed with a compatibility setting, just like on SQLServer.
I believe they won't do it because it's one of the things that gives them an upper hand on performance.
|
|
|
|
|
I know that, and in the past I thought that string.Empty and null must be different.
But look at a process before software: a user fills in a paper form. For a question he does not provide an answer. Is that null ("'answer' is not set to an object"), or did he answer with "string.Empty"?
And exactly that's the point why I agree with Oracle, though it causes pain in programming (and providing compatibility to a "wrongly-designed" Microsoft SQL Server database where string.Empty was allowed but not null... Yes, I had to experience that, and coursed about Oracle (and still do), but here they are right: if a column accepts string.Empty, that's equivalent to accepting null.
|
|
|
|
|
how much pain it will cause it to the developers doesn't matter, sir but which database works most efficiently which matters here. (So Oracle, SQL or something which is coming new)
|
|
|
|
|
Agent_Spock wrote: how much pain it will cause it to the developers doesn't matter
Now there's an helpfull attitude; aren't they the ones who have to support your sh*t?
Oracle, MySql and Sql Server can handle large amounts of data. Program against the IDb* interfaces and speak SQL92 - download the trials and TEST it. Have some console-app generate 100 million records if that's the kind of data you expect.
Don't expect to be databinding them to a gridview, regardless of your database. As soon as you have the test-setup, you'll also experience it's speed. Next you'll be learning what an index is, and find that all three db-servers support them. Aw, and yes, the schema does matter.
..and do take in account that the budget is a constraint; if you can afford a harddisk and Sql Server, you could also afford MySQL and an SSD-drive. Hard to predict who'd be faster out of those two
Agent_Spock wrote: manipulate 100 million records which configures with c# win. form.
Did you say "manipulate"? Didn't you mean "store"?
--edit;
http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems[^]
Hint: you'll need support for indexes, partitioning, and might want to take a look at both capabilities (do you need support for each type? each join?) and max. sizes of tables and database.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
modified 12-Feb-14 13:04pm.
|
|
|
|
|
yes, i mean store and search. As my work is little tricky it has to search through 100 million records every hour.
modified 14-Feb-14 6:46am.
|
|
|
|
|
You have to manipulate ALL of those 100 million records every hour?
Or is it just some, and then what percentage?
And what kind of reporting do you need to do? On how many records?
Fact is that it's not so important which DB you're having as long as you're just doing CRUD.
The size of the database is also not very important for the performance (Were talking about a factor of a couple of hundred times larger for every new level in an index, assuming B-Tree index).
It's the number of transactions! And how many records that are affected by the queries.
So the limiting factors will be the hardware (mostly the harddrives), the configuration of the hardware (It doesn't matter if the drives are fast if they're in a RAID5), and the indexes.
If you have no indexes at all, all inserts will be ridiculously fast, but querying or updating will be just as ridiculously slow.
Adding every index you can think about will make both inserts and updates ridiculously slow instead, while the queries will be faster.
Note that updates needs to both read and write.
Finding the right indexes will give you a good balance.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
