Jump to content


Photo

Query takes a long time


  • Please log in to reply
7 replies to this topic

#1 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 31 May 2006 - 03:39 PM

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!?
<?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>";
        }
    }
    ?>

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

Info: PHP Manual


#2 reandeau

reandeau
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 31 May 2006 - 05:34 PM

[!--quoteo(post=378744:date=May 31 2006, 08:39 AM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ May 31 2006, 08:39 AM) View Post[/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!?
<?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>";
        }
    }
    ?>

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





#3 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 31 May 2006 - 05:52 PM

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).

Info: PHP Manual


#4 reandeau

reandeau
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 31 May 2006 - 06:20 PM

[!--quoteo(post=378785:date=May 31 2006, 10:52 AM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ May 31 2006, 10:52 AM) View Post[/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)?


#5 lead2gold

lead2gold
  • Members
  • PipPipPip
  • Advanced Member
  • 164 posts
  • LocationOttawa, On

Posted 31 May 2006 - 06:55 PM

[!--quoteo(post=378744:date=May 31 2006, 11:39 AM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ May 31 2006, 11:39 AM) View Post[/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!?
<?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";


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


#6 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 31 May 2006 - 07:55 PM

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.

Info: PHP Manual


#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 31 May 2006 - 08:10 PM

Sub-queries will always slow you down. Replace with a join whenever possible. In this case
$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";

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 01 June 2006 - 01:48 PM

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

Info: PHP Manual





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users