Fix possible scaling issues

[zachsa]

There are a couple SQL queries that may need to be optimized in the future if a large number of projects / deployments are added.

In particular, the SQL query for tracking / asserting the 'rollup' relationship of projects into various tenants can be slow when called without a submissionId parameter (this query - https://github.com/SAEON/national-climate-change-response-database/blob/stable/src/api/src/lib/sql/merge-tenants-submissions.js). It would be better to refactor the function to NOT allow for calling without submissionId. And then refactor code where this function is called to loop over submissionIds and only call the mergeTenantsSubmissions function with both params specified potentially many, many times. Alternatively it's possible to increase the SQL Server connection timeout settings - https://github.com/SAEON/national-climate-change-response-database/blob/stable/src/api/src/mssql/pool.js#L20. But that is not the best idea since this would result in longer and longer startup times

• When creating a new tenant: https://github.com/SAEON/national-climate-change-response-database/blob/stable/src/api/src/http/create-tenant/index.js#L131-L136
• On startup, the application register all projects with all tenants (deployments). This was/is mostly helpful for development purposes but can be deleted from the production app (since the database should not be reset in production). But please move the logic into a README somewhere so that it's possible to run on a local computer when setting up the software for development: https://github.com/SAEON/national-climate-change-response-database/blob/stable/src/api/src/mssql/register-tenant-submissions/index.js
• The SQL queries used to generate the charts on the UI may need to be run eagerly if there are lots of projects added. It may be a good ideal to run the queries on a scheduled basis and persist the output into tables / materialized views (if those would work with cross joins). All these queries can be found here: https://github.com/SAEON/national-climate-change-response-database/tree/stable/src/api/src/graphql/resolvers/queries/chart/chart-types

[zachsa]

The easiest way to address the long running query on application startup is to increase the allowed connection time to sql server. This may be ideal, as the current process is useful for starting a dev environment