All 10 messages in topic - view as tree
From: Floyd Wellershaus - view profile
Date: Wed, Nov 29 2006 1:55 pm
Email: Floyd Wellershaus
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.
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
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
CREATE TABLE degreesid
(
token serial not null,
dvsid_token integer,
dvsublog_token integer not null,
g_token integer not null,
scprofil_token integer not null,
stprofil_token integer,
ssn char(9),
student_id char(15),
first_name varchar(40),
first_name_s varchar(40),
middle_name varchar(40),
middle_name_s varchar(40),
last_name varchar(40) not null,
last_name_s varchar(40) not null,
name_suffix varchar(5),
prev_last_name char(40),
prev_last_name_s char(40),
prev_first_name varchar(40),
prev_first_name_s varchar(40),
birth_dt date,
source_flag char(1) not null,
rec_status char(1) not null,
operator_id varchar(9),
timestamp datetime year to second
) IN dv_sid EXTENT SIZE 1048572 NEXT SIZE 1048572;
CREATE UNIQUE INDEX degreesid_x01 ON degreesid(token);
CREATE INDEX degreesid_x02 ON degreesid(ssn) FILLFACTOR 70;
CREATE INDEX degreesid_x03 ON degreesid(last_name, birth_dt) FILLFACTOR 70;
CREATE INDEX degreesid_x04 ON degreesid(last_name_s, birth_dt) FILLFACTOR 70;
CREATE INDEX degreesid_x05 ON degreesid(stprofil_token) FILLFACTOR 70;
CREATE INDEX degreesid_x06 ON degreesid(dvsid_token);
{
# this index is used when the user wants to query degree for a specific
# school via sentry client. Therefore, first column should be scprofil_token.
#
# also used during degree verification.
}
CREATE INDEX degreesid_x07 ON degreesid(scprofil_token, prev_last_name_s, first_name_s, birth_dt) FILLFACTOR 70;
CREATE INDEX degreesid_x08 ON degreesid(student_id) FILLFACTOR 70;
{ TABLE "sentrycf".degreedtl row size = 199 number of columns = 45 index size = 27
}
create table "sentrycf".degreedtl
(
token serial not null constraint "sentrycf".n857093_1276465,
degreesid_token integer not null constraint "sentrycf".n857093_1276466,
dvsublog_token integer not null constraint "sentrycf".n857093_1276467,
g_token integer not null constraint "sentrycf".n857093_1276468,
degree_level_ind char(1),
ddd_degt_token integer,
ddd_scad_token integer,
ddd_jins_token integer,
award_dt date,
award_dt_mmyyyy datetime year to month,
major_1_token integer,
major_2_token integer,
major_3_token integer,
major_4_token integer,
minor_1_token integer,
minor_2_token integer,
minor_3_token integer,
minor_4_token integer,
major_opt_1_token integer,
major_opt_2_token integer,
major_con_1_token integer,
major_con_2_token integer,
major_con_3_token integer,
ncescip_major_1 varchar(6),
ncescip_major_2 varchar(6),
ncescip_major_3 varchar(6),
ncescip_major_4 varchar(6),
ncescip_minor_1 varchar(6),
ncescip_minor_2 varchar(6),
ncescip_minor_3 varchar(6),
ncescip_minor_4 varchar(6),
ddd_ahnr_token integer,
ddd_hnrp_token integer,
ddd_ohnr_token integer,
attend_from_dt date,
attend_from_mmyyyy datetime year to month,
attend_to_dt date,
attend_to_mmyyyy datetime year to month,
ferpa_block char(1),
schl_finance_block char(1),
schl_aka_token integer not null constraint "sentrycf".n857093_1276469,
rec_type char(1) not null ,
rec_status char(1) not null constraint "sentrycf".n857093_1276470,
operator_id varchar(9),
timestamp datetime year to second
) in dv_dtl extent size 1048572 next size 1048572 lock mode page;
revoke all on "sentrycf".degreedtl from "public";
create index "sentrycf".degreedtl_x03 on "sentrycf".degreedtl
(dvsublog_token) using btree in table ;
create unique index "sentrycf".degreedtl_x1 on "sentrycf".degreedtl
(token) using btree in table ;
create index "sentrycf".degreedtl_x2 on "sentrycf".degreedtl (degreesid_token)
using btree in table ;
========================
-<
Database Administrator
Unix Administrator
email: fwell...@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
Reply » Rate this post: Text for clearing space
From: Keith Simmons - view profile
Date: Wed, Nov 29 2006 2:35 pm
Email: "Keith Simmons"
Floyd
UPDATE STATISTICS HIGH for the the leading column of each index might help,
however the 'sid.scprofil_token IN((1383))' clause (or indeed any IN clause)
always seems (to me) to be a bit of an issue and will cause the optimiser to
try to use any other index rather than the one containing the target column.
I assume there are usually more than one value here (else why not use
equals), could you use token = ?? OR token = ?? OR .... I find this tends to
perform better.
Keith
On 29/11/06, Floyd Wellershaus
- Hide quoted text -
- Show quoted text -
> 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.
> 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
> 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
> CREATE TABLE degreesid
> (
> token serial not null,
> dvsid_token integer,
> dvsublog_token integer not null,
> g_token integer not null,
> scprofil_token integer not null,
> stprofil_token integer,
> ssn char(9),
> student_id char(15),
> first_name varchar(40),
> first_name_s varchar(40),
> middle_name varchar(40),
> middle_name_s varchar(40),
> last_name varchar(40) not null,
> last_name_s varchar(40) not null,
> name_suffix varchar(5),
> prev_last_name char(40),
> prev_last_name_s char(40),
> prev_first_name varchar(40),
> prev_first_name_s varchar(40),
> birth_dt date,
> source_flag char(1) not null,
> rec_status char(1) not null,
> operator_id varchar(9),
> timestamp datetime year to second
> ) IN dv_sid EXTENT SIZE 1048572 NEXT SIZE 1048572;
> CREATE UNIQUE INDEX degreesid_x01 ON degreesid(token);
> CREATE INDEX degreesid_x02 ON degreesid(ssn)
> FILLFACTOR 70;
> CREATE INDEX degreesid_x03 ON degreesid(last_name, birth_dt)
> FILLFACTOR 70;
> CREATE INDEX degreesid_x04 ON degreesid(last_name_s, birth_dt)
> FILLFACTOR 70;
> CREATE INDEX degreesid_x05 ON
> degreesid(stprofil_token) FILLFACTOR 70;
> CREATE INDEX degreesid_x06 ON degreesid(dvsid_token);
> {
> # this index is used when the user wants to query degree for a specific
> # school via sentry client. Therefore, first column should be
> scprofil_token.
> #
> # also used during degree verification.
> }
> CREATE INDEX degreesid_x07 ON degreesid(scprofil_token,
> prev_last_name_s, first_name_s, birth_dt) FILLFACTOR 70;
> CREATE INDEX degreesid_x08 ON degreesid(student_id)
> FILLFACTOR 70;
> { TABLE "sentrycf".degreedtl row size = 199 number of columns = 45 index
> size = 27
> }
> create table "sentrycf".degreedtl
> (
> token serial not null constraint "sentrycf".n857093_1276465,
> degreesid_token integer not null constraint
> "sentrycf".n857093_1276466,
> dvsublog_token integer not null constraint "sentrycf".n857093_1276467,
> g_token integer not null constraint "sentrycf".n857093_1276468,
> degree_level_ind char(1),
> ddd_degt_token integer,
> ddd_scad_token integer,
> ddd_jins_token integer,
> award_dt date,
> award_dt_mmyyyy datetime year to month,
> major_1_token integer,
> major_2_token integer,
> major_3_token integer,
> major_4_token integer,
> minor_1_token integer,
> minor_2_token integer,
> minor_3_token integer,
> minor_4_token integer,
> major_opt_1_token integer,
> major_opt_2_token integer,
> major_con_1_token integer,
> major_con_2_token integer,
> major_con_3_token integer,
> ncescip_major_1 varchar(6),
> ncescip_major_2 varchar(6),
> ncescip_major_3 varchar(6),
> ncescip_major_4 varchar(6),
> ncescip_minor_1 varchar(6),
> ncescip_minor_2 varchar(6),
> ncescip_minor_3 varchar(6),
> ncescip_minor_4 varchar(6),
> ddd_ahnr_token integer,
> ddd_hnrp_token integer,
> ddd_ohnr_token integer,
> attend_from_dt date,
> attend_from_mmyyyy datetime year to month,
> attend_to_dt date,
> attend_to_mmyyyy datetime year to month,
> ferpa_block char(1),
> schl_finance_block char(1),
> schl_aka_token integer not null constraint "sentrycf".n857093_1276469,
> rec_type char(1) not null ,
> rec_status char(1) not null constraint "sentrycf".n857093_1276470,
> operator_id varchar(9),
> timestamp datetime year to second
> ) in dv_dtl extent size 1048572 next size 1048572 lock mode page;
> revoke all on "sentrycf".degreedtl from "public";
> create index "sentrycf".degreedtl_x03 on "sentrycf".degreedtl
> (dvsublog_token) using btree in table ;
> create unique index "sentrycf".degreedtl_x1 on "sentrycf".degreedtl
> (token) using btree in table ;
> create index "sentrycf".degreedtl_x2 on "sentrycf".degreedtl
> (degreesid_token)
> using btree in table ;
> ========================
> -<
> Database Administrator
> Unix Administrator
> email: fwell...@yahoo.com
> Home: 703-430-0805
> Cell: 703-477-6045
> ========================
> http://www.one.org/
> _______________________________________________
> Informix-list mailing list
> Informix-l...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
Reply » Rate this post: Text for clearing space
From: bozon - view profile
Date: Wed, Nov 29 2006 4:32 pm
Email: "bozon"
How many rows does it really return?
> Estimated Cost: 24
> Estimated # of Rows Returned: 1
How many rows does the select in the select clause return?
> (SELECT schl_branch FROM scprofil where scprofil.token = sid.scprofil_token)
How many rows are in each table?
> FROM scprofil
> FROM degreedtl dtl,
> degreesid sid
How good are the indexes that are being used?
- Hide quoted text -
- Show quoted text -
> 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
Are there any more joins that you can make?
> 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'
Why is this in double paranthesis?
- Hide quoted text -
- Show quoted text -
> AND sid.scprofil_token IN((1383))
Reply » Rate this post: Text for clearing space
From: Floyd Wellershaus - view profile
Date: Wed, Nov 29 2006 4:59 pm
Email: Floyd Wellershaus
How many rows does it really return?
> Estimated Cost: 24
> Estimated # of Rows Returned: 1
ANSWER: 1
How many rows does the select in the select clause return?
> (SELECT schl_branch FROM scprofil where scprofil.token = sid.scprofil_token)
ANSWER: 1
How many rows are in each table?
> FROM scprofil
> FROM degreedtl dtl,
> degreesid sid
ANSWER: ~33million in each
How good are the indexes that are being used?
ANSWER:
Not sure I understand what you're asking. The indexes pass oncheck -cI without error.
- Hide quoted text -
- Show quoted text -
> 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
Are there any more joins that you can make?
> 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'
Why is this in double paranthesis?
> AND sid.scprofil_token IN((1383))
ANSWER: Don't know. Do you think that matters ?
_______________________________________________
Informix-list mailing list
Informix-l...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Reply » Rate this post: Text for clearing space
From: bozon - view profile
Date: Wed, Nov 29 2006 5:27 pm
Email: "bozon"
Floyd Wellershaus wrote:
> How many rows does it really return?
> > Estimated Cost: 24
> > Estimated # of Rows Returned: 1
> ANSWER: 1
> How many rows does the select in the select clause return?
> > (SELECT schl_branch FROM scprofil where scprofil.token = sid.scprofil_token)
> ANSWER: 1
Does this usually only return one row? If so then why this
construction.
> How good are the indexes that are being used?
> ANSWER:
> Not sure I understand what you're asking. The indexes pass oncheck -cI without error.
given your input values how many rows does it have to work through.
- Hide quoted text -
- Show quoted text -
> > 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
> Are there any more joins that you can make?
> > 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'
> Why is this in double paranthesis?
> > AND sid.scprofil_token IN((1383))
> ANSWER: Don't know. Do you think that matters ?
Reply » Rate this post: Text for clearing space
From: Art S. Kagel - view profile
Date: Wed, Nov 29 2006 6:58 pm
Email: "Art S. Kagel"
Floyd Wellershaus 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.
> Thanks in advance for any insight.
Only one question - are the stats up to standard?
My suggestions:
1) Fold that subquery on scprofil into a straight join on sid.scprofil_token.
2) Add an index to desgreedtl on (degreesid_token, rec_type, rec_status) and
update stats with this key.
Art S. Kagel
- Hide quoted text -
- Show quoted text -
> 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
> 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
> CREATE TABLE degreesid
> (
> token serial not null,
> dvsid_token integer,
> dvsublog_token integer not null,
> g_token integer not null,
> scprofil_token integer not null,
> stprofil_token integer,
> ssn char(9),
> student_id char(15),
> first_name varchar(40),
> first_name_s varchar(40),
> middle_name varchar(40),
> middle_name_s varchar(40),
> last_name varchar(40) not null,
> last_name_s varchar(40) not null,
> name_suffix varchar(5),
> prev_last_name char(40),
> prev_last_name_s char(40),
> prev_first_name varchar(40),
> prev_first_name_s varchar(40),
> birth_dt date,
> source_flag char(1) not null,
> rec_status char(1) not null,
> operator_id varchar(9),
> timestamp datetime year to second
> ) IN dv_sid EXTENT SIZE 1048572 NEXT SIZE 1048572;
> CREATE UNIQUE INDEX degreesid_x01 ON degreesid(token);
> CREATE INDEX degreesid_x02 ON degreesid(ssn)
> FILLFACTOR 70;
> CREATE INDEX degreesid_x03 ON degreesid(last_name, birth_dt)
> FILLFACTOR 70;
> CREATE INDEX degreesid_x04 ON degreesid(last_name_s, birth_dt)
> FILLFACTOR 70;
> CREATE INDEX degreesid_x05 ON
> degreesid(stprofil_token) FILLFACTOR 70;
> CREATE INDEX degreesid_x06 ON degreesid(dvsid_token);
> {
> # this index is used when the user wants to query degree for a specific
> # school via sentry client. Therefore, first column should be
> scprofil_token.
> #
> # also used during degree verification.
> }
> CREATE INDEX degreesid_x07 ON degreesid(scprofil_token,
> prev_last_name_s, first_name_s, birth_dt) FILLFACTOR 70;
> CREATE INDEX degreesid_x08 ON degreesid(student_id)
> FILLFACTOR 70;
> { TABLE "sentrycf".degreedtl row size = 199 number of columns = 45 index
> size = 27
> }
> create table "sentrycf".degreedtl
> (
> token serial not null constraint "sentrycf".n857093_1276465,
> degreesid_token integer not null constraint "sentrycf".n857093_1276466,
> dvsublog_token integer not null constraint "sentrycf".n857093_1276467,
> g_token integer not null constraint "sentrycf".n857093_1276468,
> degree_level_ind char(1),
> ddd_degt_token integer,
> ddd_scad_token integer,
> ddd_jins_token integer,
> award_dt date,
> award_dt_mmyyyy datetime year to month,
> major_1_token integer,
> major_2_token integer,
> major_3_token integer,
> major_4_token integer,
> minor_1_token integer,
> minor_2_token integer,
> minor_3_token integer,
> minor_4_token integer,
> major_opt_1_token integer,
> major_opt_2_token integer,
> major_con_1_token integer,
> major_con_2_token integer,
> major_con_3_token integer,
> ncescip_major_1 varchar(6),
> ncescip_major_2 varchar(6),
> ncescip_major_3 varchar(6),
> ncescip_major_4 varchar(6),
> ncescip_minor_1 varchar(6),
> ncescip_minor_2 varchar(6),
> ncescip_minor_3 varchar(6),
> ncescip_minor_4 varchar(6),
> ddd_ahnr_token integer,
> ddd_hnrp_token integer,
> ddd_ohnr_token integer,
> attend_from_dt date,
> attend_from_mmyyyy datetime year to month,
> attend_to_dt date,
> attend_to_mmyyyy datetime year to month,
> ferpa_block char(1),
> schl_finance_block char(1),
> schl_aka_token integer not null constraint "sentrycf".n857093_1276469,
> rec_type char(1) not null ,
> rec_status char(1) not null constraint "sentrycf".n857093_1276470,
> operator_id varchar(9),
> timestamp datetime year to second
> ) in dv_dtl extent size 1048572 next size 1048572 lock mode page;
> revoke all on "sentrycf".degreedtl from "public";
> create index "sentrycf".degreedtl_x03 on "sentrycf".degreedtl
> (dvsublog_token) using btree in table ;
> create unique index "sentrycf".degreedtl_x1 on "sentrycf".degreedtl
> (token) using btree in table ;
> create index "sentrycf".degreedtl_x2 on "sentrycf".degreedtl
> (degreesid_token)
> using btree in table ;
> ========================
> -<
> Database Administrator
> Unix Administrator
> email: fwell...@yahoo.com
> Home: 703-430-0805
> Cell: 703-477-6045
> ========================
> http://www.one.org/
Reply » Rate this post: Text for clearing space
From: Art S. Kagel - view profile
Date: Wed, Nov 29 2006 6:58 pm
Email: "Art S. Kagel"
Floyd Wellershaus 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.
> Thanks in advance for any insight.
Only one question - are the stats up to standard?
My suggestions:
1) Fold that subquery on scprofil into a straight join on sid.scprofil_token.
2) Add an index to desgreedtl on (degreesid_token, rec_type, rec_status) and
update stats with this key.
Art S. Kagel
- Hide quoted text -
- Show quoted text -
> 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
> 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
> CREATE TABLE degreesid
> (
> token serial not null,
> dvsid_token integer,
> dvsublog_token integer not null,
> g_token integer not null,
> scprofil_token integer not null,
> stprofil_token integer,
> ssn char(9),
> student_id char(15),
> first_name varchar(40),
> first_name_s varchar(40),
> middle_name varchar(40),
> middle_name_s varchar(40),
> last_name varchar(40) not null,
> last_name_s varchar(40) not null,
> name_suffix varchar(5),
> prev_last_name char(40),
> prev_last_name_s char(40),
> prev_first_name varchar(40),
> prev_first_name_s varchar(40),
> birth_dt date,
> source_flag char(1) not null,
> rec_status char(1) not null,
> operator_id varchar(9),
> timestamp datetime year to second
> ) IN dv_sid EXTENT SIZE 1048572 NEXT SIZE 1048572;
> CREATE UNIQUE INDEX degreesid_x01 ON degreesid(token);
> CREATE INDEX degreesid_x02 ON degreesid(ssn)
> FILLFACTOR 70;
> CREATE INDEX degreesid_x03 ON degreesid(last_name, birth_dt)
> FILLFACTOR 70;
> CREATE INDEX degreesid_x04 ON degreesid(last_name_s, birth_dt)
> FILLFACTOR 70;
> CREATE INDEX degreesid_x05 ON
> degreesid(stprofil_token) FILLFACTOR 70;
> CREATE INDEX degreesid_x06 ON degreesid(dvsid_token);
> {
> # this index is used when the user wants to query degree for a specific
> # school via sentry client. Therefore, first column should be
> scprofil_token.
> #
> # also used during degree verification.
> }
> CREATE INDEX degreesid_x07 ON degreesid(scprofil_token,
> prev_last_name_s, first_name_s, birth_dt) FILLFACTOR 70;
> CREATE INDEX degreesid_x08 ON degreesid(student_id)
> FILLFACTOR 70;
> { TABLE "sentrycf".degreedtl row size = 199 number of columns = 45 index
> size = 27
> }
> create table "sentrycf".degreedtl
> (
> token serial not null constraint "sentrycf".n857093_1276465,
> degreesid_token integer not null constraint "sentrycf".n857093_1276466,
> dvsublog_token integer not null constraint "sentrycf".n857093_1276467,
> g_token integer not null constraint "sentrycf".n857093_1276468,
> degree_level_ind char(1),
> ddd_degt_token integer,
> ddd_scad_token integer,
> ddd_jins_token integer,
> award_dt date,
> award_dt_mmyyyy datetime year to month,
> major_1_token integer,
> major_2_token integer,
> major_3_token integer,
> major_4_token integer,
> minor_1_token integer,
> minor_2_token integer,
> minor_3_token integer,
> minor_4_token integer,
> major_opt_1_token integer,
> major_opt_2_token integer,
> major_con_1_token integer,
> major_con_2_token integer,
> major_con_3_token integer,
> ncescip_major_1 varchar(6),
> ncescip_major_2 varchar(6),
> ncescip_major_3 varchar(6),
> ncescip_major_4 varchar(6),
> ncescip_minor_1 varchar(6),
> ncescip_minor_2 varchar(6),
> ncescip_minor_3 varchar(6),
> ncescip_minor_4 varchar(6),
> ddd_ahnr_token integer,
> ddd_hnrp_token integer,
> ddd_ohnr_token integer,
> attend_from_dt date,
> attend_from_mmyyyy datetime year to month,
> attend_to_dt date,
> attend_to_mmyyyy datetime year to month,
> ferpa_block char(1),
> schl_finance_block char(1),
> schl_aka_token integer not null constraint "sentrycf".n857093_1276469,
> rec_type char(1) not null ,
> rec_status char(1) not null constraint "sentrycf".n857093_1276470,
> operator_id varchar(9),
> timestamp datetime year to second
> ) in dv_dtl extent size 1048572 next size 1048572 lock mode page;
> revoke all on "sentrycf".degreedtl from "public";
> create index "sentrycf".degreedtl_x03 on "sentrycf".degreedtl
> (dvsublog_token) using btree in table ;
> create unique index "sentrycf".degreedtl_x1 on "sentrycf".degreedtl
> (token) using btree in table ;
> create index "sentrycf".degreedtl_x2 on "sentrycf".degreedtl
> (degreesid_token)
> using btree in table ;
> ========================
> -<
> Database Administrator
> Unix Administrator
> email: fwell...@yahoo.com
> Home: 703-430-0805
> Cell: 703-477-6045
> ========================
> http://www.one.org/
Reply » Rate this post: Text for clearing space
From: John Carlson - view profile
Date: Fri, Dec 1 2006 6:13 am
Email: John Carlson
On Wed, 29 Nov 2006 03:55:19 -0800 (PST), Floyd Wellershaus
>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?
- Hide quoted text -
- Show quoted text -
>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
Reply » Rate this post: Text for clearing space
From: Floyd Wellershaus - view profile
Date: Fri, Dec 1 2006 2:04 pm
Email: Floyd Wellershaus
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 ;
========================
-<
Database Administrator
Unix Administrator
email: fwell...@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
- Hide quoted text -
- Show quoted text -
----- Original Message ----
From: John Carlson
To: informix-l...@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
>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-l...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Reply » Rate this post: Text for clearing space
From: bozon - view profile
Date: Sat, Dec 2 2006 12:27 am
Email: "bozon"
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.
- Hide quoted text -
- Show quoted text -
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 ;
> ========================
> -<
> Database Administrator
> Unix Administrator
> email: fwell...@yahoo.com
> Home: 703-430-0805
> Cell: 703-477-6045
> ========================
> http://www.one.org/
> ----- Original Message ----
> From: John Carlson
> To: informix-l...@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
>
> >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.
...
read more »
Reply » Rate this post: Text for clearing space
End of messages
No comments:
Post a Comment