Slow page loading when using a simple shortcode on the same page multiple times

admin管理员组

文章数量:1391934

I have a simple shortcode which parses "user" id from URL and loads specific data from another mysql db for that user inside one custom page:

$sql_u = 'user';
$sql_p = 'password';
$sql_db = 'database';
$sql_ip = '10.10.10.10';

function fetch_data_func ($atts) {
 global $wpdb,$sql_u,$sql_p,$sql_db,$sql_ip;
 $reg = new wpdb($sql_u,$sql_p,$sql_db,$sql_ip);
 $user = get_query_var('user');
 $result = $reg->get_row($wpdb->prepare("SELECT * from data_table WHERE url_id=%s",$user));
 return $result->{$atts['text']};
}
add_shortcode('fetch_data', 'fetch_data_func');

And this is how I call this shortcode:

Name: [fetch_data text="name"]
Some random text or other WP blocks
Address: [fetch_data text="address"]
Some random text or other WP blocks
City: [fetch_data text="city"]
etc.

I have about 20 columns in that specific mysql db for each user which means about 20 shortcode calls on various parts of the same page in wordpress.

For obvious reasons, when calling the shortcode multiple times inside one single page, loading times drastically increase. I believe with each shortcode call, a new query to database is performed. So, when calling the shortcode 20 times, then 20 queries are made, always fetching the same data and this means (in my case) about 10 secs of page loading on pretty decent apache server.

My question is, how would it be even possible to modify the shortcode so it will actually perform the query only once and fetch all data needed inside "get_row" function and then just use this shortcode inside one single page multiple times without performing any additional new queries each time. Is this even doable in that kind of form or should I look for another approach? I also tested this with get_var and multiple shortcodes, but the result is the same, slow. I'm actually lost here.

Or maybe even better question, would it be possible to call this shortcode multiple times from various parts of the same page without performing a sql query each time again?

Thank you in advance so much for any kind of help!

I have a simple shortcode which parses "user" id from URL and loads specific data from another mysql db for that user inside one custom page:

$sql_u = 'user';
$sql_p = 'password';
$sql_db = 'database';
$sql_ip = '10.10.10.10';

function fetch_data_func ($atts) {
 global $wpdb,$sql_u,$sql_p,$sql_db,$sql_ip;
 $reg = new wpdb($sql_u,$sql_p,$sql_db,$sql_ip);
 $user = get_query_var('user');
 $result = $reg->get_row($wpdb->prepare("SELECT * from data_table WHERE url_id=%s",$user));
 return $result->{$atts['text']};
}
add_shortcode('fetch_data', 'fetch_data_func');

And this is how I call this shortcode:

Name: [fetch_data text="name"]
Some random text or other WP blocks
Address: [fetch_data text="address"]
Some random text or other WP blocks
City: [fetch_data text="city"]
etc.

I have about 20 columns in that specific mysql db for each user which means about 20 shortcode calls on various parts of the same page in wordpress.

For obvious reasons, when calling the shortcode multiple times inside one single page, loading times drastically increase. I believe with each shortcode call, a new query to database is performed. So, when calling the shortcode 20 times, then 20 queries are made, always fetching the same data and this means (in my case) about 10 secs of page loading on pretty decent apache server.

My question is, how would it be even possible to modify the shortcode so it will actually perform the query only once and fetch all data needed inside "get_row" function and then just use this shortcode inside one single page multiple times without performing any additional new queries each time. Is this even doable in that kind of form or should I look for another approach? I also tested this with get_var and multiple shortcodes, but the result is the same, slow. I'm actually lost here.

Or maybe even better question, would it be possible to call this shortcode multiple times from various parts of the same page without performing a sql query each time again?

Thank you in advance so much for any kind of help!

• shortcode

Share Improve this question edited Feb 19, 2020 at 10:56 futurion asked Feb 18, 2020 at 21:20 futurionfuturion 2544 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

PHP and many other languages have a special construct to combine state an behavior – because that's what you want. That construct is called an object.

Objects are defined by classes. So you need a class that remembers the current user data (the state) and delivers the parts that you need (the behavior). This can be very simple. Create a new PHP file named UserData.php and include it right before you register the shortcode.

The content of the file should look similar to this:

namespace WPSE;

class UserData
{
    private $user, $sql_u, $sql_p, $sql_db, $sql_ip;

    public function __construct( $sql_u, $sql_p, $sql_db, $sql_ip )
    {
        $this->sql_u = $sql_u;
        $this->sql_u = $sql_p;
        $this->sql_u = $sql_db;
        $this->sql_u = $sql_ip;
    }

    public function fetch( $atts )
    {
        if ( empty ( $this->user ) {
            $this->fetch_user();
        }
        return $this->user->{$atts['text']};
    }

    private function fetch_user()
    {
        $reg         = new \wpdb( 
                           $this->sql_u, 
                           $this->sql_p, 
                           $this->sql_db, 
                           $this->sql_ip
                       );
        $user_id     = get_query_var('user');
        $this->user  = $reg->get_row(
            $reg->prepare(
                "SELECT * from data_table WHERE url_id=%s",
                $user_id
                )
        );
    }
}

And then you register the shortcode with the namespace:

include_once ('UserData.php');
$user_data = new \WPSE\UserData( $sql_u, $sql_p, $sql_db, $sql_ip );
add_shortcode( 'fetch_data', [ $user_data, 'fetch' ] );

Note that this is completely untested. I just want to give you an idea on how to handle cases like this one.

Also, welcome to WordPress Stack Exchange! :)

You can use commas to separate the parameters and display the data once. This way you can pick whichever fields you want to show.

[fetch_data fields="First Name:first_name,Last Name:last_name,City:city"]

This is the shortcode function:

add_shortcode('fetch_data', 'fetch_data_func');

function fetch_data_func($attr){
    $user_arr = get_user_data_from_db();//use your own code

    //Split by comma then by colon
    $fields = explode(',', $attr['fields']);
    $fields = array_map(function($item){
        $field = explode(':', $item);
        return array( 'label'=>$field[0],'name'=>$field[1] );
    }, $fields);

    //Use Output Buffering so you can include template here
    ob_start();
    foreach ($fields as $field) {
        echo $field['label'] . ': ' . $user_arr[$field['name']] . '<br>';
        //Will show like this 
        //First name: John
        //City: New York
    }
    return ob_get_clean();
}

本文标签： Slow page loading when using a simple shortcode on the same page multiple times