Performance issue

From: bido <sideb_neb_at_yahoo.com>
Date: 10 Sep 2005 08:36:21 -0700
Message-ID: <1126366581.454253.135260@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 Received on Sat Sep 10 2005 - 10:36:21 CDT