DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Report speedup

  1. #1
    Join Date
    Oct 2000
    Posts
    123

    Angry

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width