About SQL Partitioning
Note: To have the ability to partition tables, you must be running SQL Server, Enterprise Edition.
Why partition SQL database tables? It improves performance. Partitioning larger tables allows the application to search less data, and therefore partitioning helps speed the performance of searches and page-loads that require NetForum to scan the table in its entirety.
About SQL Partitioning in NetForum
You have always been welcome to partition SQL database tables that NetForum uses, but until now SQL partitioning has been invisible to NetForum. In 2015.1 and 2017.1, NetForum checks if a table is partitioned on its xxx_add_date field. If it finds this specific partitioning, NetForum automatically filters all the Find and List pages in iWeb to use the most recent partition.
If you are a database administrator, then the decision to partition rests with you. Abila can provide you scripts that will create table partitions based on the table's xxx_add_date. Consider your decision and plan accordingly. In your partition plan, also include a maintenance plan. Ask yourself the following questions:
- How will I decide which tables to partition? Some criteria to consider: how big is the current table, how fast does this table grow, and what will be the cut-off for current and future partitions.
- How often will I repartition to maintain relevant partition boundaries?
- How will I determine that other tables need partitioning in the future as they grow?
- If I am not already using SQL Server, Enterprise Edition, is the performance gain worth the increased price?
Note: Abila is available to help you during this planning stage. Please contact Abila to help you navigate the decision and to help you execute the partitioning.
When you run the scripts, the partitioning process itself can take hours, depending on the size of the original table and the number of partitions. Once the data is reorganized, NetForum's iWeb List pages will only show records from the most recent partition. iWeb Find pages that search the partitioned table will show an extra check box: Limit results to records added on or after [date] where [date] is the mm/dd/yyyy of the most recent partition. NetForum modifies the List and Find pages automatically in versions 2015.1 and 2017.1, no need to change any iWeb settings.