Unanswered: How to Solve Performance Issue for "View"
I have created view which is referring tables which are on the another server having thousands of records.
But when i use that view to retrieve the information it take to much time to execute the query.
How to solve the performance issue for this scenario. ???
as there is no materialized view like oracle.
Create View notificador
FROM dbsiif@Other_DBServer:notificador ;
before solving, first analyze the query plan.
First comment though is that view, as they are not materialized, and often(all the time?) more complex to execute than a plain join, and basically there is no intersting, at a dba point of view, to use views instead of joins, only if your target is writing shorter quey text...
First thing to do is to run your query from dbaccess, for instance, and execute a set explain on before, that will detail the real query plan.
SET EXPLAIN ON;
SELECT *FROM myview where conditions
Which version of informix do you use?
the explain file will be written as 'sqexplain.out' in the directory you are in.
Your problem can be lack of index, outdated statistics, or just reading too many rows transiting by the network, or a combination of all this...