0 Members and 1 Guest are viewing this topic.

Improve Performance for homepage load by more than 80%
« on: December 13, 2017, 04:39:00 PM »
Hello,

The load times of the homepage, after login were between 12 to 15 secs for a membership database of 15K users. This is primarily due to the calls to the luckyspingender functions. I was able to bring this down to 2.5 / 3.0 secs with the changes outlined below.

The main culprit is a SQL join in the luckyspingender function that need to be re-designed in conjunction with a new database column on the user table.

Here are the steps:

1. Modify your USER_TABLE
Note: replace 'osdate_user' with the name of your USER_TABLE. Do this in your MySQL development tool.

Code: [Select]
ALTER TABLE osdate_user 
  ADD COLUMN profilepics_cnt INT(4) default 0 AFTER pictures_cnt



2. Calculate a value for this profilepics_cnt
Note: Also do this in your MySQL development tool. It will calculate and populate this value for all existing members. Change the value for USER_TABLE and USER_SNAP_TABLE in the example below to your database table names.

Code: [Select]
Update USER_TABLE set profilepics_cnt = (select count(*) from USER_SNAP_TABLE where (album_id is null or album_id = 0) and USER_TABLE.id = USER_SNAP_TABLE.userid);
If using SQL Query in your Database, use this instead:
Code: [Select]
Update osdate_user set profilepics_cnt = (select count(*) from osdate_usersnaps where (album_id is null or album_id = 0) and osdate_user.id = osdate_usersnaps.userid);

3. Update /includes/internal/Functions.php
Replace the existing function for updateLoadedPicturesCnt($userid) with the following:

Code: [Select]
function updateLoadedPicturesCnt($userid) {
global $osDB;
/* This function will update teh User Table for the loaded picture count */
$picscnt = $osDB->getOne('select count(*) from ! where userid = ?',array(USER_SNAP_TABLE, $userid));
$osDB->query('update ! set pictures_cnt = ? where id = ?',array(USER_TABLE, $picscnt, $userid));

/* updated profilepics_cnt */
$profpicscnt = $osDB->getOne('select count(*)  from ! where userid = ? and (album_id is null or album_id = 0)', array(USER_SNAP_TABLE, $userid ));
$osDB->query('update ! set profilepics_cnt = ? where id = ?',array(USER_TABLE, $profpicscnt, $userid));
}

This will now update the count of profile pictures every time photos are loaded or deleted.

4. Update /plugins/luckySpinGender/libs/luckySpinGender.php

Replace
Code: [Select]
$sql = 'select snap.userid as userid, count(snap.userid) as snaps_cnt from ! as snap, ! as usr where usr.id <> ? and snap.active=? and ifnull(snap.album_id,0) = 0 and usr.status =? and usr.active=1 and usr.id = snap.userid and ((snap.userid <> ? and ? is not null) or ? is null) and usr.gender=? group by snap.userid';

if (!isset($_SESSION['luckyspin_id_'.$spinGender])) $_SESSION['luckyspin_id_'.$spinGender] = '';
$luckySpinRecs=array();

$luckySpinRecs = $GLOBALS['osDB']->getAll($sql, array(USER_SNAP_TABLE, USER_TABLE, $uid, 'Y', 'active',  $_SESSION['luckyspin_id_'.$spinGender], $_SESSION['luckyspin_id_'.$spinGender], $_SESSION['luckyspin_id_'.$spinGender], $spinGender ) );

with
Code: [Select]
$sql = 'select id as userid from ! where status =? and active=1 and id <> ? and gender=? and profilepics_cnt > 0';

    if (!isset($_SESSION['luckyspin_id_'.$spinGender])) $_SESSION['luckyspin_id_'.$spinGender] = '';
    $luckySpinRecs=array();

    $luckySpinRecs = $GLOBALS['osDB']->getAll($sql, array(USER_TABLE, 'active', $uid, $spinGender ) );


There are a few other places in the application that join USER_TABLE with USER_SNAP_TABLE for the purposes of determining the profile picture count. Other queries can also be updated to take advantage of this new column.

