Sign In

Tips and Tricks

This document highlights some features and usage patterns we hope are useful in the development of your applications.

Dedicated Server Notes

If you have an account with a dedicated database server please refer to the Dedicated Server Notes for additional documentation.

Manually Caching A Federated Table

Accessing a large amount of federated data for analysis can be slow, especially where joins and large tables are involved. Often a better solution is to create a local copy of the data at periodic intervals to create a data warehouse of sorts. Although most of our Smart Connectors support automatic caching, sometimes creating an actual copy within the database itself is needed for complex joins or other reasons.

You can accomplish this with a Scheduled Event that runs periodically and refreshes the data (see Tools > Scheduled Events...). The simplest way, if the data isn't too large, is using a commands like the following within a Scheduled Event:

CREATE OR REPLACE TABLE MyLocalTable_NEW AS SELECT * FROM MyFederatedTable; ALTER TABLE MyLocalTable_NEW ADD PRIMARY KEY (Id), ADD INDEX (SomeOtherColumn); RENAME TABLE MyLocalTable TO MyLocalTable_OLD, MyLocalTable_NEW TO MyLocalTable; DROP TABLE IF EXISTS MyLocalTable_OLD;

Dynamic SQL in Stored Procedures

For the very advanced power user, you can use dynamic SQL within a stored procedure using the EXECUTE IMMEDIATE statement. See the EXECUTE IMMEDIATE documentation for details.

One potential use case is to create a stored procedure that is called via the API with dynamic values passed into the procedure as a JSON parameter.