Performance difference DOA-BDE

[Michael Cottle]

And not in a good way...

We have an application that we're porting from BDE to DOA using Delphi 7. The problem Query is as follows:-

object qryOrgName: TOracleDataSet
  SQL.Strings = (

      'Select LEGAL_NAME "Legal Name", ABN "ABN", ACN "ACN", ORGANISATI' +
      'ON_TYPE || ORGANISATION_CODE "GPR", STATUS "Status", ORGANISATIO' +
      'N_NUMBER'
    'From Organisation'
    'Where LEGAL_NAME LIKE :LegalName'
    'Order By LEGAL_NAME ASC')
  Variables.Data = {
    03000000010000000A0000003A4C4547414C4E414D4505000000000000000000
    0000}
  Cursor = crSQLWait
  Session = DatabaseDM.DOLA
  Left = 132
  Top = 424
end

As you can see, a simple namesearch. In the BDE version the query returns instantly. In TOAD it returns instantly. Using the new DOA build you're looking at a 50s wait (All with the same "S%" parameter) The cost's appalling & of the order of 2000 but this is the same query - though the BDE mangles the params & enables me to separate the two queries in V$SQL...

DOA's version has:-
4283 disk reads against 0
89290 Buffer Gets vs 20
182456 Rows processed vs 15

Session is as follows:-

object DOLA: TOracleSession
  Cursor = crSQLWait
  DesignConnection = True
  LogonUsername = 'XXX'
  LogonPassword = 'XXX'
  LogonDatabase = 'XXX'
  ThreadSafe = True
  BytesPerCharacter = bcAutoDetect
  Connected = True
  Left = 28
  Top = 8
end

Any thoughts?

 

[Marco Kalter]

By default a TOracleDataSet assumes that you are interested in the complete result set, and thefeore fetches all records when opened. If this is not the case, set TOracleDataSet.QueryAllRecords to False. Now records will be fetched when needed.

 

[Michael Cottle]

Thanks, I'll do that. It sounds like that's the answer