RE: [GENERAL] Query time is too long for netscape

Without knowing the distribution of records in the transaction table it is
difficult to relate the schema to the actual data. Do you know what a
typical query will be like? i.e. How is a typical date range and
service_type query going to limit the number of rows returned? Will a one
month date range and a particular service type limit the return set to a
couple rows, a couple hundred, a couple of hundred thousand?

The first thing that caught my eye is that you are not limiting the query
results to an individual customer's ID so conceivably you could be returning
a large majority of half a million rows. Could this be the problem?

Phil Culberson
DAT Services

-----Original Message-----
From: Chairudin Sentosa Harjo [mailto:chai(at)prima(dot)net(dot)id]
Sent: Tuesday, January 25, 2000 1:12 AM
To: Mark Jewiss
Cc: pgsql-general
Subject: Re: [GENERAL] Query time is too long for netscape

Mark Jewiss wrote:
>
> Hello,
>
> On Tue, 25 Jan 2000, Chairudin Sentosa Harjo wrote:
>
> > The query looks like this:
> > select a.custnum, b.fullname, b.address, a.usages from
> > Detail a, Customer b
> > where a.custnum=b.custnum;
>
> Have you got any keys setup on these tables? That would help the query a
> lot.
>
> Mail out your table definitions (and the script) and then we can have a
> look.

I do have a lot of index combinations to help speed up the query,
however
it still takes too long. Here is the script and the table definitions.

SCRIPT
$sql = "select a.pin, b.first_name, b.last_name,
start_time, duration,
country_code, area_code, phone_number,
a.service_type, total_units
from tbs_billing_record a, ibs_subscriber b
where a.pin=b.pin
and date(start_time) between '$begindate' and '$enddate'
and a.service_type='$service_type'
order by $sortby1,$sortby2";

Pg::doQuery($conn,$sql,\(at)bilrec);

The values of $begindate, $enddate, $service_type, $sortby1, $sortby2
are given by user, through HTML form using CGI script.

TABLE DEFINITION
create sequence tbs_br_seq
increment 1
minvalue 0000000001
start 1;

create table tbs_billing_record
(
tbs_br_seq int DEFAULT nextval('tbs_br_seq') NOT NULL,
pin varchar(128) NOT NULL,
start_time datetime,
duration integer,
service_code varchar(2),
country_code varchar(3),
area_code varchar(3),
phone_number varchar(24),
service_type varchar(1),
total_units float,

);

create index tbs_br_pin_idx on tbs_billing_record (pin);
create index tbs_br_start_time_idx on tbs_billing_record (start_time);
create index tbs_br_duration_idx on tbs_billing_record (duration);
create index tbs_br_country_code_idx on tbs_billing_record
(country_code);
create index tbs_br_total_units_idx on tbs_billing_record (total_units);
create index tbs_br_pin_start_time_idx on tbs_billing_record (pin,
start_time);
create index tbs_br_pin_duration_idx on tbs_billing_record (pin,
duration);
create index tbs_br_pin_total_units_idx on tbs_billing_record (pin,
total_units);
create index tbs_br_start_time_duration_idx on tbs_billing_record
(start_time, duration);
create index tbs_br_tbsbrseq_idx on tbs_billing_record (tbs_br_seq);
create index tbs_br_pinstarttimeservicetype_idx on tbs_billing_record
(pin,start_time,service_type);
create index tbs_br_starttimeservicetype_idx on tbs_billing_record
(start_time,service_type);

create table ibs_subscriber
(
id integer,
last_name varchar(30),
first_name varchar(20),
mi_name varchar(1),
password varchar(20),
pin varchar(128),
);

create index ibs_subs_id_idx on ibs_subscriber (id);
create index ibs_subs_last_name_idx on ibs_subscriber (last_name);
create index ibs_subs_first_name_idx on ibs_subscriber (first_name);
create index ibs_subs_password_idx on ibs_subscriber (password);
create index ibs_subs_last_first_name_idx on ibs_subscriber (last_name,
first_name);
create index ibs_subs_id_password_idx on ibs_subscriber (id, password);
create index ibs_subs_pin_idx on ibs_subscriber (pin);
create index ibs_subs_pin_flname_idx on ibs_subscriber
(pin,first_name,last_name);

To print out the detail I use this (part of the long script):

foreach $record (@bilrec)
{
($pin,$first_name,$last_name,$start_time,$duration,
$country_code,$area_code,$phone_number,$service_type,
$total_units)=(at)$record;

$new_duration = ($duration/60);
($front,$back) = split(/\./,$new_duration);
$new_back = substr($back,0,2);
$new_duration = $front.'.'.$new_back;

$no = $no + 1;
$fullname=$first_name.' '.$last_name;
push(@rows,
td([$no,$pin,$fullname,$start_time,center($new_duration),
$country_code,$area_code,$phone_number,
$service_type,
$total_units]));
}

print table
({-border=>'', -cellspacing=>3, -cellpadding=>3},
TR(\(at)rows)

Regards,
Chai

************