Hello,

I am still working on displaying statistics using dbman and I have one more question...

My goal now is to minimize the times that I call &query for a particular report. Let me explain with an example...

I have a select field called "Status" which can be filled with different items such as "OPEN, CLOSED, FINISHED, ANSWERED..".
One of my reports shows the number of records in all the possible statuses for a particular time frame (Lets say January).

Sooo, in order to accomplish this, I have set up a loop that goes through all the possible statuses (OPEN,CLOSED,...) and calls up &query as many times as different statuses there are. i.e.

$in{"Date"}="Jan";
$in{"Status"}="$WhateverStatusValue";
my ($status,@results) = &query('view');

Even though this method works now, it will obviously get slower and slower as more records are added hence my question:

Is there way I can just call &query with the "Date" once

$in{"Date"}="Jan";
my ($status,@results) = &query('view');

and go trough each of the records that resulted from the &query so that I can count how many are in all different statuses

foreach $rec (@rec) {
if ($rec{Status} eq "OPEN") { opencoutner++}
elseif ($rec{Status} eq "CLOSED"){ closedcoutner++}
....
}
or something like that...

Cheers

-JO

Sounds like it would work. Have you given it a try?



------------------
JPD

Hi

It almost does, but not quite. This is breaking all the ideas I had about performance. I must be missing something here...

I modified the routine to avoid calling &query a million times but it seems that it takes longer now than before (considerably I might add...)

This is the old loop using &query


Before this loop I set up $in{"Date"}="Jan" So it searches all different statuses in January when it calls &query.

The new loop looks like this

I first set up $in{"Date"}="Jan" and call &query with
my ($status,@results) = &query('view');
Then, I do this loop...


The problem that I am having is that the first option (using &query) which I thought was less optimized takes

Processing Time: 1 wallclock secs ( 0.60 usr + 0.01 sys = 0.61 CPU)

Where as the second option

Processing Time: 6 wallclock secs ( 5.59 usr + 0.01 sys = 5.60 CPU)

What is going on here??? I assume the convertion from array to hash is what is taking the longest, but how can I get rid of it?

(There are only 77 hits for Jan)

This is off the top of my head, so I don't know if it would work, but do you think you would be able to do it without a pass for each record for each button?

The procedure would be something like--


That way you do one &query (for the month) and do one &array_to_hash for each record. Your totals would be in
$count{'OPEN'}
$count{'CLOSED'}
$count{'FINISHED'}
$count{'ANSWERED'}

BTW, I get a little confused about when you need quotation marks and when you don't, but I don't think you need them in
if ($tmp{"Status"} eq "$button")



------------------
JPD

I think I have it even more efficient.


You could even do


At this point, you probably wouldn't see much difference in speed, but when you get a large database, it could make a difference.



------------------
JPD

Hi JPD,

I think we got it...

This is the super-dooper-optimized loop



The loop speed differences are these

Processing Time: 0 wallclock secs ( 0.15 usr + 0.01 sys = 0.16 CPU)

and

Processing Time: 1 wallclock secs ( 0.89 usr + 0.04 sys = 0.93 CPU)
using the &query on 85 hits

Now, I just have to change all my statistics code, joy...

Thanks
-JO