SQL Server, SQL Server Express, and SqlDataSource Control

Re: One big table vs. small ones – multi language application, 1 500 000 rows per each language

MarwCzech — Mon, 06 Jul 2009 06:17:14 GMT

Hi all,
thanks for all the useful opinions. I have decided to store the data in a big table. If I’ll see the performance of the Views is not optimal I’ll try to use 4 tables instead the Views and update them once a day.

Thanks for help.

Marw

Re: One big table vs. small ones – multi language application, 1 500 000 rows per each language

integrasol — Sun, 05 Jul 2009 09:14:53 GMT

I disagree with splitting into smaller tables, but I certainly agree that a clustered index should be added, if not already, to the tables, preferably the ID columns, and non-clustered indexes for any field that is searchable, because of the number of rows.

Re: One big table vs. small ones – multi language application, 1 500 000 rows per each language

malcolms — Sun, 05 Jul 2009 08:55:03 GMT

It would be a good option to split this table up into smaller tables. That way there's less data to search through so your performance will increase.

Just make sure you have your clustered and non clustered indexes as without them, you'll have a heap and CRUD work will be painful.

Re: One big table vs. small ones – multi language application, 1 500 000 rows per each language

Naom — Sun, 05 Jul 2009 04:55:01 GMT

I agree with the previous poster - to me the problem sounds like a single table partitioned by language.

Re: One big table vs. small ones – multi language application, 1 500 000 rows per each language

integrasol — Sat, 04 Jul 2009 13:28:25 GMT

I think this is hard to call, especially without doing any load and performance testing. However, upfront I'd go with the single table solution and an associated language table. I would then establish a baseline and continue to monitor the performance, and if need to be, consider a horizontal partitioning of the rows, potentially based when it was inserted or similar.

One big table vs. small ones – multi language application, 1 500 000 rows per each language

MarwCzech — Sat, 04 Jul 2009 08:30:26 GMT

Hi all,

I’m trying to do an optimal database design and I’m stuck. In a nutshell what I have and what I’m looking for:

- There is a multi language (4 countries) application

- For each language I need to store 1 500 000 rows per year

- The table has only 4 columns and one FK

- There is a view selecting in the actual country: employee, sum of top 10 salaries, count of work shift in a month

- Insert into DB is 1 a day, selecting from the view is 1000 a day

The question:

Shall I store data in one big table with 4 500 000 rows per year and select with language condition (Sample 1) or separate data into 4 small tables (Sample 2)?

Sample 1:

------------------------------------------------------------------

T_Results

---------------

IdResult

IdImport

Employee

Salary

T_Import

----------------

IdImport

ImportDate

Language

Sample 2:

-----------------------------------------------------------------

T_Results_EN

----------------

IdResult

IdImport

Employee

Salary

T_Import_EN

----------------

IdImport

ImportDate

T_Results_DE

----------------

IdResult

IdImport

Employee

Salary

T_Import_DE

----------------

IdImport

ImportDate

Thanks a lot!

Marw

Hi all,

I’m trying to do an optimal database design and I’m stuck. In a nutshell what I have and what I’m looking for:

There is a multi language (4 countries) application.
For each language I need to store 1 500 000 rows per year.
The table has only 4 columns and one FK.
There are 4 views selecting from the countries: employee, sum of top 10 salaries, count of work shift in a month.
Insert into DB is 1 a day, selecting from the view is 1000 a day.

The question:
Shall I store data in one big table with 4 500 000 rows per year and make the views with language condition (Sample 1) or separate data into 4 small tables (Sample 2)?

Sample 1:
----------------------------------------

T_Results
IdResult
IdImport
Employee
Salary

T_Import
IdImport
ImportDate
Language

Sample 2:
----------------------------------------

T_Results_EN
IdResult
IdImport
Employee
Salary

T_Import_EN
IdImport
ImportDate
Language

T_Results_DE
IdResult
IdImport
Employee
Salary

T_Import_DE
IdImport
ImportDate
Language

Thanks a lot!
Marw