budimir Posted November 13, 2013 Share Posted November 13, 2013 I need help with performance improvement of this script. Script is working fine, but it's quite slow. It loads a lot of data, around 200.000. I am using paging to speed it up, but it's still slow. Can you give me any guide lines how to speed it up or to optimize? //Početak paginga if (isset($_GET['pageno'])) { $pageno = $_GET['pageno']; } else { $pageno = 1; } // if $upit11 = mysql_query("SELECT id FROM kalkulacija_stavke WHERE id_kalkulacija = '$id_kalkulacije' AND kataloski_broj NOT LIKE '1%'") or die (mysql_error()); $brojcanik = mysql_num_rows($upit11); $rows_per_page = 100; $lastpage = ceil($brojcanik/$rows_per_page); $pageno = (int)$pageno; if ($pageno > $lastpage) { $pageno = $lastpage; } // if if ($pageno < 1) { $pageno = 1; } // if $limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page; mysql_query( "SET NAMES utf8", $veza ); mysql_query( "SET CHARACTER SET utf8", $veza ); if($_SESSION["checked"] = "checked"){ $upit = "SELECT kalkulacija_stavke.*, kalkulacija_zamjene_staro.ne_koristi_se FROM kalkulacija_stavke LEFT JOIN kalkulacija_zamjene_staro ON kalkulacija_stavke.kataloski_broj = kalkulacija_zamjene_staro.kataloski_broj_stari WHERE id_kalkulacija = '$id_kalkulacije' AND ne_koristi_se = '0' AND kataloski_broj NOT LIKE '1%' ORDER BY kataloski_broj ASC $limit"; } else { $upit = "SELECT * FROM kalkulacija_stavke WHERE id_kalkulacija = '$id_kalkulacije' AND kataloski_broj NOT LIKE '1%' ORDER BY kataloski_broj ASC $limit"; } $rezultat = mysql_query($upit,$veza) or die (mysql_error()); while($row = mysql_fetch_array($rezultat)){ $broj = $row["id"]; $id_kalk = $row["id_kalkulacija"]; $id_cjen = $row["id_cjenika"]; $vrijeme = $row["vrijeme"]; $kataloski_broj = trim($row["kataloski_broj"]); $kategorija_artikla = $row["kategorija_artikla"]; $grupa_proizvoda = $row["grupa_proizvoda"]; $podgrupa_proizvoda = $row["podgrupa_proizvoda"]; $cijena_eurska = number_format(round(($row["cijena_EUR"]),2),2,",","."); $cijena_KN = number_format(round(($row["cijena_KN"]),2),2,",","."); $carina = number_format(round(($row["carina"]),2),2,",","."); $spediter = number_format(round(($row["spediter"]),2),2,",","."); $banka = number_format(round(($row["banka"]),2),2,",","."); $transport = number_format(round(($row["transport"]),2),2,",","."); $nabavna_cijena = number_format(round(($row["nabavna_cijena"]),2),2,",","."); $drezga_marza_po_grupi = number_format(round(($row["drezga_marza_po_grupi"]),2),2,",","."); $drezga_zarada = number_format(round(($row["drezga_zarada"]),2),2,",","."); $neto_VPC = number_format(round(($row["neto_VPC"]),2),2,",","."); $neto_MPC = number_format(round(($row["neto_MPC"]),2),2,",","."); $trosak_firme = number_format(round(($row["trosak_firme"]),2),2,",","."); $trosak_firme_p = number_format(round(($row["trosak_firme_p"]),2),2,",","."); $diler_marza_po_grupi = number_format(round(($row["diler_marza_po_grupi"]),2),2,",","."); $preporucena_VPC = number_format(round(($row["preporucena_VPC"]),2),2,",","."); $preporucena_MPC = number_format(round(($row["preporucena_MPC"]),2),2,",","."); $zarada_diler_kn = number_format(round(($row["zarada_diler_kn"]),2),2,",","."); $zarada_diler_p = number_format(round(($row["zarada_diler_post"]),2),2,",","."); $zarada_za_nas_kn = number_format(round(($row["zarada_za_nas_kn"]),2),2,",","."); $zarada_za_nas_p = number_format(round(($row["zarada_za_nas_post"]),2),2,",","."); $brutto_zarada_za_nas_kn = number_format(round(($row["brutto_zarada_za_nas_kn"]),2),2,",","."); $brutto_zarada_za_nas_p = number_format(round(($row["brutto_zarada_za_nas_post"]),2),2,",","."); $datum1 = date("d.m.Y H:i:s",strtotime($vrijeme)); //Dohvačanje starih i zamjenjenih brojeva $upit23 = "SELECT ne_koristi_se, kataloski_broj_novi FROM kalkulacija_zamjene_staro WHERE kataloski_broj_stari = '$kataloski_broj'"; $query23 = mysql_query($upit23) or die (mysql_error()); $row = mysql_fetch_array($query23); $staro = $row["ne_koristi_se"]; $zamjena_novo = $row["kataloski_broj_novi"]; echo ' <tr> <td width="65"> '; if (!empty($zamjena_novo)){ echo '<img src="images/zamjena.png" border="0" title="Broj je zamijenjen sa '.$zamjena_novo.'">'; } if (!empty($staro) AND $staro == 1){ echo ' <img src="images/staro.png" border="0" title="Broj se ne koristi!">'; } //Dohvačanje naziva artikla iz NAV-a $upit233 = "SELECT naziv_artikla FROM kalkulacija_import_kategorija WHERE kat_br = '$kataloski_broj'"; $query233 = mysql_query($upit233) or die (mysql_error()); $row = mysql_fetch_array($query233); $naziv_artikla = $row["naziv_artikla"]; if (empty($naziv_artikla)) { $upit234 = "SELECT naziv FROM kalkulacija_import_cjenik_stavke WHERE kataloski_broj = '$kataloski_broj'"; $query234 = mysql_query($upit234) or die (mysql_error()); $row44 = mysql_fetch_array($query234); $naziv_artikla = $row44["naziv"]; } //Zamjena hrvatskih znakova $some_special_chars = array("æ", "è", "í", "ó", "ú", "Á", "É", "Í", "Ó", "Ú", "ñ", "Ñ"); $replacement_chars = array("ć", "č", "i", "o", "u", "A", "Ć", "I", "O", "U", "n", "N"); $replaced_string = str_replace($some_special_chars, $replacement_chars, $naziv_artikla); echo' </td> <td width="120"><span title="VPC: '.$neto_VPC.' - PVPC: '.$preporucena_VPC.'">'.$kataloski_broj.'</span></td> <td width="200">'.$replaced_string.'</td> <td width="100"><div align="center">'.$kategorija_artikla.'</div></td> <td width="110"><div align="center">'.$grupa_proizvoda.'</div></td> <td width="140"><div align="center">'.$podgrupa_proizvoda.'</div></td> <td width="110"><div align="center">'.$cijena_eurska.'</div></td> <td width="90"><div align="center">'.$cijena_KN.'</div></td> <td width="80"><div align="center">'.$carina.'</div></td> <td width="80"><div align="center">'.$spediter.'</div></td> <td width="100"><div align="center">'.$banka.'</div></td> <td width="80"><div align="center">'.$transport.'</div></td> <td width="100"><div align="center">'.$nabavna_cijena.'</div></td> <td width="80"><div align="center">'.$drezga_marza_po_grupi.' %</div></td> <td width="100"><div align="center">'.$drezga_zarada.'</div></td> <td width="90"><div align="center"><strong>'.$neto_VPC.'</strong></div></td> <td width="90"><div align="center"><strong>'.$neto_MPC.'</strong></div></td> <td width="90"><div align="center">'.$diler_marza_po_grupi.' %</div></td> <td width="100"><div align="center">'.$zarada_diler_kn.'</div></td> <td width="110"><div align="center"><strong>'.$preporucena_VPC.'</strong></div></td> <td width="110"><div align="center"><strong>'.$preporucena_MPC.'</strong></div></td> <td width="90"><div align="center">'.$brutto_zarada_za_nas_kn.'</div></td> <td width="90"><div align="center">'.$brutto_zarada_za_nas_p.'</div></td> <td width="80"><div align="center">'.$trosak_firme_p.' %</div></td> <td width="80"><div align="center">'.$trosak_firme.'</div></td> <td width="100"><div align="center">'.$zarada_za_nas_kn.'</div></td> <td width="80"><div align="center">'.$zarada_za_nas_p.'</div></td> <td width="150"><div align="center"> '; if ($status == 1) { echo '<a href="povjest_redak_kalkulacije.php?id='.$broj.'&id_kalkulacije='.$id_kalkulacije.'&kataloski_broj='.$kataloski_broj.'&id_cjenika='.$id_cjen.'"><img src="images/povjest.png" border="0" width="20" height="20" alt="Povijest" title="Pogledaj povjest artikla"></a> <a href="usporedba_redak_kalkulacije.php?id='.$broj.'&id_kalkulacije='.$id_kalkulacije.'&kataloski_broj='.$kataloski_broj.'&id_cjenika='.$id_cjen.'"><img src="images/history1.png" border="0" alt="Usporedba" title="Usporedba retka sa prošlom godinom" width="25" heigth="25"></a> <a href="calculator.php?id='.$broj.'&id_kalkulacije='.$id_kalkulacije.'" onclick="basicPopup(this.href);return false"><img src="images/calculator_n.png" border="0" title="Kalkulator zarade za dilera"></a></div></td>'; } else { echo'<a href="izmjeni_redak_kalkulacije.php?id='.$broj.'&id_kalkulacije='.$id_kalkulacije.'&id_cjenika='.$id_cjen.'"><img src="images/izmjeni.png" border="0" alt="Izmjeni" title="Izmjeni redak kalkulacije"></a> <a href="obrisi_redak_kalkulacije.php?id='.$broj.'&id_kalkulacije='.$id_kalkulacije.'&id_cjenika='.$id_cjen.'" onclick="provjera(this.href); return false;"><img src="../brisanje.png" border="0" alt="Obrisi" title="Obriši redak kalkulacije"></a> <a href="povjest_redak_kalkulacije.php?id='.$broj.'&id_kalkulacije='.$id_kalkulacije.'&kataloski_broj='.$kataloski_broj.'&id_cjenika='.$id_cjen.'"><img src="images/povjest.png" border="0" width="20" height="20" alt="Povijest" title="Pogledaj povjest artikla"></a> <a href="usporedba_redak_kalkulacije.php?id='.$broj.'&id_kalkulacije='.$id_kalkulacije.'&kataloski_broj='.$kataloski_broj.'&id_cjenika='.$id_cjen.'"><img src="images/history1.png" border="0" alt="Usporedba" title="Usporedba retka sa prošlom godinom" width="25" heigth="25"></a> <a href="calculator.php?id='.$broj.'&id_kalkulacije='.$id_kalkulacije.'" onclick="basicPopup(this.href);return false"><img src="images/calculator_n.png" border="0" title="Kalkulator zarade za dilera"></a></div></td> </tr> '; } } echo ' </table> <p align="center"> </p> <p align="center"> '; if ($pageno == 1) { echo " <font color='#990000'>Početak</font> || Natrag "; } else { echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1&id=$id_kalkulacije'>Prva</a> | "; $prevpage = $pageno-1; echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage&id=$id_kalkulacije'>Natrag</a> "; } // if echo " ( <font color='grey'>Stranica - <b>$pageno</b> od <b>$lastpage</b></font> ) "; if ($pageno == $lastpage) { echo " Naprijed || <font color='#990000'>Kraj</font> "; } else { $nextpage = $pageno+1; echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage&id=$id_kalkulacije'>Naprijed</a> | "; echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage&id=$id_kalkulacije'>Posljednja</a> "; } // if echo " <br />Rezultata: ".$brojcanik." </p>"; Quote Link to comment Share on other sites More sharing options...
dalecosp Posted November 13, 2013 Share Posted November 13, 2013 (edited) Start here: $upit11 = mysql_query("SELECT id FROM kalkulacija_stavke WHERE id_kalkulacija = '$id_kalkulacije' AND kataloski_broj NOT LIKE '1%'") or die (mysql_error()); //select every ID ... $brojcanik = mysql_num_rows($upit11); // but only use this query for a count? //This would be preferable, I think: $upit11 = mysql_query("SELECT count(id) FROM kalkulacija_stavke WHERE id_kalkulacija = '$id_kalkulacije' AND kataloski_broj NOT LIKE '1%'") or die (mysql_error()); //select every ID ... $brojcanik = mysql_result($upit11,0); Edited November 13, 2013 by dalecosp Quote Link to comment Share on other sites More sharing options...
dalecosp Posted November 13, 2013 Share Posted November 13, 2013 Incidentally, mysql_* is deprecated; you might get a little better performance from mysqli_* or PDO, but I'm not sure about that.... Quote Link to comment Share on other sites More sharing options...
Psycho Posted November 13, 2013 Share Posted November 13, 2013 dalecosp is correct on the first point. Your first query is selecting ALL records in order to get a count. That means all the data for those records have to be queried and loaded into memory. A simple COUNT() query will give you the count without all that overhead. Also, the "NOT LIKE" part of the query could create some performance issues as well. It's a pretty simple comparison, but if you still have issues after making the above change you could try using a SUBSTR() comparison AND SUBSTRING(kataloski_broj, 1) <> '1'" Second, you are running other queries in the loop that output the query results to display. Never run queries in loops! You should create ONE query that returns all the data you need. Looking at the main query, you have an if/else that either includes the 'kalkulacija_zamjene_staro' table or not just to check if a particular value is 0 or not. But later you do another query on that table anyway. So, the main query should include that table regardless and the only change should be the check for a 0 in that particular field. In fact, the logic to determine total records doesn't take this into account and would get incorrect results. Not to mention the comparison is an assignment (i.e. uses a single equal sign instead of two) so it would ALWAYS run the first query. Also, this won't improve performance, but will make your app easier to create and maintain. You have a lot of lines where you are formatting a number in a specific way $zarada_diler_p = number_format(round(($row["zarada_diler_post"]),2),2,",","."); Instead of writing the same complicated code on every line, create a function to format the values. That way you are ensured to get consistent results. Otherwise, a simple typo on one line could cause problems that are hard to debug and fix function myNumberFormat($number) { $returnValue = number_format(round(($number),2),2,",","."); return $returnValue; } //Then call it in your code $neto_VPC = myNumberFormat($row["neto_VPC"]); $neto_MPC = myNumberFormat($row["neto_MPC"]); $trosak_firme = myNumberFormat($row["trosak_firme"]); // Etc. . . Think about how much easier it would be to change the format if needed. So, after looking at your code, I think you need three things. 1) A condition to determine if you want to exclude those records where 'ne_koristi_se' = 0 2) A query to determine the total count of records that takes into account item #1 above 3) A single query to get ALL the data for the selected page. I think the code below is correct based upon looking through what you have. But, the logic to output the results from the single query will need to be modified. It contains three sections of code and I left some things out in between (such as calculating the LIMIT). //Determine if the results should exclude 'ne_koristi_se' = 0 records $checked = ($_SESSION["checked"] = "checked") "AND kzs.ne_koristi_se = '0'" : ''; //Create and run query to detemine total number of records $query = "SELECY COUNT(*) FROM kalkulacija_stavke AS ks LEFT JOIN kalkulacija_zamjene_staro AS kzs ON ks.kataloski_broj = kzs.kataloski_broj_stari WHERE ks.id_kalkulacija = '$id_kalkulacije' AND ks.kataloski_broj NOT LIKE '1%' $checked"; $result = mysql_query($query) or die(mysql_error()); $brojcanik = mysql_result($result, 0); //Create and run ONE query to get all data for the page $upit = "SELECT ks.*, kzs.ne_koristi_se, kzs.kataloski_broj_novi, krk.naziv_artikla FROM kalkulacija_stavke AS ks LEFT JOIN kalkulacija_zamjene_staro AS kzs ON ks.kataloski_broj = kzs.kataloski_broj_stari LEFT JOIN kalkulacija_import_kategorija AS kik ON kik.kat_br = kzs.kataloski_broj_stari WHERE ks.id_kalkulacija = '$id_kalkulacije' AND ks.kataloski_broj NOT LIKE '1%' $checked ORDER BY kataloski_broj ASC $limit"; $result = mysql_query($query) or die(mysql_error()); Quote Link to comment Share on other sites More sharing options...
budimir Posted November 15, 2013 Author Share Posted November 15, 2013 Psycho, Thank you very much for you're effort. You did an excellent explanation to me. I like the idea with the function! Much faster and easier. I will try query and see how it goes. When I rework my code I will post it back here. If you will be interested to give me any further advices what to improve. I'm still lerning, so everything is valuable to me. My next step will be to switch to mysqli... But application is to big to completly rewrite it, so I will do just some parts. Any speed improvement if I switch to mysqli? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.