Efficiency of Queries vs. Arrays

[zandermander]

Question for those in the know:

Background:
A few weeks back when reading some postings on a PHP/MySQL board I read a post where a person talked about a three-tier architecture where the first tier was the db queries an such with the results being put into arrays and setting variables. The middle tier was the actual guts (programming) of the solution and the third tier was the user interface.

Now, after several redesigns and rebuilds of my learning-site (is there any other way?) I am actually beginning to implement this architecture!

Question(s):
Just how much more efficient (processor utilization, time to produce pages, server load, etc.) is it to store query results in an array and then manipulate (query, read from, etc.) the array instead of requerying the db?
As an illustrative example, I forsee a table in my solution to have ~15 fields and 1200 records in it. If I need to search this table to pull a dozen records out of it which would be more efficient: doing the query directly or having some PHP code to search through an array which holds all 1200 records?

Is there a break even point? I can see clearly how a small table with ~12 records is best queried once, the result put into an array and using the array from there. But how about the 1200 I used in the example above? Is there a rule of thumb such as # of times I expect to read the array (or do the query) during a session or hits on the site or ???

Many thanks once again for your thoughts and comments!

EZ

[Vin DSL]

Hrm.. interesting question! You are obviously 'one of us', or at least on your way to becoming 'one of us.'

As fate would have it, last night I got interested in this very thing, and added code to my web site that addresses this issue. Now, let's see how sharp you actually are.

Go to my web page by clicking the 'WWW' below this message. Look at the footer of various pages, and tell me what you see, young Luke.

May 'The Force' be with you...

[jason]

PHP's array manipulation has historically been rather slow. For most general purpose applications it works fine, but I would think that loading up an array with 1200 records would be slower than a more specific query. SQL was designed to be an efficient means of getting the data you need out of a database. PHP has some pretty good array handling functions, but they don't compare to the search capabilities of a well designed SQL database (with proper keys, indexing, and such).

If you do a query for 1200 records and only use a quarter of them in your script, then the added overhead of pulling everything into an array is probably overkill. On the other hand, if you need to use the same data in different ways in a single script, or you need two two or three subsets of that data in different places in the script then they array method might work OK for you. Just try to make your queries as sepecific as possible to avoid pulling records you don't need.

--Jason

[zandermander]

Ah, Master Vin!

Cool that footer is! Do it how do you? Difficult is, writing like Yoda.

So I'll write like a normal dufus instead of a Yoda dufus.

Vin, I was on your site a while back - quite amazing skillz you have! (I think there might be 110 queries in my whole site right now - not just one page!) Care to share the code you use for that footer? I think that would most definitely help me decide situation-by-situation how to optimize my code for speed.

Jason, thanks! You reinforced some things I was thinking about how the MySQL "engine" is optimized for searching/pulling data. Why use a screwdriver when a prybar is what's needed? Different tools for different jobs.

The site I'm building will be relatively low-volume once it goes live so if I miss some optimizations or have some inefficient code, it won't be too bad but I very much want to learn best practices for other higher-volume sites/projects in the future.

Many thanks (again)!!

EZ

[Vin DSL]

Originally posted by zandermander
Vin, I was on your site a while back - quite amazing skillz you have! (I think there might be 110 queries in my whole site right now - not just one page!) Care to share the code you use for that footer? I think that would most definitely help me decide situation-by-situation how to optimize my code for speed...

No way, baby! You're part of 'the energy'. The world would never forgive me...

[zandermander]

No way, baby! You're part of 'the energy'. The world would never forgive me...

Vin, information wants to be free and Google knows all. You won't have to worry about forgiveness from anyone though. Here's a nice page that explains it:

http://www.webclass.ru/tut.php?tut=218

[Vin DSL]

Originally posted by zandermander

Vin, information wants to be free and Google knows all...

Lesson number 2: Knowledge is power. I explain things only to those which have no possibility of understanding what I'm talking about, nor the ability to use it. This post is a prime example. You understood what I said earlier, but now you don't have a clue. There is no danger in that...