Query takes a long time

[ober]

I have the following select box that gets filled on a page load. Everything else on the page gets loaded almost instantly (I'm on a LAN), but when I get to this part of the page, it takes several very long seconds to fill the select box.

I use almost the same code in another setup, so I think the PHP is fine. I'm pretty much narrowing it down to the actual query, but I thought I'd post here and see if anyone sees something wrong with how I'm processing it.

And I know the query is pretty involved, so I'm wondering if I broke it up a bit whether it would be faster!?
[code]
<?php
    $query = "SELECT HEADER_ID, CELL_NUMBER, END_DATE, TEST_NAME FROM EngineTestHeader WHERE TEST_TYPE_CODE IN ($ttcode) AND HEADER_ID IN (SELECT DISTINCT HEADER_ID FROM TransientCycleData) ORDER BY END_DATE DESC";
    $result = $ehandle->update_query($query);
    if($result && mssql_num_rows($result) > 0)
    {      
        // dump everything to the select box  
        $i = 0;
        while($row = mssql_fetch_array($result))
        {
            extract($row);
            $cell = $CELL_NUMBER;
            if(strlen(trim($CELL_NUMBER)) == 1)
                $cell = "0" . $CELL_NUMBER;
            echo "<option value=\"$HEADER_ID\">Cell $cell&nbsp;-&nbsp;" . @date("M d Y h:i A", strtotime($END_DATE)) . "&nbsp;-&nbsp;$TEST_NAME</option>";
        }
    }
    ?>
[/code]

Oh, and the $ehandle->update_query() part is a custom class that I use. I know it's not that.

[reandeau]

[!--quoteo(post=378744:date=May 31 2006, 08:39 AM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ May 31 2006, 08:39 AM) [snapback]378744[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I have the following select box that gets filled on a page load. Everything else on the page gets loaded almost instantly (I'm on a LAN), but when I get to this part of the page, it takes several very long seconds to fill the select box.

I use almost the same code in another setup, so I think the PHP is fine. I'm pretty much narrowing it down to the actual query, but I thought I'd post here and see if anyone sees something wrong with how I'm processing it.

And I know the query is pretty involved, so I'm wondering if I broke it up a bit whether it would be faster!?
[code]
<?php
    $query = "SELECT HEADER_ID, CELL_NUMBER, END_DATE, TEST_NAME FROM EngineTestHeader WHERE TEST_TYPE_CODE IN ($ttcode) AND HEADER_ID IN (SELECT DISTINCT HEADER_ID FROM TransientCycleData) ORDER BY END_DATE DESC";
    $result = $ehandle->update_query($query);
    if($result && mssql_num_rows($result) > 0)
    {      
        // dump everything to the select box  
        $i = 0;
        while($row = mssql_fetch_array($result))
        {
            extract($row);
            $cell = $CELL_NUMBER;
            if(strlen(trim($CELL_NUMBER)) == 1)
                $cell = "0" . $CELL_NUMBER;
            echo "<option value=\"$HEADER_ID\">Cell $cell&nbsp;-&nbsp;" . @date("M d Y h:i A", strtotime($END_DATE)) . "&nbsp;-&nbsp;$TEST_NAME</option>";
        }
    }
    ?>
[/code]

Oh, and the $ehandle->update_query() part is a custom class that I use. I know it's not that.
[/quote]


Its hard to know what would be slowing up your query without knowing how big your data set is and what you have indexed. You may want to try indexing some of the variables that you are trying to reference in the above query if they aren't already like HEADER_ID.

What's in your $ttcode variable? Try doing a LEFT JOIN with TransientCycleData. Something like SELECT HEADER_ID, CELL_NUMBER, END_DATE, TEST_NAME FROM EngineTestHeader LEFT JOIN TransientCycleData ON (EngineTestHeader.HEADER_ID = TransientCycleData.HEADER_ID)



Jon Tjemsland

[ober]

Jon,

Thanks for the reply. Currently there are close to 700,000 records in the table and it grows at 12000 records per test. I'm indexing an auto_increment field called "ID". Each set of 12000 (approximately) records has an ID called HEADER_ID tied to it. I think that's where the main time consumption is happening... doing the distinct on all of those.

and $ttcode is "130,210" in this case. It's always going to be a very small comma seperated list of numbers (currently a max of 3).

[reandeau]

[!--quoteo(post=378785:date=May 31 2006, 10:52 AM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ May 31 2006, 10:52 AM) [snapback]378785[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Jon,

Thanks for the reply. Currently there are close to 700,000 records in the table and it grows at 12000 records per test. I'm indexing an auto_increment field called "ID". Each set of 12000 (approximately) records has an ID called HEADER_ID tied to it. I think that's where the main time consumption is happening... doing the distinct on all of those.

and $ttcode is "130,210" in this case. It's always going to be a very small comma seperated list of numbers (currently a max of 3).
[/quote]

Have you tried indexing the HEADER_ID field? Or could you create another table that would store the HEADER_IDs in it (pretty similar to just indexing)?

[lead2gold]

[!--quoteo(post=378744:date=May 31 2006, 11:39 AM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ May 31 2006, 11:39 AM) [snapback]378744[/snapback][/div][div class=\'quotemain\'][!--quotec--]
And I know the query is pretty involved, so I'm wondering if I broke it up a bit whether it would be faster!?
[code]
<?php
    $query = "SELECT HEADER_ID, CELL_NUMBER, END_DATE, TEST_NAME FROM EngineTestHeader WHERE TEST_TYPE_CODE IN ($ttcode) AND HEADER_ID IN (SELECT DISTINCT HEADER_ID FROM TransientCycleData) ORDER BY END_DATE DESC";

[/code]

Oh, and the $ehandle->update_query() part is a custom class that I use. I know it's not that.
[/quote]

We had a similar problem at work the other day, and after troubleshooting the hell out of it (it came down to the "IN" clause). When we removed the IN and replaced it with a WHERE clause (then manipulated the string slightly for it to work). it COMPLETELY got faster.

I'm no pro when it comes to SQL, fenway may have another sollution for you but i would focus on the keyword "IN". Aparently that keyword causes every single iteration of the first select to do a sequential search over and over again for every item found. using the WHERE clause causes only one search and builds it's temporary table from that.

Chris

[ober]

I can get rid of the first IN, but not the second, which is where I assume most of the slow-down is.

And Jon, I'll try indexing the HEADER_ID field... but creating another table is a whole other headache in itself. But I'll think about it. Thanks.

[Barand]

Sub-queries will always slow you down. Replace with a join whenever possible. In this case
[code]
$query = "SELECT DISTINCT e.HEADER_ID, e.CELL_NUMBER, e.END_DATE, e.TEST_NAME
FROM EngineTestHeader e
INNER JOIN TransientCycleData t
ON e.HEADER_ID = t.HEADER_ID
WHERE e.TEST_TYPE_CODE IN ($ttcode)
ORDER BY END_DATE DESC";[/code]

[ober]

Thanks Barand. That did make a difference, but the biggest difference here was indexing the table. Thanks Jon.