Re: PL/SQL Performance

From: Saqib Zulfiqar <saqib.zulfiqar_at_cressoft.com.pk>
Date: 1998/05/18
Message-ID: <6jq4rv$l9m$1@sussi.cressoft.com.pk>#1/1

Dear Lynn Ranen,

I've looked at ur query and there are a couple of possibilities that could be tried to achieve the desired performance.

Ur original query is:

SELECT
>A.full_product_number, A.ffn ... B.insname
> FROM chpa_test_product A,
> chpa_insurer B
>WHERE B.ffn = A.ffn
> AND A.county_id IN (select distinct F.id
> from chpa_county F
> where lower(F.county) IN ('broward', 'dade'))
> AND A.plan_type IN ('HMO','PPO','HMO','HMO','HMO')
> AND A.deductible IN (0,100,250,500,750,2000)
> AND LOWER(A.benefit_level) IN
> ('basic','standard','street','plusplan')
>AND A.ffn IN (select distinct D.ffn
> from chpa_county D
> where lower(D.county) IN ('broward','dade')
> AND D.ffn IN (select distinct C.ffn
> from chpa_insurer C)) ;

Modification # 1:

In the original query u have written
Select
A.full_product_number, A.ffn ... B.insname
> FROM chpa_test_product A,
> chpa_insurer B

<snip.....>
to give better performance , i hope that ur using the larger table first and then the relatively smaller table. i.e: chpa_test_product has more rows than chpa_insurer. therefore chpa_insurer table will be scanned on the index value in lesser time than a larger table. So use the lager table first.

Modification # 2:

---

<snip.....>
AND A.county_id IN (select distinct F.id
> from chpa_county F

Don't use the distinct keyword in ur subqueries: Distinct is slower on queries because the data is kept in a temp area. therefore I/O delay and processing on the data. Just like the dalay a sort would do on a non sorted query.

Modification # 3:

---

<snip....>
  where lower(F.county) IN ('broward', 'dade'))

Rather than writing the above line try
where (F.county) in ('BROWARD','DADE'))
the reason being that the index will only be used if the original indexed field present in the where statement is used. By using a function on a field the possibility of an indexed scan is none. Therefore don't use functions on fields in the where conditions U can also use
where f.county in (upper('broward',upper(dade') if u have to.

Modification # 4:

---

The following fields must be indexed:
i) A.ffn and B.ffn
ii) A,county_id
iii)f.county
iv) a.plan_type
v)a.deductible
vi)a.benefit_level
The reason being that u r using these column in ur where clause and for the range scans they must be indexed.

Modification # 5:

---

<snip....>
AND A.ffn IN (select D.ffn
> from chpa_county D
> where D.county IN ('BROWARD','DADE')

this is an optional modification, if the result set in ur last subquery is small, i.e: a small set of rows are returned from a large table then the exists clause makes sense otherwise use the in statement. I personally would prefer the query with the exists statement.

After apply ing the modifications, the query becomes

SELECT
A.full_product_number, A.ffn ... B.insname

FROM chpa_test_product A,	     -- (larger table comes first)
     chpa_insurer B

WHERE B.ffn = A.ffn
 AND A.county_id IN (select F.id
   from chpa_county F
   where F.county IN ('BROWARD', 'DADE'))

 AND A.plan_type IN ('HMO','PPO','HMO','HMO','HMO')
 AND A.deductible IN (0,100,250,500,750,2000)
 AND A.benefit_level  IN
                ('BASIC','STANDARD','STREET','PLUSPLAN')

AND A.ffn IN (select D.ffn

   from chpa_county D
   where D.county IN ('BROWARD','DADE')) ;

If u feel that the last "IN" has to be replaced by an exist then subsitute the last "AND" statement with this.. AND exists (

		select 1 from d.ffn
			from chpa_county D
		where D.county IN ('BROWARD','DADE')
		and A.ffn = D.ffn
		) ;

P.S: using so many hard coded values in the "IN" statements doesn't seem a good design/programming practice to me. Any way....... I hope that these tips will help, in any case let me know.

HTH   Lynn Ranen <ranen_at_ix.netcom.com> wrote:

>Hi All --
 

>I’m still having an uphill battle with performance issues. Last week, I
>posted for help with a “long query”. Since then, I have looked harder at the
>issue and find that overall performance is horrible and completely
>unacceptable.

////////////////////////////////////////////
//                                        //
//           Saqib Zulfiqar               //
//           Software Engineer            //
//           CresSoft Pvt Ltd.            //
//           Lahore, Pakistan.            //
//E-Mail :saqib.zulfiqar_at_cressoft.com.pk  //
//                                        //
////////////////////////////////////////////

I get melancholic sometimes, but I've learned to enjoy it Received on Mon May 18 1998 - 00:00:00 CDT