Re: Optimizing SQL question

Thanks for the suggestions everyone. I'll give them a try and let you know what I find out.

Dean Eshleman

On 2/28/2012 10:44 PM, Luis Rodriguez wrote:

Dean,

Besides Charles and Eric's excellent suggestions and comments, I wonder
what would happen if you create a derived index for FILE1 using the HISTC
field? Also, *maybe* if you use a CTE, "pre-selecting" FILE1 the more
complex part of the search would be done on a fewer number of records.

Something like:
With T1 as (Select * FROM FILE1 WHERE HISTC IN('C', 'D', 'F') )
Select a.FLD1, a.FLD2, a.FLD3, a.FLD4, a.FIRSTNME, a.MIDDLENME, a.LASTNME
, a.HISTC , b.ADDRL1, b.CITY, b.STATE from T1 a left outer join FILE2 b
...

Regards,

Luis Rodriguez
IBM Certified Systems Expert - eServer i5 iSeries

________________________________
Confidentiality Notice: This information is intended only for the individual or entity named. If you are not the intended recipient, do not use or disclose this information. If you received this e-mail in error, please delete or otherwise destroy it and contact us at (800) 348-7468 so we can take steps to avoid such transmission errors in the future. Thank you.