CODEWITHSUNDEEP
phpsqljoinwhile-loopnested-loops
Oranges13
Oranges13

Reputation: 1084

Which is more efficient: a SQL JOIN or nested PHP loop?

I have several tables from which I need to display data.

Schools
-------
id | Title

Programs
--------
id | descr | title | type | school.id

Granted, not the best set up but there isn't any thing I can do about the database structure.

I need to create a list, seperated by School, Program, and Program Type. Right now, I have a big huge nested loop (pseudo code):

Select * from Schools
For Each School
    print $school_header;
    Select Program Type from Programs WHERE School.id = $school_id 
        For Each Program type
           print $type_header;
           Select * from Programs where School.id = $school_id and type = $program_type
           For Each Program
               print $program_link;

Needless to say its a big mess.

The end result is a list:

Is there a way to do this with fewer queries and less code that isn't so database intensive?

Upvotes: 2

Views: 2169

Answers (4)

Mixxiphoid
Mixxiphoid

Reputation: 1034

MSSQL is made to perform joins. Moreover, with a bit luck your tables may be indexed which will increase the performance even more.

It is (highly probably) always better to run a complex query than to loop trough all the data with php.

EDIT:

Maybe you can use aliases to mark the structure of the data.
Example: SELECT id as test_id .... SELECT program as test_test2_program... etc.

Upvotes: 1

Chris Shain
Chris Shain

Reputation: 51319

The real issue here is not whether one or the other programming language is more efficient. The vast, vast difference in performance is due to data locality, namely that executing the query on the database itself doesn't require you to move all of the data back and forth over the wire for evaluation, whereas the PHP solution does.

For your query, what you want to do is write a stored procedure which returns multiple result sets (for further joining in PHP) or a single large result set (by joining in SQL). My guess would be that it would look something like:

SELECT S.Title as School_Title, pt.title as Program_type_title, p.title as Program_Title
FROM School S 
INNER JOIN ProgramType pt on s.id = pt.school_id
INNER JOIN Program p on pt.id = p.program_type_id
ORDER BY S.Title, pt.title, p.title

This will give you the large rectangular array, ordered by each title in order of priority. This way you can loop through rows until you see the School title change, or the Program Type title change, or whatever, and render the appropriate closing tags.

Upvotes: 6

Cade Roux
Cade Roux

Reputation: 89651

SELECT *
FROM Schools
INNER JOIN Programs
    ON Programs."school.id" = Schools.id
INNER JOIN Groups
    ON Groups."programs.id" = Programs.id
INNER JOIN GroupsToCourses
    ON GroupsToCourses.group_id = Groups.id
INNER JOIN Courses
    ON Courses.course_id = GroupsToCourses.course_id

It will almost certainly outperform your code in PHP, and can significantly outperform it by saving database roundtrips, data on the wire as well as letting the database optimizer do its thing.

Upvotes: 0

Daniel A. White
Daniel A. White

Reputation: 190907

MSSQL will do a much better job. Its how RDBMS are designed to handle this sort of thing. If you are doing them on the db server, you are saving your network utilization.

Upvotes: 3

Related Questions