You should describe more details of your application to get some reliable answers, imho... Is it CGI or pure mod_perl? Apache 1 or 2?

It is web application running on mod_perl 1 with apache 1.3. But the classes that implements business objects basically don't care if it runs under mod_perl or from command line. They only need the database with a certain schema.

How many rows exist in db tables and how complex queries are?

There could be more than a million record in the cache table. The operation on the table is basically like this (in perl-like pseudocode):

sub search
{
   my ($arrival_date, $departure_date, $number_of_guests) = @_;
   
   validate_search_cache($arrival_date, $departure_date);
   
   my @search_results = ... do SELECT query to get a list of available
+ room-package combination between $arrival_date and $departure date f
+or $number_of_guests ...

   return @search_results;
}

sub validate_search_cache
{
   my ($arrival_date, $departure_date) = @_;

   my @validated_dates = ... do SELECT query that returns a list of da
+tes between $arrival_date and $departure_date having valid combinatio
+ns of room-package ...

   my @invalid_dates = ... look for dates between $arrival_date and $d
+eparture_date that doesn't exists in @validated_dates ...

   foreach (@invalid_dates) {
      initialize_search_cache($_);
   }
}

my @field_list = (
   { 
      name => 'valid_price', 
      op => sub { ... computation to check if price valid on   
                  a certain date ... },
   },
   {
      name => 'available_room_count',
      op => sub { ... calculcation to get the available room 
                  count on a certain date ... },
   },
   ...
);

sub initialize_search_cache
{
   my ($date) = @_;

   my @keys;
   my @values;
   foreach (@field_list) {
      push @keys, $_->{name};
      push @values, $_->{op}->($date);
   }

   my $sth = $dbh->prepare("INSERT INTO (".(join ',', @keys).") VALUES
+ (".(join ',', map "?", @values).")";
   $sth->execute(@values);
}
[download]

I hope that explains how the code works. The queries are mostly simple, except when doing the search for availability.

badaiaqrandista

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.