I have been trying to track down why my DBMan has slowed to a crawl and think I have found it. I use a date field to sort on and the database has gotten to the point of generating server timeouts it's so slow. By accident I set it to sort on a different field and bang 400% or more faster.

Database details:
There are no large text fields in the database.
9 fields
1496 records
210KB total size

%db_def = (
ID => [0, 'numer', 5, 8, 1, '', ''],
Headline => [1, 'alpha', 50, 255, 1, '', ''],
URL => [2, 'alpha', 50, 255, 1, '', '^http://'],
Type => [3, 'alpha', 20, 20, 0, '', ''],
Source => [4, 'alpha', 25, 25, 0, '', ''],
PostTime => [5, 'date', 20, 20, 1, &get_date, ''],
PostedBy => [6, 'alpha', 20, 35, 1, 'Larry', ''],
Verified => [7, 'alpha', 0, 3, 1, 'Yes', 'Yes|No'],
UserID => [8, 'alpha', -2, 15, 0, '', '']
);

I am thinking that adding a new field that holds a unix date might help? This would eliminate the need to convert the date field data to unix format on each record read.

I am assuming this is where the bottle neck is? I don't want to go changing 3000 records in two databases to find out I'm going in the wrong direction.

JPDeni if you read this you may remember helping me with the custom DATE/TIME routine. I really don't think the custom date routine is causing problems but here it is just in case.

It is just date and time combined into one. We had to do this to get records to index by date and time.

Format = May-07-1999 07:43:17

sub get_date {

my ($time) = @_;
($time) | | ($time = time());
my ($sec, $min, $hour, $day, $mon, $year, $dweek, $dyear, $daylight) = localtime($time);
my (@months) = qw!Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec!;
($day < 10) and ($day = "0$day");
$year = $year + 1900;
($sec < 10) and ($sec = "0$sec");
($min < 10) and ($min = "0$min");
($hour < 10) and ($hour = "0$hour");

return "$months[$mon]-$day-$year $hour:$min:$sec";
}

sub date_to_unix {

my ($date_and_time) = $_[0];
my (%months) = ("Jan" => 0, "Feb" => 1, "Mar" => 2, "Apr" => 3, "May" => 4, "Jun" => 5,
"Jul" => 6, "Aug" => 7, "Sep" => 8, "Oct" => 9, "Nov" => 10,"Dec" => 11);
my ($date, $time) = split(/ /, $_[0]);
my ($mon, $day, $year) = split(/-/, $date);
my ($hour, $min, $sec) = split(/:/, $time);
unless ($mon and $day and $year) { return undef; }
unless (defined($months{$mon})) { return undef; }
use Time::Local;
eval {
$day = int($day); $year = int($year) - 1900;
$time = timelocal($sec,$min,$hour,$day,$months{$mon},$year);
};

if ($@) { return undef;}
return ($time);
}

I don't know exactly what the best way would be to go about adding a new Unix format date field to the existing databases. I am hoping I can add a new forum field that would use the current date field data. Then all I would have to do is use DBMan modify record on each and record to update the new field. Note: I do know how to add the empty field to the db_def I just need help filling it with the proper data. And help with how to pass the Unix formated data to the sort routine.

My grand plan is to move to MySQL but I can't afford to for at least another month and my databases are growing by 40 records a day.

I am praying that someone can help me, any help will be greatly appreciated. I need to fix five databases this weekend.

Cheers!



------------------
Larry "NgtCrwlr" Mingus
www.makeitsimple.com

Sorry I wasn't around to help you before the weekend. I was wandering the streets of Monterey, CA and looking at jellyfish at the aquarium.

It might be a good idea to try adding a unix date field to your database. That should make things faster.


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

No problemo JPDeni, everyone needs some time away from these computers and you certainly deserve your time. I hope that you had a wonderfull time.

Speaking of aquariums we just setup a new 55gal home for Fredett our 7 inch Sunfish. We raised her from 3/8 of an inch.

Over the weekend I did a quick fix of changing the sort to sort on ID, that worked fine as a temp fix.

I am hoping I can add a new forum field or subroutine that would use the current date field data and convert the data to Unix date format for the new field.

Then I could use DBMan modify record on each record to automaticly update the new Unix date field based on the existing date field.

I need the new unix date field to be calculated from the exitsing date field. That way I can manually enter older dates and the Unix date would match the manual entry.

Note: I do know how to add the empty field to the db_def and the existing databases. I just need help filling it with the proper data.

I would think this would have to be a subroutine added to the add record and modify record subs. This is where I need help. Here is my guess:

$rec{'UnixDate'} = &date_to_unix($rec{'PostTime'})

<INPUT TYPE=hidden name="UnixDate" VALUE="$rec{'UnixDate'}" SIZE="10" MAXLENGTH="10"></TD></TR>

I know you are probably busy as a bee with the backlog, if you can just let me know if I am headed in the right direction.

You don't know how much you were missed...

Looks good to me. That's what I'd do.

JPD

PS. Thanks!

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