Could you fix your code. The while loop doesn't terminate and as the indenting has gone ary somewhere in posting, it's not immediately obvious where the missing '}' should be?

I re-pasted it, please let me know if it still isn't working.....

... If you cut and paste the code from here you may need to make 2 edits on line 33 and 52 because those lines wrap and have + symbols on them...

- dEvNuL

I don't get this code?

You take the keys in turn and break them into their constituant parts.

while(my($key,$val) = each(%ret)) {
  my @parts = split(/\./, $key);
  my $id = '';
  foreach my $add (@parts) {
[download]

Then you iterate over those parts and concatenate them back together, piece by piece, into $id.

    $id .= $add;
[download]

And then, if $id (so far) doesn't match $key, you skip to the next iteration

    if($id eq $key) {
      next;
    }
[download]

Which means you'll never reach this code, until you've completely re-built $id to match $key.

    if(!$ret{$id}{'count'}) {
      $ret{$id}{'count'} = 0;
      $ret{$id}{'head_count'} = 'head_count';
      $ret{$id}{'cat_count'} = 'cat_count';
      $ret{$id}{'subcat_count'} = 'subcat_count';
    }
    $ret{$id}{'count'} += $ret{$key}{'count'};
    $id .= '.';
  }

}
[download]

Which, unless I'm missing something (quite possible), there is no need for split or the for loop as this would do the same thing:

while(my($key,$val) = each(%ret)) {
    $id = $key;
    if(!$ret{$id}{'count'}) {
      $ret{$id}{'count'} = 0;
      $ret{$id}{'head_count'} = 'head_count';
      $ret{$id}{'cat_count'} = 'cat_count';
      $ret{$id}{'subcat_count'} = 'subcat_count';
    }
## Though this doesn't do much?
    $ret{$id}{'count'} += $ret{$key}{'count'};
}
[download]

What did I miss?

BTW. It won't help much towards your performance goal, but you can save a bit of memory by changing:

my @keys = sort keys %cats;
my $cnt = scalar @keys;
...
@keys = keys %ids;
$cnt = scalar @keys;
...
@keys = keys %ret;
$cnt = scalar @keys;
[download]

to

my $cnt = keys %cat;
...
$cnt = keys %ids;
...
$cnt = keys %ret;
[download]

which does the same thing without copying all the keys to an array or sorting one set of them for no reason. Actually, if your low on memory, avoiding that sort and the (re-)allocation of those arrays might help your performance.

---

Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.

Lingua non convalesco, consenesco et abolesco. -- Rule 1 has a caveat! -- Who broke the cabal?

"Science is about questioning the status quo. Questioning authority".

The "good enough" maybe good enough for the now, and perfection maybe unobtainable, but that should not preclude us from striving for perfection, when time, circumstance or desire allow.

Do I understand correctly that all that you do is something like:

  select a,b,c,d,count(*)
  from x
  group by a,b,c,d
[download]

Can you please show the SQL query?

Raafschild

Yes... The problem is my orange is not performing well, so I'm switching to an apple. The question is: which apple should I use?

The code, in this case, does (or rather will, if I go forward with it) indeed read from the database as that is how it will populate the data structure I am using. The point is, this "server" would be persistent. So each request would only need to execute the code I am timing (and to fetch any data not yet in its cache or which is "dirty".

I wanted to avoid getting into the SQL specifics, because I use some Postgres-specific stuff which is not immediately apparent. My query is something like:


SELECT count(*) as count,category.category,nlevel(category.category) A
+S level,
subpath(category.category,0,nlevel(category.category)-1) as parent,
category.head_title, category.cat_title, category.subcat_title FROM da
+ta, category
WHERE
data.category <@ category.category
GROUP BY category.category, category.head_title, category.cat_title, c
+ategory.subcat_title
[download]

The category table looks like:


         Table "category"
    Column    |       Type         
--------------+-------------------
 head_title   | character varying  
 cat_title    | character varying  

 subcat_title | character varying  
 category     | ltree
[download]

The data table looks like:


                 Table "data"
       Column       |           Type                                  
+ 
+----------------------------------------------
 id                 | integer                  
 category           | ltree[]
[download]

... there are other columns here but these are the ones relevant to the query.

- dEvNuL

This certainly is a fruit of a different kind ;-).

You might want to store the results of the query in a summary table and have that table maintained by a set of triggers on the data table. That way the persistence and the caching is handled by the database, so you don't have to write it yourself.

Queries on this summary table should by quick, and all changes in the data table are immediately available in the summary table.

Sorry for a not very perlish suggestion in this forum.

Raafschild

This is a fascinating tool. I'm going to need to study this for a while.. :)

Might you know what happens if I start a process with say "200mb" of memory and I try to stuff 300mb into it? Does the call to "set" fail?

- dEvNuL

You can also get help from SQL gurus. There are often tips for each database that dramatically improve performance by moving things around a bit.

If you've truly done all you can on the tuning front, then please disregard. :)

Thank you for the timely comment.. :) The database is Postgres using the ltree extension. I've done all I can in the tuning side for now, I've fooled with various ways of doing the query, but its just a matter of the type of work to be done.

- dEvNuL

But, as you said, you aren't doing the same kind of work in perl ... so it's likely that if you do the work differently in SQL it'll be faster. I'd put money on postgresql vs. perl for calculating a table of numbers from another set of numbers.

Saying that it matters a _lot_ what version of postgresql you are using, so an upgrade to postgresql-8.0 could provide the speedup you need ... and an increase in other parts :).

--
James Antill

I am runing postgres 7.4. I don't see much on the Postgres site about performance improvements, except for the somewhat vague statement "This release has a more intelligent buffer replacement strategy, which will make better use of available shared buffers and improve performance.".

The situation (on my development box) is one of *SEVERE* disk thrashing. Just doing a "select count(category),category from data" and I can hear the disk drive sounding like it is going to take off.

While I agree that Postgres can summarize rows faster then I could ever hope to, it lacks the sophisticated cache configurations that are possible with other RDBMS (notably Sybase or Informix, which if I could use either of those here could probably make this problem a non-issue).

That being said, I will give upgrading Postgres a shot, but I am not optimistic as it still relies on the operating system to cache files and thus there is no way to ensure certain tables have a higher caching priority.


- dEvNuL

Dealing with heirarchal data is precisely what ltree was designed to do (using GiST indexes).

I do tend to agree that this is not ideally done in a relational database, which is the purpose of this post. I'm trying to find a different/better solution but so far am coming up empty. :(

- dEvNuL