Seeding up a large system (web based)

black-ops

black-ops

black-ops

Associate
Joined
4 Mar 2007
Posts
315
Location
United Kingdom
Hello all,

So I have implemented a system on part of my job contract that requires a database of about 10 (columns ) * 4000 (rows), now for each row a specific calculation is made. These rows are displayed upon the selection of the previous parent. For example:

Block A will contain a1,a2,a3,a4 and so on.
The user, if selected all the blocks will see the full set of rows (4000). The calculation will then run through all the rows and use the data from the associated 8 columns (as the two before are used to display the rows).

For example the query is getting data that is specific to that row such as:

$query = mysql_query("SELECT * FROM table WHERE parent = 'x' and child = 'y');

However carrying out this calculation for each row can take up to 5 minutes and my boss is moaning on about how this is unacceptable and we need to do it in a few seconds... How would I shorten the speed of the calculations?

Regards,
 
Can you run a cron to cache the results? If the data doesn't need to be bang up to date, you can run the job in the background then display data from the cache table to the user.
 
That's fine, it's already doing that. He's just complaining about the data entry stage. The user basically has to enter over 4000 inputs which to me is just plain stupid... but it's an online simulation which would be managed by a large team anyway.
Basically runs like this (row by row)

Capture Previous Input data -> Capture current page $_POST data -> Sort data into calculations per row.

So basically it runs a query on all previously inputted data and current data. Then feeds it into several very complex math functions... it does this for each and every row of questions... It's fine retrieving that data takes a few seconds but to calculate it at the start (which is only done once!) it takes a long while to feed all this into the database while calculating...
 
Ok well,

Let say the person using account (x). logs in to create a "project" under the system. This project gives a total number calculated on various contributing factors. There are however 4000 factors to this final value.

When you create a project you have to go through all these factors and enter data.

At the end of entering all this, the system will cycle through each of these factors and carry out a calculation of around 8 calculations per factor. At the end it will then group them and total them. Dumping EVERY single calculated answer for each factor into the database.

This naturally takes a LONG time to do. However my boss isn't willing to accept that I am potentially entering in over 100,000 bits of data into the database and expects it to go through within a few seconds.

Upon editing this, the values are in the database so you can view individual factors within seconds. Which he can't seem to make the correlation as to why that is fast and the other is slow.

So my question is there any known way of handling such a high volume of data and carrying out calculations quickly like this.
 
black-ops said:
Ok well,

Let say the person using account (x). logs in to create a "project" under the system. This project gives a total number calculated on various contributing factors. There are however 4000 factors to this final value.

When you create a project you have to go through all these factors and enter data.

At the end of entering all this, the system will cycle through each of these factors and carry out a calculation of around 8 calculations per factor. At the end it will then group them and total them. Dumping EVERY single calculated answer for each factor into the database.

This naturally takes a LONG time to do. However my boss isn't willing to accept that I am potentially entering in over 100,000 bits of data into the database and expects it to go through within a few seconds.

Upon editing this, the values are in the database so you can view individual factors within seconds. Which he can't seem to make the correlation as to why that is fast and the other is slow.

So my question is there any known way of handling such a high volume of data and carrying out calculations quickly like this.
Click to expand...

Are you letting your DBMS carry out the calculations or are you doing it in a script? My gut feeling is it would be more efficient to let the DBMS do it... if you're already doing that.. I'm stumped.
 
brainchylde said:
Are you letting your DBMS carry out the calculations or are you doing it in a script? My gut feeling is it would be more efficient to let the DBMS do it... if you're already doing that.. I'm stumped.
Click to expand...

lol looks like you're going to be stumped then, my thoughts entirely :/ I honestly can't be going through 1500 lines of code per page to figure out what is going wrong here, I am a solo coder on this project haha, I think the best means would just pop a loading bar on there and say deal with it haha.

It's gone on for far too long.
 
5 minutes sounds like a long time though, have you tried profiling it and seeing where it spends all its time, you might find that someone is doing something very stupid and causing it to be so slow. Also isn't data access normally the slowest part of a web app, so repeated querying is probably not the best approach. Try getting all the data and then working on it.

Oh and make sure you MySQL indexes are setup correctly!
 
You must log in or register to reply here.
Back
Top Bottom