System Admin and Configuration

Get Involved. Join the Conversation.

Topic

    Sristy Arya
    ROQL Error : Poor performing query - too many rows examinedAnswered
    Topic posted August 29, 2019 by Sristy AryaBronze Trophy: 5,000+ Points 
    57 Views, 10 Comments
    Title:
    ROQL Error : Poor performing query - too many rows examined
    Summary:
    ROQL Error : Poor performing query - too many rows examined
    Content:

    Hi All,

     

    I am executing an ROQL for find incidents having specific Product id.

    Query : 

    Select Incident.ID from Incident where Incident.Queue IN (49,50,51,52,53,189) and Incident.CustomFields.c.product_id=" . $product_id

    Where the Product ids I have are 2.

    After running I am encountering an error : Poor performing query - too many rows examined

    Can anyone help in this issue.

     

    Thanks 

    Sristy 

    Version:
    19B

    Best Comment

    Gursimran Singh Saini

    FIT Ops? :P

    Go with a report, it will work just fine. Make sure its on Operational Database.

    Comment

     

    • Sristy Arya

      Hi,

       

      I don't want to use Analytics here , need to do with ROQL only as its fast. If someone can help me in solving the ROQL Issue.

       

      Thanks

      Sristy

    • Suresh Thirukoti

       Sristy - did u tried using prefix in ROQL like "USE Reporting; <roql query" ..not sure on syntax....its there in PHP/SOAP documentation....

      ~Suresh

    • Gursimran Singh Saini

      FIT Ops? :P

      Go with a report, it will work just fine. Make sure its on Operational Database.

      • Sristy Arya
        Hi GS Hope you doing Good.
        • Sristy Arya
          We recently upgraded to 19B and on Portal there is incident creation code which has First runs an ROQL to check if the incident already exists for the product .During debugging I found that the issue is with the query. Thanks
      • Sristy Arya

        Hi GS,

        The same query is working fine on Production & Incident got created & its same is not working on Upgrade. The only solution is to use analytics in place of ROQL ? 

        Thanks

        Sristy

    • Allan Schrum

      Analytics and ROQL have the same issues regarding examining too many rows. Both have the same limits, so switching will not fix this issue.

      The problem is that the number of rows being examined by that query is too many (more than 2M rows). You will need to add more WHERE qualifiers that will limit the number of rows examined to something more reasonable. An alternative (as mentioned) is to use the reporting database (USE REPORT; SELECT ...) as that allows up to 5M rows to be examined in the query.

      Perhaps the incident's status could be part of the query to limit the number of rows? CreatedTime?

      • Sristy Arya
        Hi Allan, But ROQL processing is fast as compared to analytics.Why can't we do with ROQL ? Thanks Sristy
        • Allan Schrum

          You can use ROQL, but like Analytics there are constraints in the system to prevent bad queries. ROQL and Analytics are very similar in function but are implemented completely differently using different object models (Connect Common Object Model vs. raw schema). Both generate SQL to query the database, both have row-limits, both have MAX JOIN SIZE limits, but each provides their results in different formats. Analytics provides more functionality when used by the various UIs (and that is the area it was designed for, so having more capabilities there makes sense). Analytics, when invoked by the public API is at a slightly lower level and does not provide all the bells and whistles that the UI Analytics provides, but the data are generally available. The infrastructure is different so the weight of each API is different. ROQL is fast (thanks!) but perhaps not as full featured as Analytics. Depends on your needs.

          All that being said, use ROQL to do what you need (I do so all the time) but remember to operate within the constraints of the system which is the topic of the OP: how to get around the "Too Many Rows Examined" error. Without knowing exactly what is being done, I can only make generic suggestions which usually sparks an idea for a better query. I'm hoping we hear back from you that it now works with better filters, or that you can describe what you are doing that we on the Forums can make better suggestions. My suggestion on checking Incident status was that perhaps only those that are open are of interest. Under the covers that means a different index is used which might filter the number of rows down to something usable for your query.

    • Suresh Thirukoti

      Thanks Allan for clarifying on the point that ROQL and Reports are having same MAX JOIN SIZE setting......yes...All this while we are under impression that Reports and ROQL are quite distinct ....You rock wink

      ~Suresh