Bind variables and SQL Monitor/DB Monitor

Guest · Post by **Guest** » Thu 09 Dec 2004 10:32

I work for a company who are new to ODAC. We are currently trying to upgrade from the BDE and connect to an Oracle 8.1.7 i and 9i databases. We are currently assessing the scalability of our product and are undertaking some perfomance testing.

From the ODAC test program we are getting results that show a vast amount of queries not using bind variables. This may be probelms with legacy code, but I first wanted to ask if there are any specific differences between the way ODAC and BDE handle Bind Variables. We have just used the Migration wizard to convert the code for the test application and have yet to start tweeking the components.

The BDE TQuery component has a prepared property and I see that ODAC TOraQuery has a Keep prepared option do these provide similar functionality? In what situation should they be used?

Also when I try and look at the difference between the ODAC and BDE queries within SQL Monitor and DB monitor I get 3 pages of very comprehensive output for the BDE and only 5 lines for ODAC. Is this just differences with the way the way ODAC connects to the SQL Mointor?

Alot of questions but I would be grateful of any help you could provide.

Thanks

Paul · Post by **Paul** » Fri 10 Dec 2004 14:13

You can see bind variables in DBMonitor in "Params" tab when cursor is on select or prepare command line. DBMonitor and SQLMonitor always show bind variables if SQL statement is written with parameters. There is no difference between BDE and ODAC how them execute statement and pass bind variables to Oracle server. TQuery.Prepared examines the current state of query (if it is prepared or not already). If TOraQuery.OptionsDS.KeepPrepared is true then TOraQuery remains prepared after closing and it is not needed to call Prepare before the next executing. It is useful if you want to call several times TOraQuery.Execute with different parameters. If TOraQuery.OptionsDS.KeepPrepared is true you can do the following to increase overall execution time. Every subsequent OraQuery.Execute does not call OraQuery.Prepare inside in this situation.

Code: Select all

 
OraQuery.Prepare;
OraQuery.ParamByName(..).Value:=Value1;
OraQuery.Execute;
OraQuery.ParamByName(..).Value:=Value2;
OraQuery.Execute;
OraQuery.ParamByName(..).Value:=Value3;
OraQuery.Execute;

ODAC does not show all the debug information in DBMonitor, SQLMonitor. It was made for increasing performance. DBMonitor can show the following operations: Prepare, Execute, Connect, Disconnect, Commit, Rollback, Error messages. ODAC hides Prepare event on TOraQuery.Execute if TOraQuery.OptionsDS.KeepPrepared is false.

Guest · Post by **Guest** » Wed 22 Dec 2004 15:32

Thanks Paul for the information.