SQL Comlicate querys VS PHP Simple manipulation!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mamad876
Forum Newbie
Posts: 6
Joined: Wed Mar 22, 2006 1:59 pm

SQL Comlicate querys VS PHP Simple manipulation!

Post by mamad876 »

Hello all,

I have a problem, that I thought maybe some one have a experience it it.
First lets explain the situations:
I'm currently working on a big online database project with LAMP.
There is a database with about 10 tables, average 15 columns each.
This tables contain information about employees in an organization.
There will be lots of different reports regarding to this informations.
This is fully object oriented based programing in PHP.
So, here comes the challenge:
is it better to make on simple query to database like :

Code: Select all

SELECT *, ... FROM some_table INNER JOIN ...
and then manipulate data with PHP classes and functions to make reports
OR
in the other hand making all reports based on complicated query and relay completely on database results?


thank you very much in advance.
regards,
MAMAD
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: SQL Comlicate querys VS PHP Simple manipulation!

Post by John Cartwright »

SQL will almost always be faster than PHP. I will always suggest moving as much as possible to the query.
mamad876
Forum Newbie
Posts: 6
Joined: Wed Mar 22, 2006 1:59 pm

Re: SQL Comlicate querys VS PHP Simple manipulation!

Post by mamad876 »

Thank for reply,

I've just did a time checking with both ways, almost the same result???
I've just thought you are right with that but the figures are little different!!!

Testing execution time with objects: 0.1707
Testing execution time with query: 0.1848

The query prepared to get the same result, as my object oriented system, provides.

So any answer?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: SQL Comlicate querys VS PHP Simple manipulation!

Post by John Cartwright »

Try doing it with a reasonable dataset, like 20,000 rows (or better yet, 200,000 rows). ;)

.. and when you post benchmarks, you need to post the actual code you used to run it. More often than not, the benchmarks themselves are wrong.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: SQL Comlicate querys VS PHP Simple manipulation!

Post by RobertGonzalez »

Better yet, move all of your queries to the database as stored procedures. Test timing on that and get back to us.

PS I think you might be surprised but the results you get.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: SQL Comlicate querys VS PHP Simple manipulation!

Post by VladSun »

Everah wrote:PS I think you might be surprised but the results you get.
Sometimes very surprising ;)

http://www.pgsql.cz/index.php/PostgreSQ ... procedures
There are 10 types of people in this world, those who understand binary and those who don't
mamad876
Forum Newbie
Posts: 6
Joined: Wed Mar 22, 2006 1:59 pm

Re: SQL Comlicate querys VS PHP Simple manipulation!

Post by mamad876 »

Thanks all,

Yes, I think it's because my testing database was very small,
Actually I'm using stored procedures (MySQL 5.x) and Thats why I want to do manipulations with PHP code.
I think it's not possible or very difficult to create very customizable store procedures and I must come back to classic queries to generate reports.

Anyway I cannot post my codes because it's a huge amount of lines in very different files. and as it based on objects it also will be very difficult for me to post just some lines that actually do the work.

If I have time I will post UMLs of my work.

And for testing with over 20,000 lines, this database will never have 20,000 lines the maximum records will be 500 - 1,000 lines maybe over two or three years, just the data is very in details, each record will have up to 100 to 150 columns!!!

Maybe I should go to Python or even C++ for data manipulations, I don't know don't know really what to do! My brain is locked!!!

regards,
MAMAD
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: SQL Comlicate querys VS PHP Simple manipulation!

Post by RobertGonzalez »

You can manipulate data just as easily with PHP. The question is, do you really want to? If you can fetch the data from the data store the way you want it to display then do that regardless of which programming language you use for the display.
mamad876
Forum Newbie
Posts: 6
Joined: Wed Mar 22, 2006 1:59 pm

Re: SQL Comlicate querys VS PHP Simple manipulation!

Post by mamad876 »

Thanks all friends!

Hard & costly decision, but finally regarding to your replies, I decided to change the way it is!
Now I'm going to change all my code, make it on custom queries. :mrgreen:

Have fun!

Regards,
MAMAD
Post Reply