Hi Michael,

Who are indexes on table PROJ_HRS?

Because SQL IN() is not optimalized on ORACLE?

Why Your SQL is not?

SELECT PROJ_HRS.* FROM PROJ_HRS
WHERE (PROJ_HRS.proj_cat_no = 1 OR
       PROJ_HRS.proj_cat_no = 3 OR PROJ_HRS.proj_cat_no = 6)
   AND PROJ_HRS.atelier = 0 
   AND PROJ_HRS.accepted = 0
   AND PROJ_HRS.refused = 0 
   AND PROJ_HRS.wrk_hrs <> 0 

MartinJ

>Hi All,
>
>I am wondering something. I have 2 queries (see below) query 2 takes 6 times longer to execute than query 1. Why? It's more compact. Could someone please explain this to me. Is there an issue with the 'IN()'? Both are executed by SPT using the Oracle ODBC.
>
>VFP6 SP5 Oracle 8i
>
>

>lcSqlStatement = "SELECT PROJ_HRS.* " +;
>		 "FROM PROJ_HRS " +;
>		 "WHERE PROJ_HRS.proj_cat_no = 1 " +;
>		 "  AND PROJ_HRS.atelier = 0 " +;
>		 "  AND PROJ_HRS.accepted = 0 " +;
>		 "  AND PROJ_HRS.refused = 0 " +;
>		 "  AND PROJ_HRS.wrk_hrs <> 0 " +;
>		 "UNION ALL SELECT PROJ_HRS.* " +;
>		 "FROM PROJ_HRS " +;
>		 "WHERE PROJ_HRS.proj_cat_no = 3 " +;
>		 "  AND PROJ_HRS.atelier = 0 " +;
>		 "  AND PROJ_HRS.accepted = 0 " +;
>		 "  AND PROJ_HRS.refused = 0 " +;
>		 "  AND PROJ_HRS.wrk_hrs <> 0 " +;
>		 "UNION ALL SELECT PROJ_HRS.* " +;
>		 "FROM PROJ_HRS " +;
>		 "WHERE PROJ_HRS.proj_cat_no = 6 " +;
>		 "  AND PROJ_HRS.atelier = 0 " +;
>		 "  AND PROJ_HRS.accepted = 0 " +;
>		 "  AND PROJ_HRS.refused = 0 " +;
>		 "  AND PROJ_HRS.wrk_hrs <> 0 " +;
>		 "ORDER BY 1 "
>
>lcSqlStatement = "SELECT PROJ_HRS.* " +;
>		 "FROM PROJ_HRS " +;
>		 "WHERE PROJ_HRS.proj_cat_no IN ( 1, 3, 6 ) " +;
>		 "  AND PROJ_HRS.accepted = 0 " +;
>		 "  AND PROJ_HRS.refused = 0 " +;
>		 "  AND PROJ_HRS.atelier = 0 " +;
>		 "  AND PROJ_HRS.wrk_hrs <> 0 " +;
>		 "ORDER BY 1 "

>
>TIA
>Mike