slow query
Floyd Wellershaus posted an sqexplain plan, query and DDL for a slow join of degreedtl and degreesid with a scalar subquery on scprofil. bozon asked how many rows were returned and how good the indexes were. Art S. Kagel asked whether statistics were current and suggested folding the subquery into a join and adding an index on (degreesid_token, rec_type, rec_status). No outcome reported.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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/