[CDash] slow label query

[Wolfenbarger, Paul R]

I am hoping for someone with more sql understanding than I have to help with an issue. In api/v1/index.php at line 1004 there is a label query that is causing a bottleneck in my installation

                $label_query_base =
                    "SELECT b2t.status, b2t.newstatus
                    FROM build2test AS b2t
                    INNER JOIN label2test AS l2t ON
                    (l2t.testid=b2t.testid AND l2t.buildid=b2t.buildid)
                    WHERE b2t.buildid = '$buildid' AND
                    l2t.labelid IN $label_ids";
                $label_filter_query = $label_query_base . $limit_sql;
                $labels_result = pdo_query($label_filter_query);

This is returning two non-indexed fields from build2test and joining on indexed fields and since my build2test and label2test tables are each about 43 million records, this takes 20 to 300 seconds each. Multipled by at least 40 builds in the loop and you can see my issue.

As a c++ programmer my first instinct was to split the query and make all the operations work on indexes. I did some queries at the command line and got sub-second returns from each query and the php would now look something like

                $testids = pdo_query(
                  “select testid from label2test
                   where label2test.buildid=$buildid
                   and label2test.labelid in $label_ids") ;
                $test_ids = implode("','", $testids) ;
                $label_filter_query =
                  "select status, newstatus
                   from build2test where buildid = $buildid
                   and testid in $test_ids" ;
However, from my reading it seems that multiple queries of this sort are frowned on in the sql world.  Before I set up a test instance to check this out do any of you have any insight into how to re-structure the query to get the kind of performance I need?

Thanks!

----------------------------------------------------------------------------------------
"If you understand what you're doing, you're not learning anything."
  -- A. L.

Paul R. Wolfenbarger, P.E., M.S.
Computational Simulation Infrastructure
DevOps Product Owner
Sandia National Laboratories
P.O. Box 5800 MS 0845
Albuquerque, NM  87185-0845
505-844-5458 phone
prwolfe at sandia.gov<mailto:prwolfe at sandia.gov>
----------------------------------------------------------------------------------------


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://public.kitware.com/pipermail/cdash/attachments/20170223/1f77c971/attachment.htm>