Mysql multiple queries Vs. Query in multi-dimensional array

--Apple-Mail-13-1058467181
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit

Hello all,
I have an enterprise application that needs to be optimized/re-
engineered. In the process of re-designing the framework, i stumbled
across the idea of storing large mysql result sets in multi-
dimensional arrays for easy/quick reference.

Basically, my application polls the database looking for 1 of up to 3
documents for each day in the reporting period (1 to 4 weeks) for
multiple clients, and follows a complex hierarchy based on the
document and related information returned. One of the areas i imagine
i am losing resources is in the queries sent to the database where no
result is returned.

My idea involves grabbing all of each document within the reporting
period, and storing each type in its own multi-dimensional array with
all other documents of the same type. Then, throughout the report, i
would check array keys for results instead of querying the database.
It sounds like a sensible solution for taking some of the 20000+
queries of each report, but i'm not sure if the gains would be offset
by the demands on RAM due to the large arrays.

Drew Stokes: Web Development at MPCS
contact | drew@mpcompliance.com | 818.792.4135
The information contained in this email message and its attachments is
intended only for the private and confidential use of the recipient(s)
named above, unless the sender expressly agrees otherwise. If the
reader of this message is not the intended recipient and/or you have
received this email in error, you must take no action based on the
information in this email and you are hereby notified that any
dissemination, misuse, copying, or disclosure of this communication is
strictly prohibited. If you have received this communication in error,
please notify us immediately by email and delete the original message.


--Apple-Mail-13-1058467181--

Re: Mysql multiple queries Vs. Query in multi-dimensionalarray

Drew Stokes wrote:
> Hello all,
> I have an enterprise application that needs to be
> optimized/re-engineered. In the process of re-designing the framework,
> i stumbled across the idea of storing large mysql result sets in
> multi-dimensional arrays for easy/quick reference.
>
> Basically, my application polls the database looking for 1 of up to 3
> documents for each day in the reporting period (1 to 4 weeks) for
> multiple clients, and follows a complex hierarchy based on the document
> and related information returned. One of the areas i imagine i am
> losing resources is in the queries sent to the database where no result
> is returned.
>
> My idea involves grabbing all of each document within the reporting
> period, and storing each type in its own multi-dimensional array with
> all other documents of the same type. Then, throughout the report, i
> would check array keys for results instead of querying the database. It
> sounds like a sensible solution for taking some of the 20000+ queries of
> each report, but i'm not sure if the gains would be offset by the
> demands on RAM due to the large arrays.

I can't see any report doing that number of queries (unless each query
is just selecting a different column in the same table *maybe*), so
looking at reducing that number is a good idea.

The arrays may work - but it depends. Are you fetching the same thing
from the db each time or are you only using each piece of information once?

eg

$title = select title from table where id='x';
....
$title = select title from table where id='x';

If you are, then using an array of some sort would work nicely.

If you're doing the above for each entry in the db, maybe it would be
quicker to retrieve everything and process in php.

$all_titles = select title from table;

It really depends on what the report is doing, what you're fetching from
the database (and how) - so doing a new report using your new ideas
(even with hardcoded values) is about the only way to find out for sure
which is going to be better.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php