Home » Developer & Programmer » Forms » SQL Vs Oracle Forms Performance issue (SQL 10.2.0.1.0 Forms 6.0.8)
SQL Vs Oracle Forms Performance issue [message #678399] Sat, 30 November 2019 04:59 Go to next message
bluetooth420
Messages: 141
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
Re: SQL Vs Oracle Forms Performance issue [message #678403 is a reply to message #678399] Sat, 30 November 2019 11:26 Go to previous messageGo to next message
John Watson
Messages: 8077
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 Go to previous messageGo to next message
Littlefoot
Messages: 21517
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.
Re: SQL Vs Oracle Forms Performance issue [message #678406 is a reply to message #678404] Sat, 30 November 2019 15:52 Go to previous messageGo to next message
bluetooth420
Messages: 141
Registered: November 2011
Senior Member
A quick reply: v2cust is a view but underlying all tables involved are indexed
Re: SQL Vs Oracle Forms Performance issue [message #678409 is a reply to message #678406] Sat, 30 November 2019 22:10 Go to previous messageGo to next message
BlackSwan
Messages: 26642
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.
Re: SQL Vs Oracle Forms Performance issue [message #678423 is a reply to message #678409] Mon, 02 December 2019 04:35 Go to previous message
cookiemonster
Messages: 13755
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.
Previous Topic: LOV buttons do not show questioning, but ellipsis
Next Topic: LOGON function
Goto Forum:
  


Current Time: Mon Dec 09 20:37:15 CST 2019