Page 1 of 1
Big SQL vs Multiple trips
Posted: Mon Nov 22, 2010 10:46 am
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
Re: Big SQL vs Multiple trips
Posted: Mon Nov 22, 2010 11:32 am
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.
Re: Big SQL vs Multiple trips
Posted: Mon Nov 22, 2010 3:57 pm
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.
Re: Big SQL vs Multiple trips
Posted: Mon Nov 22, 2010 7:47 pm
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.
Re: Big SQL vs Multiple trips
Posted: Thu Nov 25, 2010 2:32 pm
by josh
Both. Large queries when working with many records are best
Re: Big SQL vs Multiple trips
Posted: Fri Nov 26, 2010 1:27 am
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.
Re: Big SQL vs Multiple trips
Posted: Sat Nov 27, 2010 12:18 pm
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.
Re: Big SQL vs Multiple trips
Posted: Sat Nov 27, 2010 12:55 pm
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.
Re: Big SQL vs Multiple trips
Posted: Sat Nov 27, 2010 9:25 pm
by Jenk
I got the impression that that was exactly the question the OP is asking.
Re: Big SQL vs Multiple trips
Posted: Tue Nov 30, 2010 11:37 pm
by josh
Unless one of you two is clairvoyant, you should probably ask instead of debating

Re: Big SQL vs Multiple trips
Posted: Sat Dec 04, 2010 4:08 am
by Jenk
The OP is free to chime in at any time :p