Re: SQL Statement
Date: 1995/12/17
Message-ID: <4b00ik$a85_at_bagan.srce.hr>#1/1
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