Improving performance of website (PHP)

black-ops · 7 May 2011 at 13:22

I am a designer and have been one for some time now,
I was contracted to develop a rather large system which has thousands upon thousands of rows of data. Let's call this r.

for each row of data we have 3 tables to look up it's associated values for that r. That's fine, that's pretty instantaneous. However, The final part of our calculations require us to calculate even more columns. ultimately the equations I have at the moment are logarithmic in the number of cells we calculate for each r. As it has a relationship to over 500 more columns to search through to get an answer.

When we calculate one row it's fine, but when we calculate 3000 rows each of which calculate 500 more columns it can take up to two hours to process. Bearing in mind we are now running 10 equations which each run their own queries for each row. There is no other way around this, as this is the way their system works...

Now they are moaning that they want to be able to calculate all of this in under 5 seconds... while using PHP as the main language and nothing else...

Can any of you advise with more experience on things like this?

suarve · 7 May 2011 at 14:08

Can't you store the result of the equations in a column?

SMB30 · 7 May 2011 at 16:19

Are you doing the equations in your database or php?

black-ops · 7 May 2011 at 17:40

The Data being extrapolated is from previous user inputs, this get's put into the DB row each time they progress through the system.

On the final page, it queries the pre calculated data to be used in the final calculation. Data is already in the database at this stage.

Our final calculations take up roughly 2k lines of code and this must be executed for each "group" in the system, a single group can physically only be represented by one row of data. Thus we have several thousand groups running several thousand lines of code. The system is literally huge. I can't explain what the system is out of NDA but, after it calculates it stores this data "final answers" after summing everything up into the database. Retrieving the final results is fast as it only has 10 or so queries to run on the results pages.

But it's the calculations which take ages.

It has to loop through every segment that exists and carry out a calculation for it.

black-ops · 7 May 2011 at 17:41

SMB30 said:
Are you doing the equations in your database or php?

Calculations are being carried out in the PHP there is only one part which uses the DB and that was purely a system flaw which I couldn't overcome due to the way they structure their system's logic.
But I've tested that alone against all the rows and that computes in a couple of seconds. The time being taken is literally from the PHP code, which is pretty damn fast when dealing with a couple of hudred rows, it just takes forever when dealing with a huge set.

SMB30 · 7 May 2011 at 17:43

So it is not something you can offload from php for the database to do, possibly faster?

Beansprout · 7 May 2011 at 23:24

Profile the code and figure out what's taking so long - try XDebug.

Hades · 8 May 2011 at 02:50

Assuming there is no obvious area of the code which is inefficient then could you pass this off to a more efficient lanuage? e.g. call a bit of Java or C code which uses threading to process different parts of the calculations in parallel. I assume you can't run threads in PHP (not a PHP expert I'm afraid)?

black-ops · 8 May 2011 at 22:19

Thing is I could in theory do another language, but the pain in the behind part is, the designer of the system changes his goals every five seconds and wont be prepared to pay me more to develop in another language based upon the £7.00 an hour I get at the moment... (love being a student...)

But that aside, I've tried offloading parts into the database it's just the data they supplied me with (all the core components of the calculations) are just so vast and rely on differential equations I wouldn't know where to start with carrying out these calculations in phpMyadmin... yes that's right, they are using that for a huge system... which i'm sure is fine but being the sole programmer on the project it's a lot to do.

Tetsujin · 9 May 2011 at 11:30

Nowt wrong with using phpMyAdmin to administer a MySQL database in a PHP environment.

I think most of us are going to have trouble helping you with specifics due to firstly the scale of the system, and secondly the NDA-caused blind flying that we're having to do.

The general pointer you've already been given about doing as much maths inside the SQL statements as possible, is probably the best one.

Another would be to create caching tables. If you know certain of these rows/columns aren't going to change over certain [periods of time/numbers of updates] and these are involved explicitly in specific sub-sections of the "equations", you could pre-calculate the results of these equations and store them in new caching tables, then simply retrieve in PHP (or JOIN in to existing SQL statements, depending on size) instead of calculating each time.

Can you do any database restructuring? I guess not, but that'd likely be the real long term solution.

Are these equations, equations in the literal mathematical sense? Is there any plain text manipulation/searching going on?