Database Cleanliness and Best Practices
|Suggested Skills: Tessitura, SQL|
| Objective: In order to keep your mail2 system running cleanly and efficiently, we recommend periodically doing the following items. An explanation of each item is included.
Regularly clean up the email addresses located in your T_EADDRESS table.
Depending how your website is configured, and how data is input into Tessitura, the T_EADDRESS table in Tessitura can sometimes end up with extra spaces on either end of the address when it is inserted into Tessitura. We recommend you remove these spaces by running the following ad hoc query:
SET address = ltrim(rtrim(replace(address,char(9),'')))
WHERE address LIKE ' %'
OR address LIKE '% '
OR CHARINDEX(char(9),address) > 0
Clean up LTX_M2_ELISTS_EADDRESS
This table can get very large over time, especially for large organizations with lots of lists. As you delete or inactivate mail2 public or private lists overtime, you can also delete any rows in LTX_M2_ELISTS_EADDRESS associated with those lists, if you'd like. We do not do it by default in the event that you need a historical record of the opt-ins and opt-out associated with that list. In order to delete all of the rows in LTX_M2_ELISTS_EADDRESS that relate to an inactive or deleted mail2 list, run the following ad hoc query:
DELETE FROM LTX_M2_ELISTS_EADDRESS
where (m2_list_no in(select id from LTR_M2_ELISTS WHERE active = 'N')
OR NOT EXISTS(SELECT * FROM LTR_M2_ELISTS WHERE id = m2_list_no))
Update T_LIST so that lists older than "X" number of days have the TMS flag flipped to "N".
If your organization is constantly importing to mail2, and you end up with many list manager lists that show up in your mail2 list dropdown in the mail2 dashboard, it can be a good idea to have an automated routine that automatically flips the TMS indicator in T_LIST to "N" for any lists that are older than a specified amount of time.
By default, promotions are updated in Tessitura real time. For large deployments, this can slow down Tessitura because of the amount of transactions that happen against the database simultaneously. We recommend that you enable the async processing option for mail2 described in this article.