Hello again - I have a question about building stored procedures in Pervasive. I use Pervasive with an accounting application and need to build a new Crystal Report. I have bulit the report using an ODBC connection to Pervasive and unfortunately have run into some serious performance issues. I am confident that the performance issue is not in the query since I am running the same query in MS Access (linked tables) and it is processing in a matter of seconds.

I believe the root of the problem is in the data relationships. In Crystal, I am running 2 different queries, and then using a left outer join on a table.

Is it possible to build a materialized view in Pervasive? This will prevent the SQL from running everytime we run the report. I could schedule this to refresh every hour and then the report can be available on demand.


Thanks again!
Cheers,
ian

Short answer, no. There is no "materialized view" in Pervasive. There isn't way to prevent the SQL from being executed everytime.

One comment though, if the query is fast using linked tables (to the same PSQL database) in Access and is slow in Crystal then the problem is not in the PSQL engine. PSQL does not distinguish between different applications. Crystal may be the culprit here.

You might be able to create a Stored Procedure to execute the 2 queries and the left outer join. You'll need to use the RETURNS clause in the Stored Procedure to get records back. A View might be a better option but I'm not sure what your SQL is. A View is only really good for a single SQL statement.