Re: SQL Statement

In article <4a3c6h$ekb_at_mercury.starnet.gov.sg>, wisaac_at_starnet.gov.sg says...
>
>Hi,
>
>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.
>
>I have 2 tables with the fillowing structures:
>Table A Table B
>------- -------
>A1 PK B1 PK
>A2 B2
>A3 B3
>
>PK - Primary Key
>
>The characteristic of Table B is that for B2 and B3 has a lot of repeated
>values.
>
>Table B has a index on B1,B2,B3.
>
>The problem SQL statement is as follows:
>
>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);
>
>Can someone suggest any possible way to improve the 1st mentioned SQL
>statement?
>
>Thanks in Advance!!
>
>Isaac
>E-mail : wisaac_at_starnet.gov.sg
>

The order of the fields in an index is important, redeclare your index on B as B1, B3, B2 or create another index on B1, B3 only or create an index on B1 only and another on B3 only. Since B2 is between B1 and B3 in your index, the index can't be used for the second half of your OR clause.

-- 
Tony Noble
cz0763%zip02_at_mt.gov
Opinions expressed do not necessarily reflect those of my employer.