Pre load vs. Ajax - want your opinion

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
internet-solution
Forum Contributor
Posts: 220
Joined: Thu May 27, 2010 6:27 am
Location: UK

Pre load vs. Ajax - want your opinion

Post by internet-solution »

I have a PHP page with a table with 150 * 6 cells. Each cell value has some comments associated with it. I want to show the comments with onmouseover event + javascript. Values and comments are stored on a MySQL server on separate tables.

I am considering three options:
[*] Pre load all the values and comments when the page is loaded - takes longer, but I need only one transaction between web and MySQL server.
[*] Pre load the values on page load, then load comments via Ajax when needed, initial page load is much faster, but the page may request up to 150 * 6 inidivdual database transactions.
[*] Pre load the values on page load, then load the comments via AJAX in to a javascript array. Comments are shown from the array without further database transactions - compromise between the other two options, but needs more scripting

Just wondering what you think re the above options with respect to web / db performance and user experience.

I would also like to know about other alternatives.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Pre load vs. Ajax - want your opinion

Post by social_experiment »

I would go with option 2, only load whatever is needed.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Pre load vs. Ajax - want your opinion

Post by califdon »

I'm tempted to agree with social_experiment, but I would say that it depends on how you think users will use the comments. If you expect that typical usage would be for a user to only need to view the comments for a few of the values (maybe just 1 or 2 even), I would probably go with option #2, but if they are quite likely to need to refer repeatedly to various comments, perhaps for a large number of values, then preloading all of them might be preferable.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Pre load vs. Ajax - want your opinion

Post by Jonah Bron »

Since you're using the mouseover event, I'd go with option 3. If you had to click something, option 2 + caching would be better.
internet-solution
Forum Contributor
Posts: 220
Joined: Thu May 27, 2010 6:27 am
Location: UK

Re: Pre load vs. Ajax - want your opinion

Post by internet-solution »

Thank you for your replies. I will most probably reconsider if onclick is more appropriate compared to onmouseover and see if option 2+caching is doable.
KCAstroTech
Forum Commoner
Posts: 33
Joined: Sat Aug 27, 2011 11:16 pm

Re: Pre load vs. Ajax - want your opinion

Post by KCAstroTech »

Let me fist apologize for the long post, I hope you find it useful. :D
I recently bumped into this very question myself with a very similar situation. In mysql I've got several tables with foreign keys linking them to parent tables. My question was whether it was better to load all of this information into the page or let jQuery & Ajax do the work.
Here are some pros and cons for both as I saw it:

Preload (server side):
Pros:
  • One call to the server
  • Server does most of the work making less work for the client/browser
  • Less programming work (typically it is easier to write in PHP all that is needed to assemble the table)
Cons:
  • Lots of data to send, of which not all may be needed or viewed
  • More work for the server which if you are on a shared server can greatly impact your usage and they may shut you down (as they did me)
jQuery/Ajax (client side):
Pros:
  • Uses less server resources
  • Minimizes bandwidth usage (only raw Json data is being sent instead of the assembled rows and cells)
  • Can typically be faster (since the server is only sending exactly what is needed at the point of call, also more and more client computers are much faster than they were 5+ years ago and can handle the work much more easily)
Cons:
  • Multiple calls to the server (each time you hover and the data isn't cached it has to request the data from the server)
  • More work for the client (on older slower client machines you may run into it taking longer for their computer to try and assemble the data versus just having the server send it pre assembled)
  • Potential compatibility issues as you now have to write your JavaScript to work across multiple browsers
  • More programming work since you now have to have a javascript file to handle the events and ajax queries and a php script to then get what was requested and send it back)
In my situation each table row had basic information about a specific user, then when you hover over that user it displayed additional information (mostly notes). However, someone looking at the table is most likely only interested in seeing the additional information on only a few of the rows. In going with option 1 then I just had the server both process and send information that is never seen nor needed, which on a large table with multiple people looking at it didn't make my website host happy as I was using a lot of resources.

Ultimately I decided to go with option 2. While if someone decides to look at ALL of the data then there is going to be a lot of calls to the server and mysql transactions, it was very unlikely. Additionally I was offloading a large chunk of the processing and memory usage from the server to the user, thus getting my server resource usage under control and my host was again happy. Also consider that servers are designed to handle multiple transactions. The trick is to compromise so that you aren't sending too little information and thus making more calls and you aren't sending too much information with fewer calls at which point you lose the benefit.

I did consider and even tested option 3, but decided that this was not for me as I was again sending useless information. What I did was that each time I hovered over a row, it checked the jQuery data object for that row to see if the data was there, if not it would send an ajax request to populate it. Then once the data was there it displayed it and if user hovered over it again it simple used the now cached data instead of making another call. Also since each row had to look in multiple mysql tables to get all of the data, instead of making multiple ajax requests I created a special php file that would handle that job request and then retrieve all of the relevant data for that row and pass all of it back in a single ajax request.

The end result was that the page was lighter and loaded faster, and when hovering there was a small delay (<2 seconds) before the data was retrieved, assembled and displayed. All in all, I was actually quite pleased with the results.

PS: Again sorry for the long post. :D
Post Reply