Re: SQL Statement

Issac Wong Tuck Kwong (wisaac_at_starnet.gov.sg) wrote:
: 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 first statement uses index only on column B1. Instead of using "or" which never uses index try

 Select A1,A2,B1 from A,B
 where A3 = variable_1 and B1 = variable_1  union
 Select A1,A2,B1 from A,B
 where A3 = B1 and B3 = variable_2;  

But, maybe you mistyped your SQL statement? ( It looks very strange.) Maybe it looks like

Select A1,A2,B1 from A,B
 where A3 = B1 and

       (B2 = variable_1 or  /* not B1 ??? */
        B3 = variable_2);

In that case try to create two indexes over columns B1,B2 and B1,B3. Then try next statement:

Select A1,A2,B1 from A,B
 where A3 = B1 and

       B2 = variable_1
union
Select A1,A2,B1 from A,B
 where A3 = B1 and

        B3 = variable_2;

I hope it helps!

Mario Simic
marios_at_open.cc.fer.hr
Zagreb, Croatia Received on Sun Dec 17 1995 - 00:00:00 CET