[Wish]_Automatic Table Partition definition and management for Entities

In several production environments we've been seeing tables as large as several millions records. This will impact scalability and performance.

In that regard I suggest allow defining in Service Studio entities that will automatically map into a partition table object on the database. By defining a partition column (key that's usually a datetime) the entity will be in fact several separated tables in the database and managed by the database's partitioning function based on the defined key.

At the moment, both SQL Server 2005 Enteprise Edition and Oracle 10g Enterprise Edition supports this feature.

More info on this on http://msdn2.microsoft.com/en-us/library/ms345146.aspx for MS SQL Server 2005.

Cheers

Miguel João

Hi,

In the energy solution (that deals with huge sets of data), this feature is not like a "nice to have". It's really a demanding, otherwise the applications will be unusable (slow queries).

By the way, there's someone with expertise in this matter?

I'll buy that feature.

Pedro

Some related and important info regarding this feature :

- Only available with Enterprise Edition : http://msdn2.microsoft.com/en-us/library/ms143761.aspx
- To user this feature, servicestudio must allow the creation of partition functions and schemas
- The process to create a partitioned table from a non-partitioned table (our default entities) is painful and always requires a data migration
- There's no user friendly interface in the SQL server management studio to perform this configurations/operations. Scripting is the only choice.

Some useful links :
http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/
http://redmondmag.com/features/article.asp?EditorialsID=576
http://www.microsoft.com/sql/editions/enterprise/comparison.mspx

I also attach a great presentation.
Bye,
Pedro

Hi Pedro,

this is a really advanced feature, and doesn't fit correctly inside service studio language due to the amount of knowledge you need to invest in the definition of each partitioned entity (the partition function, the table spaces, among other settings). I'm not saying this is not relevant in a project with a large dataset, but you can already tune the existing entities in your database with partitioning (for both Oracle and Sql Server). Service studio will respect and maintain your partition definitions during 1cp operations.
Cheers,
Lúcio

Hi Lucio,

Getting back to this issue...
Do you mean that we can:
- create a table on service studio
- alter it to be partioned on sql server management studio
- continue using 1 click publish on service studio on it, even when changing its structure (add/remove columns) ?

Thanks in advance.

Regards,
Pedro Moita.

@Pedro

"- continue using 1 click publish on service studio on it, even when changing its structure (add/remove columns) ?"

No, it will not work, if you remove a column, without using Service Studio, its going to say ok the column is missing, yet it exist in the eSpace, so lets create it again!

In the case where you added a column, without Service Studio, your agile platform application will not know about your newly added column either!

Therefore the answer is no, it will not work. 

Note: If you created the entity tables externally and used integeration studio to map the tables so that you can use it in your agile platform application - its a different story.

Hi Robert,

 "- continue using 1 click publish on service studio on it, even when changing its structure (add/remove columns) ?"

I meant changing the structure always using service studio, and never altering the tables directly.

Regards,
Pedro Moita.