slow query
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 ;
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
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? > > 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))
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
> 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 ;
>
> ========================
> -<<Floyd Wellershaus>>-
> Database Administrator
> Unix Administrator
>
>
> email: fwellers@yahoo.com <mailto:fwellers@yahoo.com>
>
> Home: 703-430-0805
>
> Cell: 703-477-6045
> ========================
>
> http://www.one.org/
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
> 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 ;
>
> ========================
> -<<Floyd Wellershaus>>-
> Database Administrator
> Unix Administrator
>
>
> email: fwellers@yahoo.com <mailto:fwellers@yahoo.com>
>
> Home: 703-430-0805
>
> Cell: 703-477-6045
> ========================
>
> http://www.one.org/