|
|
Posted by Erwin Moller on 11/24/06 10:02
Sandman wrote:
> In article <4566b547$0$321$e4fe514c@news.xs4all.nl>,
> Erwin Moller
> <since_humans_read_this_I_am_spammed_too_much@spamyourself.com>
> wrote:
>
>> >> First: Prefetching all members into memory for each page where you
>> >> need one or two members is absolutely a no-go. You must change that of
>> >> course. Just fetch the information you need WHEN you need it.
>> >
>> > Yes, but if I need to fetch information for 100 members on one page
>> > making 100 individual SQL queries is also a no-go.
>>
>> Hi Sandman,
>>
>> As Jerry and Micha indicated: Do not fetch all members into PHP memory
>> each page, just fetch the ones you need.
>
> Right, but how?
Well, it is your platform, how can I tell? ;-)
Can you not find out beforehand what you need?
eg: Who wrote in this topic?
+ to who do they refer?
+ anything else that show up on the page.
I am quite sure that you can tell that beforehand, simply because you do
produce the output eventually.
But it is possible that doing this takes up more time. :-/
Hard to tell from this distance.
<snip>
>
> That's impossible to know beforehand. Articles listed may or may not
> contain references to members. Any given page might contain references
> to member ids from 30 different sources in 30 different tables. I
> can't know, at the beginning of any page, exactly what member
> information will be displayed on that page.
See my former comment.
I stress this point because you claim that the query itself takes only 0.16
secs.
That means that most time is 'lost' by PHP importing this resultset.
If you can drill down the results to 1% by first finding out WHAT you
need......
>
>> 3) Do NOT store the array (or resultset, or whatever approach you have to
>> represent data from the database) in a session.
>
> As in $_SESSION? No, no way. :) I don't even use sessions. :)
Ok good, I thought I better mention it. :-)
>
>> 4) Design your database smart. I mean by this: Design your database in
>> such a way that the queries you typically need run fast.
>
> They do, they run very fast. The query runs in 0.16 seconds, the
> action takes 0.8 seconds (and I'm assuming the extra time is moving
> the resultset to the array).
Is the 0.8 secs the time needed to produce all the HTML also, or is the 0.8
secs the time needed to read the resultset into PHP's memory?
>
>> Did you normalize your database? (In case you don't know what that is:
>> One important thing about normalization is the fact that you represent
>> each piece of data only in one place(=table/column) and make the
>> relations by means of primary and foreign keys.)
>
> Yes.
>
>> The more joins, the slower the queries.
>
> No joins in this select. It's a very simple select and looks pretty
> much like this:
>
> select field, field, field from member.member where status = 'ok' and
> origin = 'LVL';
>
> "LVL" being the code for the particular site. "origin" is indexed. The
> above query, when fetching 9000 posts, takes 0.16 seconds. That I can
> live with it. It's moving that information to a PHP array that seems
> to take too long.
>
>> SO it might help to duplicate some data (denormalization) in some tables.
>> This can help a lot, but use it cautiously, since a good designed
>> database stay as close to 100% nomalized as possible.
>
> Yes, the data I want is all in seperate "field, field, field".
Clear.
Just to be sure, you should of course NOT running seperate queries for each
result you need.
eg BAD:
SELECT username FROM tbluser WHERE (userid=23);
SELECT username FROM tbluser WHERE (userid=36);
SELECT username FROM tbluser WHERE (userid=123);
eg GOOD:
SELECT userid, username FROM tbluser WHERE
(userid IN (23,36,123));
Right now you are fetching them all, so in that case this is irrelevant, but
if you can switch your code in such a way you do know beforehand what you
need, it is important.
> Yeah, I've never actually used them, and wouldn't you agree that that
> wouldn't help me?
Correct, it would only reduce the 0.16 secs you talked about. Not the (0.8 -
0.16) secs.
>
>> 7) Prepared data in the database (Last resort)
>> Do not use this method unless the others do not help enough.
>> I don't like this method, but was forced to use it myself in a few
>> occasions.
>> I'll describe a case I had and how I solved it.
>> It is a huge discussionplatform, and all participants had the possibility
>> to vote on articles of others. The weight their vote carried varried on a
>> lot of factors and the logic was also totally recursive, meaning that if
>> one visitor did one thing (eg read an article, or make a vote), all
>> others had to be updated.
>> So realtime calculation of the whole system was no option for me.
>> I decided to create a table that contained the calculated data, use that
>> data whenever I need it, and run an update every hour or so.
>
> Yes, I do aggregation a lot in five minute intervals (such as labels,
> scores and such). But that's not a part of fetching this information.
>
>> This method is not considered 'good programming' (at least by me), but
>> gets the job done, and in some situation you have no choice.
>
> Indeed. I have a "What's new?" function that lists new content on the
> site, from several tables (articles, calendar, project rooms, forum,
> and so on). That data hgas to be aggregated into a seperate table for
> speed.
>
>
> Thanks for the post, very good advice, but I'm afraid that it won't
> help much for me.
>
Well, I try. :-)
Regards,
Erwin Moller
Navigation:
[Reply to this message]
|