Solved

Looking for alternative to ADO data control

Posted on 2002-07-18
7
292 Views
Last Modified: 2013-11-23
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
0
Comment
Question by:iameric
7 Comments
 
LVL 4

Accepted Solution

by:
gencross earned 50 total points
ID: 7163311
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7163505
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
 
LVL 4

Expert Comment

by:AlonHirsch
ID: 7164244
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7164846
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
 

Author Comment

by:iameric
ID: 7188989
Thank you for the valuable feedback. I now have a much better idea how to attack the problem.

Eric
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 8014013
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
 
LVL 1

Expert Comment

by:Computer101
ID: 8096065
Comment from expert accepted as answer

Computer101
E-E Admin
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now