Post reply

Poor Performance : Select count(*) takes 30 Mins ? Why ?

  • December 4, 2008 at 6:13 pm

    #198446

    Our ERP System (JDE) uses Oracle 8i as our database .

    We found that the performance of query table is getting poor .

    Select count(*) takes 30 Mins (select count(*) from hqprddta.F0911) to get the output !

    How can I solve this ?

    Thanks a lot !

    Geoffrey

  • December 4, 2008 at 6:45 pm

    #908034

    No chance of using indexes... it's gonna return the whole table. How many rows are in the table? Also, are you sure it's a table and not a view?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 4, 2008 at 7:00 pm

    #908035

    The Record Count is 9777594 !

    It's table !

  • December 4, 2008 at 7:15 pm

    #908038

    Sorry... I missed the fact that you were doing a count on the table... not returning rows... Got a PK or any decent indexes on the table?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 4, 2008 at 7:19 pm

    #908039

    Nope, just blame it on Oracle.

  • December 4, 2008 at 7:40 pm

    #908043

    The table has PK and 13 indexes !

  • December 5, 2008 at 4:47 am

    #908155

    Please do this, get and post execution plan for both "select count(*)..." and "select count(your-pk-column)..."

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy