-
I need sombody help me with speed up the ORACLE report when client and server separated far away.
I built a report based on SQL query:
select distinct col1, col2, function1(col1) as alias1, decode(....) as alias2, function2(col2) as alias3
from table1
where condition1 and
condition2(involving one parameter passed from another form)
ORDER BY column1, column2,......;
where function1, function2 is any function located on server side.
When it runs locally, the speed is OK; but if connected to another database server(several miles away), the report is very slow, it alway "formatting" every page for a certain period of time.
I need some advice on how to increase the performance by all means. Please help.
Thanks
-
Okay, this is just a guess. Is what this report running in an Oracle Product? Does this Oracle product have a local PL/SQL engine? If so, this could possibly be your problem. Mind you, I haven't used any of Oracle's other products (Forms, Designer, etc.), but I do remember reading that some have local PL/SQL engines. This *may* mean that, since you are using PL/SQL functions in your SQL, maybe the local engine is parsing the statement for you, calling over to the server to get the raw data for the rows, and then for each row, maybe calling back out to the server again to resolve the PL/SQL function call. Such a setup would result in an abnormal growth curve of response time over distance.
Can anyone confirm or deny this theory?
- Chris
