Advertise here

  Talk Arcades > Arcades > Scripts & Software > TradeArcadePro

Welcome to Talk Arcades, the premier forum for arcade webmasters.

You are currently viewing our boards as a guest. By joining our community you will be able to make posts, communicate privately with other arcade webmasters and talk advantage of the GameFeed™ Distribution Network for 1-click installation of games into your arcade.

Registration is easy, so please join us today!

Reply
 
LinkBack Thread Tools Search this Thread
 
Old 06-15-2011, 11:22 AM   #1 (permalink)
lfhost
Senior Member
 
Join Date: Aug 2008
Posts: 270
lfhost is on a distinguished roadlfhost is on a distinguished road


Default Part 2 Trade Arcade Pro Speed Up/Reduce 80-90% Query Overhead On Average

As a followup to my first thread of improving listGames() function. Here is an improvement to

topPlayed.html, newest.html and categories. This is a little more involved than the other thread, but is worth it in the end.

First I will show the stat tests of queries, memory and script execution

Default = Vanilla install of Tap, Updated = new queries used.

topPlayed.html

Default

PHP Code:
Initial711168bytes | Final: 898152bytes Peak1068320bytes 22 queries used
Script Execution Time
0.00982403755188 
Updated

PHP Code:
Initial711416bytes | Final: 898368bytes Peak1068568bytes 6 queries used
Script Execution Time
0.00626993179321 


newest.html

Default

PHP Code:
Initial711192bytes | Final: 898344bytes Peak1068408bytes 22 queries used
Script Execution Time
0.00947499275208 
Updated

PHP Code:
Initial711016bytes | Final: 898192bytes Peak1068200bytes 6 queries used
Script Execution Time
0.00607585906982 


Categories

Default

PHP Code:
Initial713160bytes | Final: 901464bytes Peak1070328bytes 23 queries used
Script Execution Time
0.00823092460632 
Updated

PHP Code:
Initial712776bytes | Final: 901104bytes Peak1069968bytes 8 queries used
Script Execution Time
0.00677704811096 
To do this, open includes/engine.php

find

PHP Code:
$sql="SELECT * FROM games WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal LIMIT $start,$limit"
replace with

PHP Code:
/**edited by lfhost**/
    //$sql="SELECT * FROM games WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal LIMIT $start,$limit";
    
$sql="SELECT games.*,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal LIMIT $start,$limit"
find

PHP Code:
$totalPages=ceil(mysql_num_rows(mysql_query("SELECT * FROM games WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal"))/$limit); 
replace with

PHP Code:
/**edited by lfhost**/
//$totalPages=ceil(mysql_num_rows(mysql_query("SELECT * FROM games WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal"))/$limit);
$totalPages=ceil(mysql_num_rows(mysql_query("SELECT games.*,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal"))/$limit); 
find

PHP Code:
$sql="SELECT * FROM games WHERE gameName LIKE '%$q%' OR  gameDesc LIKE '%$q%' OR gameTags LIKE '%$q%' AND gameStatus=1 ORDER BY gamePlaysTotal LIMIT $start,$limit"
replace with

PHP Code:
/**edited by lfhost**/
//$sql="SELECT * FROM games WHERE gameName LIKE '%$q%' OR  gameDesc LIKE '%$q%' OR gameTags LIKE '%$q%' AND gameStatus=1 ORDER BY gamePlaysTotal LIMIT $start,$limit";
$sql="SELECT games.*,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameName LIKE '%$q%' OR  gameDesc LIKE '%$q%' OR gameTags LIKE '%$q%' AND gameStatus=1 ORDER BY gamePlaysTotal LIMIT $start,$limit"
find

PHP Code:
$sql="SELECT * FROM games WHERE gameStatus=1 ORDER BY gamePlaysTotal DESC LIMIT $start,$limit"
replace with

PHP Code:
/**edited by lfhost**/
//$sql="SELECT * FROM games WHERE gameStatus=1 ORDER BY gamePlaysTotal DESC LIMIT $start,$limit";
$sql="SELECT games.*,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1 ORDER BY gamePlaysTotal DESC LIMIT $start,$limit"
find

PHP Code:
$sql="SELECT * FROM games WHERE gameStatus=1 ORDER BY gameAdded DESC LIMIT $start,$limit"
replace with

PHP Code:
/**edited by lfhost**/
//$sql="SELECT * FROM games WHERE gameStatus=1 ORDER BY gameAdded DESC LIMIT $start,$limit";
$sql="SELECT games.*,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1 ORDER BY gameAdded DESC LIMIT $start,$limit"
find

PHP Code:
$sqlTotal="SELECT * FROM games WHERE gameStatus=1"
replace with

PHP Code:
/**edited by lfhost**/
//$sqlTotal="SELECT * FROM games WHERE gameStatus=1";
$sqlTotal="SELECT games.*,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1"

open /templates/[Your Template]/browse.php
find

PHP Code:
$gameCatName=catId2Name($gameCat); 
replace with

PHP Code:
/**edited by lfhost**/
    //$gameCatName=catId2Name($gameCat);
    
$gameCatName=$row['catName']; 
Save and upload the new updated files to

/includes/engine.php
/templates/[Your Template]/browse.php
__________________
Thanks
Paul
I code for FOOOOOD
Your coding errors make me Orgasm LOL

Arcade Themes and Mods
Sniperz Login with Fb and submit your GAMES
ABC Arcade | Youlose | Wicked Games | GirlyCade | Gurl Arcade | Zombie Arcade
lfhost is offline  
Digg this Post!
Reply With Quote
 
