Page 1 of 1

SQL Comlicate querys VS PHP Simple manipulation!

Posted: Mon Apr 28, 2008 10:56 am
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

Re: SQL Comlicate querys VS PHP Simple manipulation!

Posted: Mon Apr 28, 2008 11:36 am
by John Cartwright
SQL will almost always be faster than PHP. I will always suggest moving as much as possible to the query.

Re: SQL Comlicate querys VS PHP Simple manipulation!

Posted: Mon Apr 28, 2008 1:59 pm
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?

Re: SQL Comlicate querys VS PHP Simple manipulation!

Posted: Mon Apr 28, 2008 4:12 pm
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.

Re: SQL Comlicate querys VS PHP Simple manipulation!

Posted: Mon Apr 28, 2008 4:41 pm
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.

Re: SQL Comlicate querys VS PHP Simple manipulation!

Posted: Mon Apr 28, 2008 5:37 pm
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

Re: SQL Comlicate querys VS PHP Simple manipulation!

Posted: Mon Apr 28, 2008 10:53 pm
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

Re: SQL Comlicate querys VS PHP Simple manipulation!

Posted: Mon Apr 28, 2008 11:35 pm
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.

Re: SQL Comlicate querys VS PHP Simple manipulation!

Posted: Tue Apr 29, 2008 12:35 am
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