Looking for alternative to ADO data control

I'm a novice VB6 programmer developing a front end to an Oracle database. I remember in an introductory course an instructor saying "here is the ADO data control and this is what it can do...but nobody uses it because it is so slow." I'm trying to use it and finding that to be the case.

Can anyone point me in the direction I need to go to learn how to access the Oracle database and create recordsets, etc. without using the data control? My application has to be faster.

Thank you

Eric Olsen
iamericAsked:
Who is Participating?
 
gencrossConnect With a Mentor Commented:
The fastest way is to use SQL Statements to return/update/delete data in the database.  This is a little more difficult to use, but much faster.  You will want to open an ADO database connection and use a SQL statement to return a recordset.

Example.  This example opens a SQL Server database.  You will need to modify the connection string and provider for Oracle(there is help in the VS help or Microsoft's site or the web for the correct provider and connection string)...

Dim objDBConn as ADODB.Connection
Dim rstTemp as ADODB.Recordset

With objDBConn
        .CursorLocation = adUseServer
        .ConnectionString = "driver={SQL Server};server=ehssqldev;uid=sa;pwd=pass;database=newdb"
        .Provider = "sqloledb"
        .CommandTimeout = 60 'Time out in one minute
        .Open

        If objDBConn.State <> adStateOpen Then
            'Connection not open successfully
            Set objDBConn = Nothing
        End If

        'Once the connection is open a recordset with data in it
        set rstTemp = .execute("SELECT * FROM myTable")
End With

'Go through all records of the recordset
With rstTemp
    Do while not rstTemp.EOF
        debug.print .Fields("myField").Value
        .MoveNext
    Loop
       
    .Close
End With

       
0
 
Anthony PerkinsCommented:
As gencross has pointed out, the alternative to the data controls is writing code.  But it should also be pointed out, that the advantage is not only in speed, but also in far better control.  Trust me, once you dump the Data Control there is no looking back.

Anthony
0
 
Alon HirschSoftware Development ManagerCommented:
I agree with everyone - the data control is an 'easy' solution for lazy people.
The fastest results will be obtained by using stored procedures in Oracle, or if you donot want to use stored procedures, then use prepared commands with ADO :
<from gencross example above>
Dim objDBConn as ADODB.Connection
Dim rstTemp as ADODB.Recordset
dim cmdSelect as ADODB.Command

With objDBConn
       .CursorLocation = adUseServer
       .ConnectionString = "driver={SQL Server};server=ehssqldev;uid=sa;pwd=pass;database=newdb"
       .Provider = "sqloledb"
       .CommandTimeout = 60 'Time out in one minute
       .Open

       If objDBConn.State <> adStateOpen Then
           'Connection not open successfully
           Set objDBConn = Nothing
       End If
End With

set cmdSelect = new adodb.command

with cmdSelect
   .ActiveConnection = objDBConn
   .prepared = True
   .CommandType=adCmdText
   .CommandText="SELECT * FROM MyTable"
end with

set rstTemp = cmdSelect.Execute

'Go through all records of the recordset
With rstTemp
   Do while not rstTemp.EOF
       debug.print .Fields("myField").Value
       .MoveNext
   Loop
       
   .Close
End With

The reason this will speed up your application over the previous approach is that each time you issue the SQL SELECT, Oracle will have to compile the sql and then execute it. When using command objects and prepared statements, the compile only happens the first time you query the recordset. All subsequent SELECTS will use the precompiled (prepared) version.

Ideally, the command object should be declared as a module level variable and defined when the form is loaded.
When you click on the 'Populate' button, you simply use the recordset.

I've done this in dozens of projects and it works wonders in terms of performance.
You can use command objects for SELECT, INSERT, UPDATE and DELETE statements and can also pass parameters :
SELECT * FROM MyTable WHERE sName LIKE ?
This is done to enable searching for example ...

HTH,
Alon
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Anthony PerkinsCommented:
AlonHirsch,

Not all providers support the "Prepared" property.  Further, even if it is supported, if the SQL statement is called infrequently, there is not much to gain as will take a hit the first time the call is made.  But your points are well made and should be taken in account when developing an application.  One of the challenges with Oracle is that it is difficult to return resultsets from stored procedures.  A workaround I have seen, is precisely the one you advocate:  Prepared statements.

Anthony
0
 
iamericAuthor Commented:
Thank you for the valuable feedback. I now have a much better idea how to attack the problem.

Eric
0
 
DanRollinsCommented:
Hi iameric,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept gencross's comment(s) as an answer.

iameric, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
Computer101Commented:
Comment from expert accepted as answer

Computer101
E-E Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.