Re: Performance issue

"bido" <sideb_neb_at_yahoo.com> wrote in
news:1126366581.454253.135260_at_g49g2000cwa.googlegroups.com:

> Hi all,
> I have two long and very similar SQL queries. The only difference is in
> this part:
>
> Query 1: ... AND build_id IN (SELECT build_id from build where .... )
> ....
>
> Query 2: ... AND build_id IN ('xxxx', 'yyyy' ,.....) ....
>
> Note that the same snaphost above is repeated three times in both
> queries and that the SELECT statement above in Query 1 returns exactly
> the records specified inside the IN block of Query 2 meaning 'xxxx',
> 'yyyy' and so.
>
> Other than that both queries are exactly the same.
>
> The first query takes around 25 seconds to execute the second takes 1
> minute 25 seconds!!!
>
> Note that both queries are programmatically generated by our
> application based on the user input. We had to change Query 1 into
> Query 2 due to the introduction of a new mandatory feature.
>
> How come Query 2 is much slower than Query 1 and is there a way to make
> it faster?!
>
> Thx,
>
> Ben
>
>

generate & post the EXPLAIN_PLAN for both queries Received on Sat Sep 10 2005 - 10:51:58 CDT