Support

Account

Register Login

Home › Forums › ACF PRO › Performance issues with hundreds of queries

Solved

Performance issues with hundreds of queries

  • Hi everybody,
    I have a website for a client (http://vfl-gummersbach.de) wherefor I enormusly make use of ACF fields and espacially repeater fields.
    The problem is that we first had about 700 queries for the main page, which causes CPU problems to the new server the client moved the site to (AMD Opteron(tm) Processor 6378 Quadcore, each 1400MHz (Turbo up to 2800MHz each approaching full capacity).
    After each sport-event the page has up to 3,000 visitors (roundabout 30 people visiting simultaneously) and dramatically slow down the performance – or crash the server in worst case…

    After I made use of the new JSON-feature I could reduce the amout of queries by having 100 queries less on each page.

    I tried to make use of this method but I couldn’t benefit from it: http://support.advancedcustomfields.com/forums/topic/improve-performance-of-images-in-repeater-fields/

    I am not sure how complex the queries get as I save a lot of fields on optionpages…

    Is there anything I can do to have less complex queries?

    I tried to make my loops as nice as possible, e.g. the gamebox on top of the right sidebar:

    <?php $repeater = get_field('spieluebersicht', 'option');
    $games = count($repeater) + 1;
    $reversed_repeater = array_reverse( $repeater );
    $i = 0;
    foreach ( $repeater as $game ) {
		$row = $reversed_repeater[$i];
		$gespielt = $row['gespielt'];
		$i++;
		if ( $gespielt == 1 ) break;
	}
    $last_game = $reversed_repeater[$i-1]; // get the first row
    $next_game = $reversed_repeater[$i-2]; // get the first row
    $logo = $last_game['logo_gegner']; // get the sub field value
    $tore_heim = $last_game['tore_heim']; // get the sub field value
    $tore_gast = $last_game['tore_gast']; // get the sub field value
    $spielart = $last_game['spielart'];
    $next_spielart = $next_game['spielart'];			
    $next_gegner_logo = $next_game['logo_gegner'];
    $spielort = $next_game['spielort'];
    $datum = $next_game['datum'];		
	$template_url = get_template_directory_uri();
	$logo_img = wp_get_attachment_image( $logo, 'thumbnail' );	
    ?>

	<div id="spielinfo" class="box left">
        <div id="spiele" class="box_content">
            <h1>Letztes Spiel</h1>
            <div id="spiele_navi">
                <a id="naechstes" class="spiele_link right" title="Nächstes Spiel"></a>        
                <a id="letztes" class="spiele_link_aktiv right"></a>
                <div class="clear"></div>
            </div>
            <div id="last_game">
				<?php if ( $i >= $games ) :?>
                	<h2 class="no_last_game">Derzeit liegt kein gespieltes Spiel vor.</h2>
                <?php endif;?>
                <div id="heim">
                    <?php if ($spielart == "Heimspiel") : ?>
                    <img src="<?php echo $template_url; ?>/images/vfl_logo.png" alt="Vfl Gummersbach Logo"/>
                    <?php else : ?>
                    <?php echo $logo_img; ?>
                    <?php endif;?>
                </div>
                <div id="gast">
                    <?php if ($spielart == "Heimspiel") : ?>
                    <?php echo $logo_img; ?>
                    <?php else : ?>
                    <img src="<?php echo $template_url; ?>/images/vfl_logo.png" alt="Vfl Gummersbach Logo"/>
                    <?php endif;?>
                </div>
                <div id="ergebnis">
                <h2><?php echo $tore_heim;?>:<?php echo $tore_gast;?></h2>
                </div>
            </div>
            <div id="next_game">
				<?php if ( $i != 1 ) :?>
                    <div id="gegner">
                        <?php echo wp_get_attachment_image( $next_gegner_logo, 'thumbnail' ); ?>
                    </div>
                    <div id="spielort">
                    <span id="datum"><?php echo $datum;?></span>
                    <?php if ($next_spielart == "Auswärtsspiel") : ?>
                    <?php echo $spielort;?><br/>
                    <a href="/bundesliga/spielplan/"><img class="icon" src="<?php echo $template_url; ?>/images/kalender.png" alt="Kalender"/></a>
                    <?php else : ?>
                    SCHWALBE arena<br/>
                    <a href="/bundesliga/spielplan/"><img class="icon" src="<?php echo $template_url; ?>/images/kalender.png" alt="Kalender"/></a>                        
                    <a href="/tickets/wie-und-wo/"><img class="icon" src="<?php echo $template_url; ?>/images/ticket.png" alt="Ticket"/></a>
                    <?php endif;?>
                    </div>            
                <?php else : ?>
                	<h2>Derzeit liegt kein anstehendes Spiel vor.</h2>
                <?php endif;?>
            </div>
            <script type="text/javascript">
            var spieleinfo = "<?php the_field('spieleinfo', 'option'); ?>"
            </script>
        </div>
        <div id="tabelle_gegner" class="box_content">
            <a href="/tabelle">
            <div id="tabelle">
                <h1>Tabelle</h1>
                <div id="sis">
                          <?php { $host = "www.gatecom.de"; $url = "http://$host/"; $check = @fsockopen($host, 80, $errno, $errstr, 3); } ?> 
                          <?php if($check) : ?>
                          <script type="text/javascript" src="http://www.gatecom.de/jsexport/jscore.aspx?art=tabelle&liga=<?php the_field( 'sis', 'option' );?>&style=costum"></script>
                          <?php else : ?>
                          <h2>Leider ist dieser Service von SIS-Handball derzeit nicht verfügbar</h2>
                          <?php endif; ?>
                </div>
            </div>
            </a>
        </div>
        <div id="liveticker" class="box_content">
            <h1><a href="http://www.dkb-handball-bundesliga.de/live/matches" target="_blank">Liveticker</a></h1>
        </div>
    </div>
<?php endif;?>
    Bildschirmfo....png

  • The best thing that you can do is implement some type of caching mechanism so that the queries are not performed on every page load. There are plenty of caching plugins available, you just need to pick the one that’s right for you.

  • Thanks for your advice but I have already done this.
    Currently I use W3 Total Cache with its page-, db- and browser-caching function.

    I was more like searching for a method to bundle different queries (e.g. repeater data).

  • if it was associated with a post, I would say to use get_post_meta($post_id), because this causes WP to get all the meta values for a post and cache them.

    With options, ACF sets the autoload to “no” and there isn’t a filter to change this. If autoload was set to “yes” then all of the options would be cached by WP and not require additional queries, however, the options would be loaded on every page load rather than just on the pages that need them.

    Like I said, ACF does not have a filter to alter the autoload value and after digging through WP core it appears that neither does WP. You may want to post something in feature requests about adding a way to alter autoload on ACF option page values.

    As I said in my previous post, I would use some type of caching mechanism, perhaps set up my own transient cache for to store the results of the html fragment that’s being generated.

  • I see… So the main problem I think is, that most of the queries occour by the amount of sub-fields in repeater fields I have on option pages.

    If I would create a pseudo option page which would be in fact a custom post type with only one post the repeater would perform better?

  • It’s quite possible, you should probably do some testing, but, my understanding is that if you use get_post_meta($post_id) without specifying a meta_key that WP gets all of the meta values for a post in a single query. WP also caches the results of this in a non-persistent cache so that the next time the function is called it uses values it already has. The ACF get_field() and other functions are just wrappers for get_post_meta() and get_option().

  • Ok that makes sense. I tried to use get_post_meta() at the beginning of my single.php but had some difficulties with deserializing my gallery-field… But isn‘t this field only queried once?
    When I use the get_post_meta here would have to loop each attachment-ID with wp_get_attachment_image_src to get the sources for each image, which would go along with additional queries and would not improve the performance, right?

    <?php $post_data = get_post_meta($post->ID); 
$galerie_neu = $post_data['galerie_neu'][0]; 
if ($galerie_neu) {
($galerie_neu) { 
$galerie_neu = unserialize($galerie_neu);
foreach ($galerie as $galerie_item) {
$galerie_id = $galerie_item['bild'];
$thumb = "galerie";
$full = "large";
$image = wp_get_attachment_image_src( $galerie_id, $thumb );
$fancybox = wp_get_attachment_image_src( $galerie_id, $full );
?>…

    I guess the main amount of queries is generated through my option-fields (about 400 out of 600). I checked my database and found about 100 unused old fields which are not listed in the backend anymore – would deleting them enhance the performance?
    Caching the option-fields would – in my optionion – solve my performance problem as every single page makes up to 100% use of all these fields. So I would have to manually change all option-field-entries in the DB to autoload = yes? Would that be a conclift issue with the JSON-feature?

    I will mark your post as the solution – because it helps in generally but in my eyes does not help in my special case.

    Thanks again for your help!

  • A solution I have used in a similar case was to run the fields loop once and then output the HTML from the loop to an option of it’s own in the DB. Next time the page is ran if the page saved date is not newer then the option save date then the HTML is taken from the HTML option in the DB.

    The data can be grabbed in the add_action(‘acf/save_post’);

    This results in one call to the DB if the post has not been altered.

    Hope this helps

    Kieran

  • Really old post, sorry @kulturbanause, didn’t see you’re follow up back then. To clarify my previous comment, I don’t actually use the value returned from

    
$meta = get_post_meta($post_id);

    instead I just use that function call for force WP to get every custom field for a post. Once that is done then I use get_field() and other ACF functions because the values are cached and the WP cache. It is just a way to reduce DB requests.


    @kcalv    , you could also use a persistent cache that can cache html fragments for this, or even the WP the WP Transients API https://codex.wordpress.org/Transients_API

Viewing 9 posts - 1 through 9 (of 9 total)

The topic ‘Performance issues with hundreds of queries’ is closed to new replies.