2 Replies - 424 Views - Last Post: 13 September 2017 - 09:59 AM Rate Topic: -----

#1 nugowey  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 11-September 17

PHP page take too long to load

Posted 11 September 2017 - 10:21 AM

Hei, I'm new in PHP and sometimes I get timeout error when I run the file.
Here's my file.php:
//RSI-Start
$sqla = $pdo->prepare("SELECT * FROM csv_data ORDER BY Date");
$sqla->execute();

$conn3 = new PDO('mysql:host='.$host.';dbname='.$database, $username, $password);
$conn3->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql4 = $conn3->prepare("REPLACE INTO rsi_data VALUES (:tanggal,:change,:gain,:loss,:avgGain,:avgLoss,:rs,:rsi)");

$data = $sqla->fetch();
$close = $data['Close'];
$avg_gain = '';
$avg_loss = '';
$rs = '';
$rsi = '';

for ($i=0; $i < 13; $i++) {
  $data = $sqla->fetch();
  $oldclose = $close;
  $close = $data['Close'];
  $change = $close - $oldclose;
  if ($change >= 0) {
    $gain = $change;
  } else {
    $gain = 0;
  }
  if ($change < 0) {
    $loss = abs($change)  ;
  } else {
    $loss = 0;
  }
    $tanggal = $data['Date'];

    $sql4->bindParam(':tanggal', $tanggal);
    $sql4->bindParam(':change', $change);
    $sql4->bindParam(':gain', $gain);
    $sql4->bindParam(':loss', $loss);
    $sql4->bindParam(':avgGain', $avg_gain);
    $sql4->bindParam(':avgLoss', $avg_loss);
    $sql4->bindParam(':rs', $rs);
    $sql4->bindParam(':rsi', $rsi);
    $sql4->execute();
}
$conn4 = new PDO('mysql:host='.$host.';dbname='.$database, $username, $password);
$conn4->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql5 = $conn4->prepare("SELECT * FROM rsi_data ORDER BY Date");
$sql5->execute();

for ($i=0; $i <13 ; $i++) {
  $data2 = $sql5->fetch();
  $gain=$data2['Gain'];
  $loss=$data2['Loss'];
  $gain2[]=$gain;
  $loss2[]=$loss;
}
$data = $sqla->fetch();
$tanggal = $data['Date'];
$close = $data['Close'];
$change = $close - $oldclose;
if ($change >= 0) {
  $gain = $change;
} else {
  $gain = 0;
}
if ($change < 0) {
  $loss = abs($change)  ;
} else {
  $loss = 0;
}
$avg_gain = array_sum($gain2)/14;
$avg_loss = array_sum($loss2)/14;
$rs = $avg_gain/$avg_loss;
if($avg_loss == 0){
  $rsi = 100;
} else {
  //calc and normalize
  $rsi = 100 - (100 / ( 1 + $rs));
}
$sql4->bindParam(':tanggal', $tanggal);
$sql4->bindParam(':change', $change);
$sql4->bindParam(':gain', $gain);
$sql4->bindParam(':loss', $loss);
$sql4->bindParam(':avgGain', $avg_gain);
$sql4->bindParam(':avgLoss', $avg_loss);
$sql4->bindParam(':rs', $rs);
$sql4->bindParam(':rsi', $rsi);
$sql4->execute();

$alpha = 1/14;

$data2 = $sql5->fetch();
foreach ($sqla as $data) {
  $data2 = $sql5->fetch();
  $oldclose = $close;
  $close = $data['Close'];
  $change = $close - $oldclose;
  if ($change >= 0) {
    $gain = $change;
  } else {
    $gain = 0;
  }
  if ($change < 0) {
    $loss = abs($change)  ;
  } else {
    $loss = 0;
  }
    $tanggal = $data['Date'];
    $avg_gain= $alpha*$gain+(1-$alpha)*$avg_gain;
    $avg_loss= $alpha*$loss+(1-$alpha)*$avg_loss;
    $rs = $avg_gain/$avg_loss;
    if($avg_loss == 0){
      $rsi = 100;
    } else {
      //calc and normalize
      $rsi = 100 - (100 / ( 1 + $rs));
    }
    $sql4->bindParam(':tanggal', $tanggal);
    $sql4->bindParam(':change', $change);
    $sql4->bindParam(':gain', $gain);
    $sql4->bindParam(':loss', $loss);
    $sql4->bindParam(':avgGain', $avg_gain);
    $sql4->bindParam(':avgLoss', $avg_loss);
    $sql4->bindParam(':rs', $rs);
    $sql4->bindParam(':rsi', $rsi);
    $sql4->execute();
}
//RSI-End

my conection.php:
<?php
$host = 'localhost'; 
$username = 'root'; 
$password = ''; 
$database = 'stock'; 
$pdo = new PDO('mysql:host='.$host.';dbname='.$database, $username, $password);
?>


I thought it because of my truncate query which ran before the file.php start, but it's not. It's because the PHP file.
I hope you could give me an explanation what I did wrong.

Is This A Good Question/Topic? 0
  • +

Replies To: PHP page take too long to load

#2 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4238
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: PHP page take too long to load

Posted 11 September 2017 - 12:59 PM

Best way to figure out performance issues is to profile a script using something like XDebug. It's well worth investing some time into, because it can be a very useful tool in many ways.

Just skimming that code, though, there are a couple of things that are worth pointing out:

  • PHP code in and of itself is rarely the reason for performance issues. They usually boil down to external calls (database queries, file operations, web services, etc...) So you should always look at those first.

  • You seem to be opening multiple DB connections. $conn3 and $conn4 are visible in that code, and I assume that you also have $conn1 and $conn2 somewhere? - You will rarely need more than one DB connection, and certainly not multiple connections to the same database. One connection can execute as many queries as are required.

  • Queries that start with SELECT * are always a red flag, especially when they are executed without a limit or where clause. Depending on the table size, it can definitely cause performance issues; you're fetching all the data from those tables into PHP. - If at all possible, you should be doing whatever filtering/processing you can on the database side, either in the query, or in extreme cases in a DB procedure/function.

Was This Post Helpful? 2
  • +
  • -

#3 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1829
  • View blog
  • Posts: 5,761
  • Joined: 15-January 14

Re: PHP page take too long to load

Posted 13 September 2017 - 09:59 AM

That code doesn't make much sense to me. Like this part:

$conn4 = new PDO('mysql:host='.$host.';dbname='.$database, $username, $password);
$conn4->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql5 = $conn4->prepare("SELECT * FROM rsi_data ORDER BY Date");
$sql5->execute();

for ($i=0; $i <13 ; $i++) {
  $data2 = $sql5->fetch();
  $gain=$data2['Gain'];
  $loss=$data2['Loss'];
  $gain2[]=$gain;
  $loss2[]=$loss;
}

You select every row from the table, then loop 13 times and fetch the next row each time. Are there only 13 rows? What does 13 mean? Apparently you're expecting more than 13, because down here you start fetching more rows:

$data2 = $sql5->fetch();
foreach ($sqla as $data) {
  $data2 = $sql5->fetch();

That first row you just throw away, you don't do anything with it (what's wrong with row 14, why throw it away?), then start looping and fetch more rows. It just doesn't make a lot of sense what you're trying to do, and it doesn't help by calling things "$sql4", "$data2", etc. Variable names are supposed to be meaningful, they are supposed to tell you something about the data in the variable.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1