Migrating data after "Use Split Tables" is enabled

Posted by Community Admin on 03-Aug-2018 20:21

Migrating data after "Use Split Tables" is enabled

All Replies

Posted by Community Admin on 09-Oct-2013 00:00

I found some interesting articles below:

www.sitefinity.com/.../managing-languages 
www.sitefinity.com/.../configuring-sitefinity-to-persist-multilingual-fields-in-separate-tables-for-each-language

We are currently using 15 different languages and have not selected "Use Split Tables" option for splitting multilingual fields into separate tables.  From the second link above and #4 on that page, it states the following:
NOTE: If you have persisted languages in one table and then you configured split table mode, the languages that you have already used are not moved to split tables. Only newly added languages are persisted in separate tables. In Split Tables Ignored Cultures, you must enter the languages that you have been using before you switched to split tables.

We have a lot of content already loaded and since we are not splitting the languages I believe it is causing performance issues.  If we were to enable the option currently, how could we get our current content migrated into their own tables?  Does a tool exist for something like this?  Has anyone experienced anything similar?  

Please let me know what options we have if any.

Thanks!

Posted by Community Admin on 12-Oct-2013 00:00

I do know that existing data will not split into their own tables and I am not aware of any tool that splits existing content into separate tables based on language.  Has anyone had experience with this or know of any manual way to complete this?

Is anyone aware of any performance improvement that "splitting tables" would provide versus not splitting them?  Does anyone have any experience with either splitting or not splitting tables.  Currently we have 15 languages and did not "split" the tables.  If we know of a worthwhile performance improvement it may be worthwhile figuring out a way to manually split the current data on the site.  Any help or advice would be beneficial.  Thanks in advance for any information provided!!

Posted by Community Admin on 14-Oct-2013 00:00

Hi,

The spiting of tables for multilingual is only supported for newly added languages the old language related data will remain in a single table.

To explain the difference between single table storage and split tables:
Single table storage: all language related data will be stored in a single table for example sf_news_items table stores the data for news items and language related data like Title, URL, Summary in french are stored as additional columns in this table making the table columns increase in number. SQL server have a limitation for maximum 1024 columns in a single table so the formula to calculate the
the number of Lstring fields * number of cultures + additional fields of the current type. This number must not exceed 1024.

Split tables: a new table is created for each added to the site after split tables are enabled using foreign keys to make a reference to each language version of the item.

The already created data in the site will remain in single tables even if a particular culture is deleted, split tables are enabled and this culture is added again. This is because when a culture is deleted the columns with the culture related data remains in the DB.

Regards,
Stanislav Velikov
Telerik

Do you want to have your say in the Sitefinity development roadmap? Do you want to know when a feature you requested is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items

Posted by Community Admin on 14-Oct-2013 00:00

This does not answer any of my questions.  

Is there a performance increase if Split tables is used?
How is performance affected if tables are not split since we currently have 15 languages enabled?
Is there a manual way to split the current data into their separate tables?
Does a tool exist that would migrate our data into their own tables?

Posted by Community Admin on 16-Oct-2013 00:00

Hello,

To answer the questions below
Is there a performance increase if Split tables is used?
How is performance affected if tables are not split since we currently have 15 languages enabled?

The performance improvement is mainly in avoiding the extension of a single database table with multiple columns added for each language (when a language is added it will create its multilingual columns in single table), this allows all multilingual fields to be moved to another tables and in databases like MySQL to support multilingual as in MySQL tables can`t dynamically create additional columns.

The other performance improvement is enabled when enabling fetch strategy for multilingual (here is a screenshot) Use Fetch Strategy option controls how the multilingual fields are loaded from the database. If it is enabled, only the fields for the current language are loaded with the initial item query and the other languages fields are lazy and are loaded on demand. Otherwise, if the option is disabled, all multilingual fields are loaded with the initial item query.

Is there a manual way to split the current data into their separate tables?

Does a tool exist that would migrate our data into their own tables?
For now there is no supported way to perform migration from single table multilingual into split tables, the migration doesn`t involve just moving the data in separate tables, but the ORM used by sitefinity have to recreate the data from a single table into multiple ones and remove single table columns.

Regards,
Stanislav Velikov
Telerik

Do you want to have your say in the Sitefinity development roadmap? Do you want to know when a feature you requested is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items

Posted by Community Admin on 18-Oct-2013 00:00

If you use the site sync feature going from one site with multiple languages storing data in a single table, syncing to a site with multiple languages that store data in split tables, would that allow you to migrate the content into the new structure?

Posted by Community Admin on 23-Oct-2013 00:00

Hi,

The site sync module can connect to a site that is a copy of the source site and an empty sitefinity site will not be connectable.

We have been searching for an approach at our end to sync a content populated site to an empty one and at the moment we can`t offer a bullet proof approach to migrate the data from single page storage for multilingual to split tables.
This is a valid scenario that is indeed very useful, however I can`t recommend an approach that will get the data moved to split tables.

Regards,
Stanislav Velikov
Telerik

Do you want to have your say in the Sitefinity development roadmap? Do you want to know when a feature you requested is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items

This thread is closed