When a desired view (say V) in my local database should contain data from local and remote data and those are quite complex (and performance is not sufficient), I often use a stored procedure instead of a view, and the procedure (say SP) would return the same data as the view but cache the remote data locally.

You can still use SP pretty much like a view because SQL Anywhere allows procedures that return a result set to be used in the FROM clause of queries, too. One main difference would probably be that any filters you would do on the view's columns might be better used as parameters to the procedure

So procedure P would do the following.

• declare a local temporary table (say LT) with the same schema (or a subset) as the proxy view (say PV) and select (possibly filtered) remote data in that local temporary table
• use the contents of the temporary table within your joins to your other local data and return the result set based on that
• As the local temp table is automatically created and dropped when the procedure is called, there's almost none overhead when it is declared as NOT TRANSACTIONAL.

Here's a short pseudo-code sample:

create procedure SPT_MyMixedResult(...parameters)
result (... description of result set columns...)
begin
   declare local temporary table LT like PV
   not transactional; -- possibly also adding a PK in the LIKE clause

   insert LT select * from PV
   -- where --- possibly applying parameters to filter remote data
   -- possibly add indexes on the local temp table if needed

   -- Now join with your local data... providing the result set
   -- as described in the RESULT clause
   select * from LT inner join SomeOtherLocalsTables
   order by ...
end;

We've found using selectable stored procedures (with proxy procedures on the "central" database mapping to the real procedures on the other databases) by far the most efficient way of combining queries from multiple databases. It means you control where the filtering of records happens, so that you scan be sure you are using the right indexes etc.

Look at the AT clause in CREATE PROCEDURE