| Author |
Topic |
|
makimark
Starting Member
34 Posts |
 Posted - 2004-02-02 : 13:15:27
|
| Hiwhat what would be the theoretical (practical) maximum number of rows per table ? Reason: i have an application that imports small text files with cdr's - one month equalling around 750K records. would it be better to partition the months into tables or can i load a whole year 9million records into one table ? What would be the erformance hit as the tables provide reporting ? (High selects).thanksMark |
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2004-02-02 : 13:58:14
|
| The number of rows per table is limited by the amount of physical storage. The max size of a single database is 1,048,516 TB (terabytes). The max file size for data file within a database is 32 TB.Check out "Maximum Capacity Specifications" in the BOL for more information.- Eric |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-02-02 : 14:00:31
|
| Well, 9 million rows in a single table is large, but not super large. For performance, partitioning might be the way to go. I suggest testing both way to see how they perform. The single table solution with good indexes might be fast enough for ya.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-02-03 : 06:01:30
|
| search here for..."horizontal partitioning" and "performance"one of which is http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26669There's no one master answer to your problem....trial and error will point you in the direction of what's best for you. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-02-03 : 08:02:44
|
| Try both and see. Your hardware will probably play a factor, as will how you make use of your data, and if the partitioning is along useful lines that can limit the data that needs to be accessed during extraction.9 million rows is not a particularly big table.-------Moo. :) |
|
|
|
CLages
Posting Yak Master
116 Posts |
Posted - 2004-02-04 : 06:01:15
|
| you can use partition database, see BOLCarlos Lageshere a piece of helpPartitioning DataThe first step in building a set of federated database servers is to horizontally partition the data in a set of tables across multiple servers. Horizontally partitioning a table refers to dividing a table into multiple smaller tables, called member tables. Each member table has the same format as the original table, but only part of the rows. Each table is placed on a separate resource (files or servers) to spread the processing load across the resources. For example, a company assigns customer identifiers (IDs) from 1 through 9999999. The Customers table may be partitioned into three member tables, with each member table having an equal customer ID range.If used without views, horizontal partitioning would require applications to have logic to determine which member tables have the data requested by the user and dynamically build SQL statements referencing the tables. The application would require complex queries joining the member tables. Changing the member tables would also involve recoding the application. Views solve the problem by making the member tables look like one table. The SQL UNION operator combines result sets with identical formats into one. Because all the member tables have the same format, the result of SELECT * statements for each table have the same format, and can be combined using the UNION clause to form a single result set that operates similarly to the original table. For example, the Customers table has been partitioned across three servers (Server1, Server2, and Server3). The distributed partitioned view defined on Server1 is:CREATE VIEW CustomersASSELECT * FROM Customers_33 UNION ALLSELECT * FROM Server2.CustomerDB.dbo.Customers_66 UNION ALLSELECT * FROM Server3.CustomerDB.dbo.Customers_99etcetc |
|
|
|
lauramayer
Posting Yak Master
152 Posts |
Posted - 2004-04-13 : 13:06:30
|
| Good afternoon,A question related to this topic. We have a request to create a matrix that uses one SQL table that would have a MINIMUM of 42,550,000 rows with four fields. This is running over a token ring network, no I'm not kidding. I have to create a list of reasons why this is a supremly bad idea. I said the performance on this just to search that many records would be horrible. I have been told just figure it out your the programmer....Does anyone have a concrete examples that I can pass on?Thanks in advance.Laura |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-13 : 13:10:20
|
| What is the uppper limit of rows for your table? 42 million isn't huge, but it's a lot. Horizontal Partitioning can handle it.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-13 : 13:17:01
|
| What's the config of the box?Brett8-) |
|
|
|
lauramayer
Posting Yak Master
152 Posts |
Posted - 2004-04-13 : 13:30:04
|
| I'd have to check with the admin about specifics, but from what I understand its a server running one drive one partition for the database one for the operating system. That's it. Upper limit: I don't know it would depend on locations that the trucks drive to and from even an addition of one record could add hundreds of records to the table. Like this has cityA to CityB, CityA to CityC ect. Laura |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-13 : 13:32:08
|
| How many CPUs does the server have? How fast are they? How much RAM does it have? Is it dedicated to SQL Server? Which OS will be used? How fast is the drive? ...We need the server specs.Tara |
|
|
|
lauramayer
Posting Yak Master
152 Posts |
Posted - 2004-04-13 : 13:42:06
|
| Thanks Tara, I've asked. Hopefully they'll tell me.Laura |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-13 : 13:43:49
|
| BTW, one drive with a single partition is going to very slow for SQL Server. Given just that bit of information, the server will not be able to handle what you have mentioned.Tara |
|
|
|
|
Theoretical row limits