Good luck.

« Last Edit: December 15, 2017, 06:29:25 AM by Pharg »
PHPStorm, MAMP Pro, Sequel Pro
Currently converting my 2.1.6 site to osDateEvo 1.2

*

Pharg

  • *****
  • 3,107
    • OsDateFourm
  • osDate Version: osDateEvo v1.3
Re: Improve Performance for homepage load by more than 80%
« Reply #1 on: December 13, 2017, 08:55:33 PM »
Hi arievaul,

Thanks for sharing the info anything that can speed the loading up is well worth it. :)

Regards,
Pharg ( Phill )

REMEMBER: ALWAYS BACKUP BEFORE YOU MAKE ANY CHANGES!!

osDateEvo v1.3 | PHP: 5.3.42 & PHP: 7.1.15 | MySQL: 5.5.35

*

Pharg

  • *****
  • 3,107
    • OsDateFourm
  • osDate Version: osDateEvo v1.3
Re: Improve Performance for homepage load by more than 80%
« Reply #2 on: December 14, 2017, 04:40:21 AM »
Hi arievaul,

Only problem with Step 4.

4. Update /plugins/luckySpinGender/libs/luckySpinGender.php

The luckySpinGender.php stops working.
Regards,
Pharg ( Phill )

REMEMBER: ALWAYS BACKUP BEFORE YOU MAKE ANY CHANGES!!

osDateEvo v1.3 | PHP: 5.3.42 & PHP: 7.1.15 | MySQL: 5.5.35

Re: Improve Performance for homepage load by more than 80%
« Reply #3 on: December 14, 2017, 06:13:44 PM »
Hi Phil,

I tried the same set of changes again on a default install of osDate evo and did not encounter any problems.  Did you get an error message or did it seem to appear like the profile did not change after some of your clicks?

I experienced the latter but when I debugged it, it does appear that the profiles are being refreshed. However, the test database is really small i.e. for the men, it is cycling through only 3 matching profiles (one of the profiles had no pictures so was excluded). The random function would sometimes pick the same man a couple of times in a row - and because the refresh is so fast, it appears like the profile is not changing (on my database of about 5K+ men, the refresh happens in less than 1 sec and I haven't gotten the same profile show up more than once yet). As you keep clicking 'Spin Again', does the image eventually change or does it never changes?

If you did get an actual error message, please let me know also.

Thanks!
PHPStorm, MAMP Pro, Sequel Pro
Currently converting my 2.1.6 site to osDateEvo 1.2

*

Pharg

  • *****
  • 3,107
    • OsDateFourm
  • osDate Version: osDateEvo v1.3
Re: Improve Performance for homepage load by more than 80%
« Reply #4 on: December 14, 2017, 08:14:40 PM »
Hi arievaul,

I had to upload the photos again to get it to work but then I would get 2 Random Men in two separate Spins, not  the
1 Random Men and 1 Random Woman like it is normally.

Didn't work correctly for me sorry, no error shows, just 2 Randoms of the same gender.
Regards,
Pharg ( Phill )

REMEMBER: ALWAYS BACKUP BEFORE YOU MAKE ANY CHANGES!!

osDateEvo v1.3 | PHP: 5.3.42 & PHP: 7.1.15 | MySQL: 5.5.35

*

Pharg

  • *****
  • 3,107
    • OsDateFourm
  • osDate Version: osDateEvo v1.3
Re: Improve Performance for homepage load by more than 80%
« Reply #5 on: December 14, 2017, 08:46:55 PM »
Hi arievaul,

I also did a website speed test before the changes, it loaded in 1.2 seconds, after adding the changes, loading went
up to 2 seconds.
So I am not sure why that happened, but it increased the loading speed.
Regards,
Pharg ( Phill )

REMEMBER: ALWAYS BACKUP BEFORE YOU MAKE ANY CHANGES!!

osDateEvo v1.3 | PHP: 5.3.42 & PHP: 7.1.15 | MySQL: 5.5.35