Re: SQL Statement

wisaac_at_starnet.gov.sg (Issac Wong Tuck Kwong) wrote:
>I have a sql statement which causes a very slow response. Can someone
>here help to check if the statement can be improved or better structured
>table.
> :
> :
>Select A1,A2,B1 from A,B
>where A3 = B1 and
> (B1 = variable_1 or
> B3 = variable_2);
 

>However the performance is improved tremendously if the SQL statement is
>reconstructed in the following manner:
 

>Select A1,A2,B1 from A,B
>where A3 = B1 and
> (B1 = variable_1);

        Just an observation, in either of these statements, if for any reason no value exists where A3 = B1 then it will not be listed reguardless if a record exists in B where B1=(...) or B3=(...). Depending on how your DB is set up (presence of Foreign Keys), this may not happen. The following will provide your original listing but also give indicators of orphan records in B (if that is a concern).

Select	A1,A2,B1 from A,B
Where	B1 = A3(+)
  and	(	B1 = Var1
	or	B3 = Var2);

	As to the speed issue, I suspect it is because the second statement is

Select	A1,A2,A3 from A
Where	A3 = variable_1;

	... And SQL might be smart enough to figure this out....