SQL*Plus uses OCI to perform queries while your web product most likely
uses ODBC. Enough said. If you are using VBScript and ASP for your web
app then check out SQL-Sombrero OCX/OCI from Sylvain Faust Intl. Using
this product will give you SQL*Plus speed.
mpir_at_compuserve.com wrote in article <6ise9l$mhn$1_at_nnrp1.dejanews.com>...
> In article <3550B465.3737D3AD_at_ix.netcom.com>#1/1,
> ranen_at_ix.netcom.com wrote:
> >
> > Hi --
> >
> > The following SQL query is the basis for a PL/SQL procedure using
> > DBMS_SQL
> >
> > that needs to be run several
> > times during a WWW-based product. The query takes so long that the
> > user's
> >
> > browsers are timing out!
> > When I run the following in SQL*Plus it takes 1.2 seconds (returns
about
> > 40
> >
> > records)
> >
> > These are the Table sizes:
> > chpa_test_product = 249 records
> > chpa_insurer = 38 records
> > chpa_county = 66 records
> >
> > Does this seem reasonable to anyone? How can Oracle be the number 2
> > selling
> > software in the world with performance like this? I changed the
> > ordering of
> > the WHERE CLAUSE to every possible combination and still only managed
to
> >
> > shave off about 2 seconds. The DB is Oracle Version 7, running on Sun
> >
> > Solaris. I'm thinking it has to be a hardware issue, or that the DB is
> > really messed up. Unfortunately, the DBA and UNIX Admin won't even
look
> > at
> >
> > the situation. Is this query REALLY that bad??
> > Any thoughts appreciated.
> >
> > Thanks -- Lynn
> >
> > SELECT
> > A.full_product_number, A.ffn , A.plan_type, A.deductible,
> >
> > A.benefit_level,A.tobacco, A.effdate, A.county_code, A.contribid,
> > A.trendid,
> >
> > A.copay, A.pcp, A.pos, 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')
> > 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));
> >
> >
>
> Try reversing the tables in the 'Where' clause for one.
>
> Next, I presume all your tables are indexed for the qualifying fields.
>
> Third, you have several select distincts. Each will require a sort before
the
> main qualifications are checked.
>
> Fourth, even if you are indexed on D.county, the use of the functioin
will
> disable the index. Try upper(ucase?) on the county names. Same for
> benefit_level, etc. Each disablement becomes a full table scan.
>
> I hope that gives you a start, at least.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>
Received on Thu May 07 1998 - 15:21:46 CDT
