On Fri, 21 May 2004, LRMK wrote:
> Probably this is not relavent to this mailing list... But any way I ll ask..
>
> I am developing a web site traffic rating system. using a combination og
> perl and java (PERL in serverside, Java Applets in cliend side - My attempt
> to create dynamic images was not that successfull so I use a java applet to
> view the rating info on the site)
>
> Here is my question The rating system is now working but every day I have to
> run a hevy SQL statement to recalculate the traffic ranks of web sites
>
> Something similer to this
>
> $DBC is the connection to the mysql database
>
>
> my $q = $DBC->prepare("SELECT domainname, (viewcount/(($now -
> startedTime)/86400)) AS views_per_day " .
> "FROM site WHERE startedTime<($now -
> 48hours) " . # I do not give ranks for the sites registered withing
> last 48 hours
> "ORDER BY views_per_day DESC")
>
> my $rank = 0;
>
> $q->execute();
>
> while (my $ref = $q->fetchrow_hashref()){
> $rank++;
> my %hash = %{$ref};
> $DBC->do("UPDATE site SET rank = $rank WHERE domainname=" .
> $DBC->quote($hash{'domainname'}));
>
> }
>
> $q->finish();
>
> $DBC->do("COMMIT");
>
>
>
>
> the problem is if there are 1500 qualified records in the table there going
> to 1501 queries (there going to be lots mor records than 1500 in the real
> system)
>
> Question 1 ) Is there a more efficiant way of doing this, atleast to reduce
> the number of queries?
I guess you are updating filed RANK, so that the domain that has more
visits per day gets value 1, the second gets value 2, etc..
Can you just omit this fileld? So you don't need to the script that
recalculates :)
I mean, can you forget about the rank filed and only work with
(viewcount/(($now - startedTime)/86400)) (wiews per day)?
---
Dani Pardo, [EMAIL PROTECTED]
Enplater S.A
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
