OraFAQ Forum: Forms » SQL Vs Oracle Forms Performance issue

Home » Developer & Programmer » Forms » SQL Vs Oracle Forms Performance issue (SQL 10.2.0.1.0 Forms 6.0.8)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Vs Oracle Forms Performance issue [message #678399]

Sat, 30 November 2019 04:59

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

Hello,

A simple query is giving result in few seconds while same query is giving result in Oracle Forms in 20 seconds. (The query is being used in when validate item in order to populate the balance).

Here are are the both codes.
in SQL

SQL> select balance  from v2cust where ccode=153;

   BALANCE
----------
     .8792

in Oracle Froms


message(2.1); pause;
Select balance 
	into :block1.d_ob
	from v2cust
	where ccode = :block1.ccode;
message(2.2); pause;

What am i missing?

Kindly help

Report message to a moderator

Re: SQL Vs Oracle Forms Performance issue [message #678403 is a reply to message #678399]

Sat, 30 November 2019 11:26

John Watson
Messages: 8879
Registered: January 2010
Location: Global Village

Senior Member

I am not any sort of Forms expert, but....

You may be getting different execution plans because of (what looks like) the bind variables that Forms uses. Can you put the two statements through EXPLAIN PLAN to check?

Also, put this through EXPLAIN PLAN,

select balance from v2cust where ccode=:var;

[Updated on: Sat, 30 November 2019 11:28]

Report message to a moderator

Re: SQL Vs Oracle Forms Performance issue [message #678404 is a reply to message #678403]

Sat, 30 November 2019 12:11

Littlefoot
Messages: 21783
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Is v2cust.ccode column indexed?

If you run the form in debug mode, see what's going on. Perhaps another trigger gets fired which spends time.

Because, there's no reason (at least, I don't see any) why would the same query run differently in the same database .

If you can't find the reason, use TKPROF, it'll show what's going on in details.

Report message to a moderator

Re: SQL Vs Oracle Forms Performance issue [message #678406 is a reply to message #678404]

Sat, 30 November 2019 15:52

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

A quick reply: v2cust is a view but underlying all tables involved are indexed

Report message to a moderator

Re: SQL Vs Oracle Forms Performance issue [message #678409 is a reply to message #678406]

Sat, 30 November 2019 22:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

bluetooth420 wrote on Sat, 30 November 2019 13:52

A quick reply: v2cust is a view but underlying all tables involved are indexed

The fact that a table has an INDEX, it does NOT mean that this INDEX is used for any or every query.

Report message to a moderator

Re: SQL Vs Oracle Forms Performance issue [message #678423 is a reply to message #678409]

Mon, 02 December 2019 04:35

cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester

Senior Member

Best thing to do is run a trace on the sqlplus session and the forms session and compare the tkprof output.
That'll show if the query is actually behaving differently and what the difference is.

Report message to a moderator

Previous Topic:	LOV buttons do not show questioning, but ellipsis
Next Topic:	Forms 10g: builder crash while opening a Form even first connect (ctrl+j)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 29 09:38:44 CDT 2023