Re: slow query
Ah, but you are so right.
Here it is:
{ TABLE "sentrycf".scprofil row size = 909 number of columns = 56 index size = 83
}
create table "sentrycf".scprofil
(
token integer not null ,
name char(50) not null ,
short_name char(15),
schl_code char(6) not null ,
schl_branch char(2) not null ,
type char(1) not null ,
college_type char(1),
tin_school_flag char(1)
default 'N' not null ,
alt_ssn_range varchar(50)
default null,
sch_city varchar(20),
state char(2),
member_status char(1) not null ,
closed_flag char(1)
default 'N' not null ,
member_start_dt date,
data_since_dt date,
stdt_population integer,
num_enrollees integer not null ,
software_used char(15) not null ,
tt_participant char(1) not null ,
tt_mbprofil_token integer,
schl_block_rpt char(1) not null ,
oedo_block_rpt char(1) not null ,
block_email char(1) not null ,
dbi_programmed char(1) not null ,
dbi_percent_allow integer,
priority_flag char(1) not null ,
perkins_flat_rate char(1) not null ,
contract_type varchar(15),
es_paid_through_dt date,
es_ntf_tape_spec varchar(150),
commercial_verify char(1) not null ,
enrollstat_release char(1),
address_release char(1),
sss_participant char(1)
default 'N' not null ,
sss_enable_to_only char(1)
default 'N' not null ,
sss_ec_agd char(1)
default 'N' not null ,
sss_ec_notes_token integer,
to_participant char(1)
default 'N' not null ,
to_active_dt date,
payment_method char(10),
cora_participant char(1)
default 'N' not null ,
cora_data_prepop char(1)
default 'S' not null ,
ev_trans_fee money(16,2)
default 0.00 not null ,
ev_block_public char(1)
default 'N' not null ,
dv_participant char(1),
dv_scprofil_token integer,
dv_gen_grad_file char(1)
default 'N' not null ,
dv_data_since_dt date,
dv_trans_fee money(16,2)
default 0.00 not null ,
dv_active_dt date,
dv_refers_requests char(1) not null ,
dv_block_autoemail char(1)
default 'N' not null ,
cv_contact_phrase varchar(200),
standing_instr varchar(255),
operator_id char(9) not null ,
timestamp datetime year to second not null
) in schl_dat extent size 1536 next size 256 lock mode page;
revoke all on "sentrycf".scprofil from "public";
create unique index "sentrycf".scprofil_x01 on "sentrycf".scprofil
(token) using btree in table ;
create unique index "sentrycf".scprofil_x02 on "sentrycf".scprofil
(schl_code,schl_branch) using btree in table ;
create index "sentrycf".scprofil_x03 on "sentrycf".scprofil (name)
using btree in table ;
create index "sentrycf".scprofil_x04 on "sentrycf".scprofil (member_status)
using btree in table ;
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
----- Original Message ----
From: John Carlson <jwcarlson1@yahoo.com.invalid>
To: informix-list@iiug.org
Sent: Thursday, November 30, 2006 11:13:41 PM
Subject: Re: slow query
On Wed, 29 Nov 2006 03:55:19 -0800 (PST), Floyd Wellershaus
<fwellers@yahoo.com> wrote:
>Is there anything glaring that I'm missing here ? The below sqexplain output describes a query that is running slow.
>I've attached the ddl for the tables involved also.
>
Ah, but you forgot one . .. . . scprofil.
>Thanks in advance for any insight.
>
>SELECT sid.ssn,
> sid.student_id,
> sid.first_name_s,
> sid.middle_name_s,
> sid.last_name_s,
> sid.name_suffix,
> sid.prev_last_name_s,
> sid.prev_first_name_s,
> sid.birth_dt,
> (SELECT schl_branch FROM scprofil where scprofil.token = sid.scprofil_token) branch, dtl.token
How fast does the query run without the SELECT listed above? I've
seen where the explain plan doesn't include this query as part of the
explain plan. Is there an index on scprofil.token? Can you verify
that the index (if any) is being used?
>FROM degreedtl dtl,
> degreesid sid
>WHERE sid.token = dtl.degreesid_token
>AND sid.first_name_s = 'test'
>AND sid.last_name_s = 'test'
>AND sid.scprofil_token IN((1383))
>AND dtl.rec_type = 'D'
>AND dtl.rec_status = 'A'
>
>
>Estimated Cost: 24
>Estimated # of Rows Returned: 1
>
> 1) stevet.sid: INDEX PATH
>
> Filters: (stevet.sid.scprofil_token = 1383 AND stevet.sid.first_name_s = 'test' )
>
> (1) Index Keys: last_name_s birth_dt
> Lower Index Filter: stevet.sid.last_name_s = 'test'
>
> 2) stevet.dtl: INDEX PATH
>
> Filters: (stevet.dtl.rec_status = 'A' AND stevet.dtl.rec_type = 'D' )
>
> (1) Index Keys: degreesid_token
> Lower Index Filter: stevet.sid.token = stevet.dtl.degreesid_token
>NESTED LOOP JOIN
>
See there, table scprofil isn't listed here. Explain plans don't
include any sub-selects within the SELECT columns.
JWC
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
I was also suspicious of the select statement in the select list. Yes,
pull that out of the query run the whole query without that clause and
look at the results.
Floyd Wellershaus wrote:
> Ah, but you are so right.
> Here it is:
> { TABLE "sentrycf".scprofil row size = 909 number of columns = 56 index size = 83
> }
> create table "sentrycf".scprofil
> (
> token integer not null ,
> name char(50) not null ,
> short_name char(15),
> schl_code char(6) not null ,
> schl_branch char(2) not null ,
> type char(1) not null ,
> college_type char(1),
> tin_school_flag char(1)
> default 'N' not null ,
> alt_ssn_range varchar(50)
> default null,
> sch_city varchar(20),
> state char(2),
> member_status char(1) not null ,
> closed_flag char(1)
> default 'N' not null ,
> member_start_dt date,
> data_since_dt date,
> stdt_population integer,
> num_enrollees integer not null ,
> software_used char(15) not null ,
> tt_participant char(1) not null ,
> tt_mbprofil_token integer,
> schl_block_rpt char(1) not null ,
> oedo_block_rpt char(1) not null ,
> block_email char(1) not null ,
> dbi_programmed char(1) not null ,
> dbi_percent_allow integer,
> priority_flag char(1) not null ,
> perkins_flat_rate char(1) not null ,
> contract_type varchar(15),
> es_paid_through_dt date,
> es_ntf_tape_spec varchar(150),
> commercial_verify char(1) not null ,
> enrollstat_release char(1),
> address_release char(1),
> sss_participant char(1)
> default 'N' not null ,
> sss_enable_to_only char(1)
> default 'N' not null ,
> sss_ec_agd char(1)
> default 'N' not null ,
> sss_ec_notes_token integer,
> to_participant char(1)
> default 'N' not null ,
> to_active_dt date,
> payment_method char(10),
> cora_participant char(1)
> default 'N' not null ,
> cora_data_prepop char(1)
> default 'S' not null ,
> ev_trans_fee money(16,2)
> default 0.00 not null ,
> ev_block_public char(1)
> default 'N' not null ,
> dv_participant char(1),
> dv_scprofil_token integer,
> dv_gen_grad_file char(1)
> default 'N' not null ,
> dv_data_since_dt date,
> dv_trans_fee money(16,2)
> default 0.00 not null ,
> dv_active_dt date,
> dv_refers_requests char(1) not null ,
> dv_block_autoemail char(1)
> default 'N' not null ,
> cv_contact_phrase varchar(200),
> standing_instr varchar(255),
> operator_id char(9) not null ,
> timestamp datetime year to second not null
> ) in schl_dat extent size 1536 next size 256 lock mode page;
> revoke all on "sentrycf".scprofil from "public";>
> create unique index "sentrycf".scprofil_x01 on "sentrycf".scprofil
> (token) using btree in table ;
> create unique index "sentrycf".scprofil_x02 on "sentrycf".scprofil
> (schl_code,schl_branch) using btree in table ;
> create index "sentrycf".scprofil_x03 on "sentrycf".scprofil (name)
> using btree in table ;
> create index "sentrycf".scprofil_x04 on "sentrycf".scprofil (member_status)
> using btree in table ;
>
>
>
>
>
>
>
>
>
>
> ========================
> -<<Floyd Wellershaus>>-
> Database Administrator
> Unix Administrator
>
>
>
> email: fwellers@yahoo.com
>
>
> Home: 703-430-0805
>
>
> Cell: 703-477-6045
> ========================
>
>
> http://www.one.org/
>
>
>
> ----- Original Message ----
> From: John Carlson <jwcarlson1@yahoo.com.invalid>
> To: informix-list@iiug.org
> Sent: Thursday, November 30, 2006 11:13:41 PM
> Subject: Re: slow query
>
>
> On Wed, 29 Nov 2006 03:55:19 -0800 (PST), Floyd Wellershaus
> <fwellers@yahoo.com> wrote:
>
> >Is there anything glaring that I'm missing here ? The below sqexplain output describes a query that is running slow.
> >I've attached the ddl for the tables involved also.
> >
>
> Ah, but you forgot one . .. . . scprofil.
>
>
> >Thanks in advance for any insight.
> >
> >SELECT sid.ssn,
> > sid.student_id,
> > sid.first_name_s,
> > sid.middle_name_s,
> > sid.last_name_s,
> > sid.name_suffix,
> > sid.prev_last_name_s,
> > sid.prev_first_name_s,
> > sid.birth_dt,
> > (SELECT schl_branch FROM scprofil where scprofil.token = sid.scprofil_token) branch, dtl.token>
> How fast does the query run without the SELECT listed above? I've
> seen where the explain plan doesn't include this query as part of the
> explain plan. Is there an index on scprofil.token? Can you verify
> that the index (if any) is being used?
>
> >FROM degreedtl dtl,
> > degreesid sid
> >WHERE sid.token = dtl.degreesid_token
> >AND sid.first_name_s = 'test'
> >AND sid.last_name_s = 'test'
> >AND sid.scprofil_token IN((1383))
> >AND dtl.rec_type = 'D'
> >AND dtl.rec_status = 'A'
> >
> >
> >Estimated Cost: 24
> >Estimated # of Rows Returned: 1
> >
> > 1) stevet.sid: INDEX PATH
> >
> > Filters: (stevet.sid.scprofil_token = 1383 AND stevet.sid.first_name_s = 'test' )
> >
> > (1) Index Keys: last_name_s birth_dt
> > Lower Index Filter: stevet.sid.last_name_s = 'test'
> >
> > 2) stevet.dtl: INDEX PATH
> >
> > Filters: (stevet.dtl.rec_status = 'A' AND stevet.dtl.rec_type = 'D' )
> >
> > (1) Index Keys: degreesid_token
> > Lower Index Filter: stevet.sid.token = stevet.dtl.degreesid_token
> >NESTED LOOP JOIN
> >
>
> See there, table scprofil isn't listed here. Explain plans don't
> include any sub-selects within the SELECT columns.
>
> JWC
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
> --0-1102247796-1164974653=:37701
> Content-Type: text/html; charset=ascii
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 9671
>
> <html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV></DIV>
> <DIV>Ah, but you are so right. </DIV>
> <DIV>Here it is:</DIV>
> <DIV>{ TABLE "sentrycf".scprofil row size = 909 number of columns = 56 index size = 83<BR> }<BR>create table "sentrycf".scprofil<BR> (<BR> token integer not null ,<BR> name char(50) not null ,<BR> short_name char(15),<BR> schl_code char(6) not null ,<BR> schl_branch char(2) not null ,<BR>