Jump to content

Query takes a long time


ober

Recommended Posts

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.
Link to comment
Share on other sites

[!--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



Link to comment
Share on other sites

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).
Link to comment
Share on other sites

[!--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)?
Link to comment
Share on other sites

[!--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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.