|
|
Posted by Sandman on 11/24/06 09:28
In article <raacm2lh6a0od30stsb5lifn93ieg981c6@4ax.com>,
Michael Fesser <netizen@gmx.de> 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.
>
> If you have to show informations about 100 members on a page then your
> script should already know which members that are. If you want to show
> some blog articles then you know who wrote them, so you can fetch all
> the member informations with a single query, maybe even together with
> the blog articles.
No, not really. For instance, forum posts can be made by a member or a
visitor (depending if the forum is open or not). So making a joined
select from the forum DB and the member DB wouldn't select posts that
aren't associated with a member.
Plus, just tagging DB posts with the member ID associated with it is
just very easy. It's when I need to show who belongs to that ID that
problems arise.
> >[...] But that's not very intelligent either,
> >since a SQL query with "select x, x, x from member where id in(<list
> >of 100's of ids>)" isn't very good.
>
> What's wrong with that?
It's a slow select. Try it.
> >So, basically, I need a function to get information about user X from
> >a member count of tens of thousands while not being time consuming if
> >I do it one time on a page or if I do it 100 times on a page.
>
> Is your database indexed properly?
Yep
> A database with some ten thousand entries is peanuts. It shouldn't
> take much time to fetch a hundred records from it.
It doesn't. Maybe you haven't been reading from the start...
How I do it today:
1. Prefetch information about ALL members (9000 entries)
+ all information always available
- Very time consuming. Somehwere around 0.8 seconds
2. When something is associated by member id, use a function
that references the array created in 1. Done.
Now, 1 is very time consuming and fetches lots of information that
isn't needed on every page, which is dumb. But when 2 arrives, how do
I fetch the information about arbitrary member id's at any given time
without spending 0.8 seconds prefetching them all? it should be
efficient when I want information about one member per page or 300
members per page.
--
Sandman[.net]
Navigation:
[Reply to this message]
|