Re: slow query

Posted in 2006

Topics: Performance & Tuning, SQL Development & Query Writing

Floyd Wellershaus — Wed, 29 Nov 2006 06:59:05 -0800 (PST) — source: Usenet: comp.databases.informix

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. > > 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-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

↪ replying to Floyd Wellershaus

bozon — 29 Nov 2006 07:27:39 -0800 — source: Usenet: comp.databases.informix

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. > > > > 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-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > --0-1778847719-1164812345=:37610 > Content-Type: text/html; charset=ascii > Content-Transfer-Encoding: quoted-printable > X-Google-AttachSize: 3245 > > <html><head><style type="text/css"></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV></DIV> > <DIV><BR> </DIV> > <DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif"> > <DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif"> > <DIV>How many rows does it really return?<BR>> Estimated Cost: 24<BR>> Estimated # of Rows Returned: 1</DIV> > <DIV> </DIV> > <DIV><STRONG>ANSWER: 1</STRONG><BR><BR>How many rows does the select in the select clause return?<BR>>          (SELECT schl_branch FROM scprofil where scprofil.token = sid.scprofil_token)</DIV> > <DIV> </DIV> > <DIV><STRONG>ANSWER: 1</STRONG><BR><BR>How many rows are in each table?<BR>> FROM scprofil<BR>> FROM     degreedtl dtl,<BR>>          degreesid sid</DIV> > <DIV> </DIV> > <DIV><STRONG>ANSWER: ~33million in each</STRONG><BR><BR>How good are the indexes that are being used?</DIV> > <DIV> </DIV> > <DIV><STRONG>ANSWER: </STRONG><BR>Not sure I understand what you're asking. The indexes pass oncheck -cI without error. </DIV> > <DIV> </DIV> > <DIV>><BR>>   1) stevet.sid: INDEX PATH<BR>><BR>>         Filters: (stevet.sid.scprofil_token = 1383 AND stevet.sid.first_name_s = 'test' )<BR>><BR>>     (1) Index Keys: last_name_s birth_dt<BR>>         Lower Index Filter: stevet.sid.last_name_s = 'test'<BR>><BR>>   2) stevet.dtl: INDEX PATH<BR>><BR>>         Filters: (stevet.dtl.rec_status = 'A' AND stevet.dtl.rec_type = 'D' )<BR>><BR>>     (1) Index Keys: degreesid_token<BR>>         Lower Index Filter: stevet.sid.token = stevet.dtl.degreesid_token<BR>> NESTED LOOP JOIN<BR>><BR><BR>Are there any more joins that you can make?<BR>> WHERE    sid.token = dtl.degreesid_token<BR>> AND      sid.first_name_s = > 'test'<BR>> AND      sid.last_name_s = 'test'<BR>> AND      sid.scprofil_token IN((1383))<BR>> AND      dtl.rec_type = 'D'<BR>> AND      dtl.rec_status = 'A'<BR>><BR>><BR><BR>Why is this in double paranthesis?<BR>> AND      sid.scprofil_token IN((1383))</DIV> > <DIV> </DIV> > <DIV><STRONG>ANSWER:</STRONG> Don't know. Do you think that matters ?</DIV> > <DIV><BR><BR>_______________________________________________<BR>Informix-list mailing list<BR>Informix-list@iiug.org<BR><A href="http://www.iiug.org/mailman/listinfo/informix-list"; target=_blank>http://www.iiug.org/mailman/listinfo/informix-list</A></DIV></DIV><BR></DIV></div></body></html>; > --0-1778847719-1164812345=:37610--