Re: problem w/ insert of temp table
Try running this one under SET OPTIMIZATION FIRST_ROWS.
Art S. Kagel
"Corey D. Herbel" wrote:
>
> Heads up sorry about the long post.
>
> I have been analyzing a very harsh query to no avail. I'd like to see
> if anybody sees any problems.
> The offensive SQL:
> SELECT a.id
> , a.createdate> , b.playerid
> , b.firstname
> , b.lastname
> , b.pos
> , c.city
> , c.name
> , c.url
> , monthnames.abb3
> , sourceid
> FROM tbl1 a
> , tbl2 b
> , tbl3 c
> , monthnames
> WHERE monthnames.number=MONTH(f.MODIFYDATE)
> and c.sportsym='FB'
> and c.sportsym=b.sportsym
> and b.sportsym=a.sportsym
> and c.teamid = p.team
> and b.playerid = a.playerid
> and a.type in (4,5,....,255)
> ORDER BY a.id DESC;
>
> Removing the order by reduces the query from about three minutes to
> around 6 seconds. I have updated statistics on the tables and have
> checked to sqexplain.out file.
>
> sqexplain.out file:
>
> Estimated Cost: 550
> Estimated # of Rows Returned: 1
> Temporary Files Required For: Order By
> 1) informix.c: INDEX PATH
> (1) Index Keys: sportsym (Serial, fragments: ALL)
> Lower Index Filter: informix.c.sportsym = 'FB'
> 2) informix.a: INDEX PATH
> Filters: informix.f.type IN (4 , 5 , ... , 255 )
> (1) Index Keys: sportsym (Serial, fragments: ALL)
> Lower Index Filter: informix.c.sportsym = informix.a.sportsym
> NESTED LOOP JOIN
> 3) informix.monthnames: INDEX PATH
> (1) Index Keys: number (Serial, fragments: ALL)
> Lower Index Filter: informix.monthnames.number
> = MONTH (informix.a.modifydate )
> NESTED LOOP JOIN
> 4) informix.b: INDEX PATH
> Filters: informix.c.teamid = informix.b.team
> (1) Index Keys: playerid sportsym (Serial, fragments: ALL)
> Lower Index Filter: (informix.b.playerid = informix.a.playerid
> AND informix.b.sportsym = informix.a.sportsym )
> NESTED LOOP JOIN
>
> I have also tried to remove the ORDER BY and instead use a TEMP table
> for the result set. Ultimately, my goal was to create an index DESC on
> the TEMP|ID column after inserting the results and then select out the
> results. I now get a 4 minute delay in just inserting the results in
> the TEMP table (w/o the ORDER BY).
>
> Any help would be greatly appreciated.
> Thanks in advance,
> Corey Herbel (Newly appointed DBA, but still green)