Old 06-15-2011, 05:34 PM   #2 (permalink)
JanArve
phpAS Staff
Arcade Warrior
 
Join Date: Jun 2008
Posts: 798
JanArve is on a distinguished roadJanArve is on a distinguished roadJanArve is on a distinguished road

MarketScore (6)

Default

Good call, I wasn't aware that using an outer join in your query could do that much difference.. Learned something new today too!
__________________


-= Check it out: Php Arcade Script =-
-= Php Arcade Script Templates: Neon Blue | Neon Green | Neon Pink | Dark Charred | Colorful =-
JanArve is offline  
Digg this Post!
Reply With Quote
 
Old 06-15-2011, 07:09 PM   #3 (permalink)
lfhost
Senior Member
 
Join Date: Aug 2008
Posts: 270
lfhost is on a distinguished roadlfhost is on a distinguished road


Default

Yeah, the outer joins are useful, you just have to make sure you select the fields you want and not just use * as that would be too much overhead and a waste of resources. Here were just pull the game fields and the catname.

So its much lighter than pulling the query on every record.

if you dont want to use a join, you could query the cat DB once, then from the array, pull back the category name by using the cat id as the array index. As the category table is small then the array wouldnt be too heavy.

Theres more than one way to approach it, I just prefer joining and let mysql do the work.
Caching the SQL would be even better :P
__________________
Thanks
Paul
I code for FOOOOOD
Your coding errors make me Orgasm LOL

Arcade Themes and Mods
Sniperz Login with Fb and submit your GAMES
ABC Arcade | Youlose | Wicked Games | GirlyCade | Gurl Arcade | Zombie Arcade
lfhost is offline  
Digg this Post!
Reply With Quote
 
Old 06-16-2011, 03:26 AM   #4 (permalink)
JanArve
phpAS Staff
Arcade Warrior
 
Join Date: Jun 2008
Posts: 798
JanArve is on a distinguished roadJanArve is on a distinguished roadJanArve is on a distinguished road

MarketScore (6)

Default

Indeed, another way to do it would be sub queries which I've been a fan of for a while.

http://dev.mysql.com/doc/refman/5.1/en/subqueries.html

It would be interesting to see if outer join or sub queries are faster if you are up for a challenge
__________________


-= Check it out: Php Arcade Script =-
-= Php Arcade Script Templates: Neon Blue | Neon Green | Neon Pink | Dark Charred | Colorful =-
JanArve is offline  
Digg this Post!
Reply With Quote
 
Old 06-16-2011, 06:14 AM   #5 (permalink)
lfhost
Senior Member
 
Join Date: Aug 2008
Posts: 270
lfhost is on a distinguished roadlfhost is on a distinguished road


Default

Hi Jan,

Im not the biggest fan of subqueries as you cant pull fields from the subqueries table i.e catName you can only really compare the data. Unless im mistaken.

But here is the result with out the catName pulled back on the homepage through listGame

PHP Code:
Initial705800bytes | Final: 899048bytes Peak1062624bytes 8 queries used
Script Execution Time
0.0070960521698 
Category Page

PHP Code:
Initial712592bytes | Final: 900808bytes Peak1047568bytes 8 queries used
Script Execution Time
0.00722503662109 
Compared to (From Above with Join)

PHP Code:
Initial712776bytes | Final: 901104bytes Peak1069968bytes 8 queries used 
Script Execution Time
0.00677704811096 
__________________
Thanks
Paul
I code for FOOOOOD
Your coding errors make me Orgasm LOL

Arcade Themes and Mods
Sniperz Login with Fb and submit your GAMES
ABC Arcade | Youlose | Wicked Games | GirlyCade | Gurl Arcade | Zombie Arcade
lfhost is offline  
Digg this Post!
Reply With Quote
 
Old 06-16-2011, 09:12 AM   #6 (permalink)
dmm2020
Arcade Elite
 
dmm2020's Avatar
 
Join Date: Apr 2009
Location: Cheyenne, WY
Posts: 1,029
dmm2020 is on a distinguished roaddmm2020 is on a distinguished road


Default

Now this is what I like to see and has been missing for some time. I don't care whose script this is, posts like this help make us all better script writers. Thanks for your renewed efforts Paul.
__________________


Military Combat Games - come check my new website out!!! - The Casino Arcade - DMSOFT-WY.COM - Webmaster Blog Site
dmm2020 is offline  
Digg this Post!
Reply With Quote
 
Old 06-16-2011, 09:16 AM   #7 (permalink)
lfhost
Senior Member
 
Join Date: Aug 2008
Posts: 270
lfhost is on a distinguished roadlfhost is on a distinguished road


Default

@dmm yes, sharing and comparing always helps improves ones skill set. Im always learning as well as its always ongoing.

Im always open to helping out our community.
__________________
Thanks
Paul
I code for FOOOOOD
Your coding errors make me Orgasm LOL

Arcade Themes and Mods
Sniperz Login with Fb and submit your GAMES
ABC Arcade | Youlose | Wicked Games | GirlyCade | Gurl Arcade | Zombie Arcade
lfhost is offline  
Digg this Post!
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trade Arcade Pro Speed Up/Reduce 80-90% Query Overhead On Average lfhost TradeArcadePro 5 06-21-2011 11:20 AM


All times are GMT -6. The time now is 04:09 AM.


Powered by vBulletin® Version 3.6.3
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
SEO by vBSEO 3.0.0 RC6
© TalkArcades.com
Forum - Register - Calendar - Memberlist - FAQ - Search


Advertise here

Single Sign On provided by vBSSO