Big SQL vs Multiple trips

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
starburst
Forum Newbie
Posts: 1
Joined: Mon Nov 22, 2010 10:37 am

Big SQL vs Multiple trips

Post by starburst »

Hi All,

What are your thoughts on whether it is better to build larger SQL statements in order to minimise DB trips or too separate out the code into more re-usable blocks (at some cost to performance) for the benefit of code structure etc.

Example: Load up a user from a database. A singly query could grab the user record, the groups the user belongs to etc, or I can separate out into user::getUser() and user::getGroupsForUser() etc

Hope you can help
Cheers
User avatar
DaveTheAve
Forum Contributor
Posts: 385
Joined: Tue Oct 03, 2006 2:25 pm
Location: 127.0.0.1
Contact:

Re: Big SQL vs Multiple trips

Post by DaveTheAve »

In my experience, it's better to grab the data as needed and cache it for further use. It'd be more overhead to grab everything at once if your not going to need all of it to render the page.

Personally, if your using the Zend Framework, or wish to implement it in your own framework, I love the methoud shown in Survive The Deep End for Lazy Loading of Domain objects: http://www.survivethedeepend.com/zendfr ... in.objects

It also shows a great methoud as how to cache data to save trips to the DB.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Big SQL vs Multiple trips

Post by pickle »

DaveTheAve wrote:In my experience, it's better to grab the data as needed and cache it for further use. It'd be more overhead to grab everything at once if your not going to need all of it to render the page.
+1

Imagine you've got 20 pages - and in each page you query the database because you need to know the name of the logged in user (in reality you'd use sessions, but that wouldn't serve this example well).

Imagine only one of those pages needs the group membership.

You'd be doing a big expensive query to retrieve the group membership, when 95% of the time it's extra work. On that 1/20 page - yes it would be more efficient to retrieve everything all at once. But for the other 19/20 pages it's much less efficient.

Long story short - do what ~DaveTheAve suggested & only retrieve what you need when you need it.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Re: Big SQL vs Multiple trips

Post by Jenk »

If using an ORM I prefer to lazyload than fetch all. In some systems it is just illogical to pull everything out in one go.

And also I think that link just broke the "Ludicrously and unwieldly long URI" record.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Big SQL vs Multiple trips

Post by josh »

Both. Large queries when working with many records are best
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Big SQL vs Multiple trips

Post by Christopher »

I'd almost always go with a "larger" query. Not sure what "larger" really means in this context though. It almost sounds like JOINs or no JOINs. The point of SQL is to have a language to get the data you need. It excels at that. Queries are probably the most expensive performance hit in web applications, so I generally try to minimize the number of queries per page. However, because SQL is so expressive and databases so complex -- there are many, many exceptions to any rule like this.
(#10850)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Re: Big SQL vs Multiple trips

Post by Jenk »

Given the context of the topic, I think it is safe to assume the OP is referring to an ORM tool pulling out every member of an entity when they are not needed. Given the example of a User and User Groups. If all the view needs is the user name, there is no need to pull out the groups as well. In this specific example I'm betting it'll be negligible difference, but in other examples (including some of my own from the logistics industry) this can be as big a difference as users waiting 30seconds for the page to load.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Big SQL vs Multiple trips

Post by Christopher »

I don't get the impression that the question as about having a getUser() method that does a complex join, and then adding a getUserName() method that just does a single-table query for places that only need that information. Writing additional, optimized queries as you understand the application makes sense (and is mature optimization ;)). I think the question is whether having much simpler code (in this programmer's eyes) is better even if users have to wait 30 seconds for the page to load.
(#10850)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Re: Big SQL vs Multiple trips

Post by Jenk »

I got the impression that that was exactly the question the OP is asking.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Big SQL vs Multiple trips

Post by josh »

Unless one of you two is clairvoyant, you should probably ask instead of debating ;-)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Re: Big SQL vs Multiple trips

Post by Jenk »

The OP is free to chime in at any time :p
Post Reply