13 Replies Latest reply: Jan 2, 2014 4:30 AM by Ramani_vadakadu RSS

IR Report found 1 million record with blob files performance is too slow!

Ramani_vadakadu Journeyer
Currently Being Moderated

we are using

oracle apex 4.2.x

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

mod_plsql with Apache

 

Hardware: HP proliant ML350P

OS: WINDOWS 2008 R2

 

customized content management system developed in apex.when open the IR report have 1 ml rows found and each rows have blob(<5MB as pdf/tiff/bmp/jpg) it will be raising rows in future! but the searching performance is very slow!

how can increasing the performance?

how can showing progressing status to user while searching progress going on IR report itself?

Thanx,

Ram

  • 1. Re: IR Report found 1 million record with blob files performance is too slow!
    scott.wesley Guru
    Currently Being Moderated

    Poor performance could be due to a number of factors, before it even hits APEX.

     

    First ensure 'Pagination type' is "Row ranges X to Y', ie - not including Z.

     

    Then you can start looking at potential indexing issues. You could also (temporarily) not show the blobs to determine if they truly are impacting performance.

  • 2. Re: IR Report found 1 million record with blob files performance is too slow!
    fac586 Guru
    Currently Being Moderated

     

    Ramani_vadakadu wrote:

     

    customized content management system developed in apex.when open the IR report have 1 ml rows found and each rows have blob(<5MB as pdf/tiff/bmp/jpg) it will be raising rows in future! but the searching performance is very slow!

     

    Other than the aforementioned pagination aspect, this probably has little to do with APEX.

     

    Searching how? Built-in IR searching is inefficient on standard columns in the absence of suitable indexes. Even more so if you're trying to search LOBs in this way. Are you trying to search the LOB content? Or just metadata associated with it? Are you using Oracle Text to do so?

     

    Has it occurred to you that something as complex as a "customized content management system" may require customized reports built specifically for it, rather than built-in, one-size-fits-all (it doesn't) Interactive Reports?

    how can increasing the performance?

    Re: 2. How do I ask a question on the forums?

    Re: 3. How to  improve the performance of my query? / My query is running slow.

    how can showing progressing status to user while searching progress going on IR report itself?

    Probably not necessary given acceptable query performance. Does Google show "processing status" whilst searching?

  • 3. Re: IR Report found 1 million record with blob files performance is too slow!
    Ramani_vadakadu Journeyer
    Currently Being Moderated

    thanks for both of them,

     

    i search just 3 index values(date,text,number as defined values) at IR master table, but blob showing to each row.total display column 6 only.

    i set X to Y pagination!

    any other idea for this?

  • 4. Re: IR Report found 1 million record with blob files performance is too slow!
    fac586 Guru
    Currently Being Moderated

    Ramani_vadakadu wrote:

     

    i search just 3 index values(date,text,number as defined values) at IR master table,

    How?

    but blob showing to each row.total display column 6 only.

    I have no idea what this means.

    any other idea for this?

    Stop posting like you are sending telegrams and provide consise but complete information.

     

    Re: 2. How do I ask a question on the forums?

  • 5. Re: IR Report found 1 million record with blob files performance is too slow!
    Ramani_vadakadu Journeyer
    Currently Being Moderated

    please find my G.drive file which is we are using the apex IR report page heading(6 columns with blob).

    how?it could be clear for you!

     

    https://drive.google.com/file/d/0B_qZ9OVtwF4iMmF5a08tM0toTFU/edit?usp=sharing.

  • 6. Re: IR Report found 1 million record with blob files performance is too slow!
    TexasApexDeveloper Guru
    Currently Being Moderated

    Not really an APEX question as apposed to being a SQL Tuning question.. If you query is NOT tuned to run properly (indexes in place, using bind variables..) then a badly written query will run BAD In APEX and also in Toad & Sql Developer

     

     

    Thank you,

     

    Tony Miller

    LuvMuffin Software

    Blog: http://glassfish1.revion.com/apex/f?p=BLOG:HOME:0:::::

  • 7. Re: IR Report found 1 million record with blob files performance is too slow!
    fac586 Guru
    Currently Being Moderated

    Ramani_vadakadu wrote:

     

    please find my G.drive file which is we are using the apex IR report page heading(6 columns with blob).

    how?it could be clear for you!

     

    https://drive.google.com/file/d/0B_qZ9OVtwF4iMmF5a08tM0toTFU/edit?usp=sharing.

    No, still completely opaque. Can't access that from work. Please illustrate the problem using apex.oracle.com.

  • 8. Re: IR Report found 1 million record with blob files performance is too slow!
    Ramani_vadakadu Journeyer
    Currently Being Moderated

    hi,

    i have two region one is global search region with pagination X-Y ONLY its working fine! becoz i disable the Rows Per Page Selector!

     

    another one region as Selected Search region by Section/department LOV .when LOV selected to submit the value for IR report.it will showing the report but taking toomuch time.!!!

    What i need?

    when i selected LOV while any status bar or progressing status can showing to end user?

    "your selected documents are being loading"  like this massage!

     

    my IR report table have the blob file.IR table having section name so i created section name LOV to reflect the IR report.

    please find the G.drive shared file : https://drive.google.com/file/d/0B_qZ9OVtwF4iTjdHVW5zN1Z5akU/edit?usp=sharing                       

    my table structure  as...

     

    CREATE TABLE NEW_MASTER       (

      NWM_DOC_ID                                NUMBER(22,4)    NOT NULL,

      NWM_DOC_FILE_BINARY           BLOB,

      NWM_DOC_MIME_TYPE             VARCHAR2(250 CHAR),

      NWM_DOC_IMAGE_UPDATED  DATE,

      NWM_DOC_FILE_NAME             VARCHAR2(50 CHAR),

      NWM_DOC_CHAR_SET              VARCHAR2(128 CHAR),

      REF_NUMBER_INDEX                VARCHAR2(256 CHAR),

      DATE_INDEX                                DATE,

      SUBJECT_INDEX                       VARCHAR2(150 CHAR),

      NWM_DOC_CRT_BY                VARCHAR2(30 CHAR),

    --Section ID as section name from other table

      NWM_ACCES_SECTION          NUMBER

                                                                );

     

    Thanx,

    Ram

  • 9. Re: IR Report found 1 million record with blob files performance is too slow!
    Ramani_vadakadu Journeyer
    Currently Being Moderated

    plz any update!


    The result from the blob file occupied space!

    select round(sum(dbms_lob.getlength(NWM_DOC_FILE_BINARY))/1024/1024/1024,3) from NEW_MASTER     

    Result

    392.748 GB

  • 10. Re: IR Report found 1 million record with blob files performance is too slow!
    fac586 Guru
    Currently Being Moderated

    Ramani_vadakadu wrote:

     

    plz any update!

    Who or what is "plz"? No user called "plz" is involved in the thread. How can they provide an update?

    The result from the blob file occupied space!

    select round(sum(dbms_lob.getlength(NWM_DOC_FILE_BINARY))/1024/1024/1024,3) from NEW_MASTER     

    Result

    392.748 GB

    This thread is now a week old. Unless you stop posting microscopic pieces of irrelevant, random information and provide the information requested—in a location where we can access it—the thread will rapidly become another week older without any resolution. If you really want help, provide the information that will enable us to provide it.

     

    On the APEX side: reproduce the application in a workspace on apex.oracle.com, with the required tables, indexes etc and some sample data (it won't be possible to create 1 million rows there, but enough to get it to run). Also post debug traces from your system showing the execution times and performance bottlenecks you are experiencing there.

     

    On the database side: post the information described here.

  • 11. Re: IR Report found 1 million record with blob files performance is too slow!
    Ramani_vadakadu Journeyer
    Currently Being Moderated

    my workspace : ram_r&d

    user name      : aramani

    password       : apex

    application number: http://apex.oracle.com/pls/otn/f?p=32172:3:112433733575097::NO:::

     

    we need searching these index values from 1 million rows.

     

    Doc. NO

    Doc. Date

    Subject

    please excuse me,i could not upload all application functionally in apex.oracle.com due to the security policy.

  • 12. Re: IR Report found 1 million record with blob files performance is too slow!
    fac586 Guru
    Currently Being Moderated

    It's impossible to make definitive recommendations on performance improvement based on the limited information provided (in particular the absence of APEX debug traces and SQL execution plans), and lacking knowledge of the application  requirements and access to real data.

     

    As noted above, this is mainly a matter of data model and application design rather than a problem with APEX.

     

    Based on what has been made available on apex.oracle.com, taking action on the following points may improve performance.

     

    • I have concerns about the data model. The multiple DMS_TOPMGT_MASTER.NWM_DOC_LVL_0x_COD_NUM columns are indications of incomplete normalization, and the use of the DMS_TOPMGT_DETAILS table hints at an EAV model. Look at normalizing the model so that the WM_DOC_LVL_0x_COD_NUM relationship data can be retrieved using a single join rather than multiple scalar subqueries. Store 1:1 document attributes as column values in DMS_TOPMGT_MASTER rather than rows in DMS_TOPMGT_DETAILS.
    • There are no statistics on any of the application tables. Make sure statistics are gathered and kept up to date to enable the optimizer to determine correct execution plans.
    • There are no indexes on any of the FK columns or search columns. Create indexes on FK columns to improve join performance, and on searched columns to improve search performance.
    • More than 50% of the columns in the report query are hidden and not apparently used anywhere in the report. Why is this? A number of these columns are retrieved using scalar subqueries, which will adversely impact performance in a query processing 1 million+ rows. Remove any unnecessary columns from the report query.
    • A number of functions are applied to columns in the report query. These will incur processing time for the functions themselves and context switching overhead in the case of the non-kernel dbms_lob.get_length calls. Remove these function calls from the query and replace them with alternative processing that will not impact query performance, particularly the use of APEX column attributes that will only apply transformations to values that are actually displayed, rather than to all rows processed in the query.
      • Remove to_char calls from date columns and format them using date format masks in column attributes.
      • Remove decode/case switches. Replace this logic using Display as Text (based on LOV, escape special characters) display types based on appropriate LOVs.
      • Remove the dbms_lob.get_length calls. Instead add a file length column to the table, compute the file size when files are added/modified using your application or a trigger, and use this as the BLOB column in the query.
    • Searching using the Search Field text box in the APEX interactive report Search Bar generates query like:

     

    select
      ...
    from
      (select
        *
      from
        (...your report query...)
      ) r
      where ((instr(upper("NWM_DOC_REF_NO"), upper(:APXWS_SEARCH_STRING_1)) > 0
      or instr(upper("NWM_DOC_DESC"), upper(:APXWS_SEARCH_STRING_1)) > 0
      or instr(upper("SECTION_NAME"), upper(:APXWS_SEARCH_STRING_1)) > 0
      or instr(upper("CODE_TYPE"), upper(:APXWS_SEARCH_STRING_1)) > 0
      or instr(upper("REF_NUMBER_INDEX"), upper(:APXWS_SEARCH_STRING_1)) > 0
      or instr(upper("DATE_INDEX"), upper(:APXWS_SEARCH_STRING_1)) > 0
      or instr(upper("SUBJECT_INDEX"), upper(:apxws_search_string_1)) > 0
      or instr(upper("NWM_DOC_SERIEL"), upper(:APXWS_SEARCH_STRING_1)) > 0
      or instr(upper("NWM_DOC_DESCRIPTION"), upper(:APXWS_SEARCH_STRING_1)) > 0
      or instr(upper("NWM_DOC_STATUS"), upper(:APXWS_SEARCH_STRING_1)) > 0
      or instr(upper("MIME_TYPE"), upper(:APXWS_SEARCH_STRING_1)) > 0
      or instr(upper("NWM_DOC_FILE_BINARY"), upper(:APXWS_SEARCH_STRING_1)) > 0 ))
      ) r
    where
      rownum <= to_number(:APXWS_MAX_ROW_CNT)

     

    This will clearly never make use of any available indexes on your table. If you only want users to be able to search using values from 3 columns then remove the Search Field from the Search Bar and only allow users to create explicit filters on those columns. It may then be possible for the optimizer to push the resulting simple predicates down into the inlined report query to make use of indexes on the searched column.

    • I have created a copy of your search page on page 33 of your app and created an After Regions page process that will create Debug entries containing the complete IR query and bind variables used so they can be extracted for easier performance analysis and tuning outside of APEX. You can copy this to your local app and modify the page and region ID parameters as required.
  • 13. Re: IR Report found 1 million record with blob files performance is too slow!
    Ramani_vadakadu Journeyer
    Currently Being Moderated

    Thanks fac,

    I will try those guidelines and update the performance asap.

    Thanx,

    Ram

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points