ADO extremely Slow

[kilobravo3]

I was using DAO to access a database in my program. I read in a text file, extract the values and then insert them into the appropriate table in my Database (Access 2000). Using the DAO reading a file with ~ 1500 lines(1 record per line) and then inserting into the database happend quite quickly (~ 1 sec or less). However, I was interested in trying out ADO. When I use the ADO to run the same piece of code the execution takes around 10 minutes. Am I missing an important setting or something. I am very new at VB so maybe someone out there knows what I am doing wrong. It just doesn't seem right that there should be such a difference.

Thanks.

[chrisjk]

I too find ADO slower than DAO, but DAO was specifically written for Jet, ADO wasn't, so it's hardly surprising. Unfortunately, Mr. Gates seems to think we should be using ADO for everything,, even though DAO is quicker (with Access anyway)

[peet]

it seems a bit too slow even for ADO i think... u have the latest version and all? (had some problems with earlier versions)

have u tried stepping through the code and see if it "hangs" at certain points?

[Sastraxi]

I suggest using SQL, as it's just (or almost) as fast, and it's supported well.

[chrisjk]

I find the biggest problem with ADO is it doesn't update quick enough, so you requery the source after modifying it and it hasn't changed! DAO doesn't seem to have the same problem.

[peet]

but 1 sec against 10 min ?? have to be something terrible wrong here, dont u think chris?

[chrisjk]

I didn't read the whole post, that is quite shocking. kilobravo3, post your ADO code so we can take a look...

I suggest using SQL, as it's just (or almost) as fast, and it's supported well

?? You need to contain SQL statements within ADO or DAO code...or is that not what you meant?

[peet]

if u can, up the db and the code, and we can test the time on our computers ...

[kilobravo3]

thanks for the fast replies. I too find the difference in speed a bit shocking. I have change my code back to DAO (quite easy in this case), so here is the DAO code I am using. I just swap in the ado object when I make the transition. I set up the ADO during design time to map to my created System DSN and table name.

'....Code to read the file

With dataTmpPhone.Recordset
.AddNew
!apptDate = apptDate
!apptTime = apptTime
!doctorName = doctorName
!patientfName = patientfName
!patientlName = patientlName
!patientNo = patientNo
!officeName = office
!miscDate = miscDate
!notes = notes
!misc1 = misc1
!misc2 = misc2
.Update
End With

....Loop back and do it again

I put some debug printing statements in trying to figure out the problem and it finally came down to the ADO. Like I said I am a Java programmer trying to learn VB so I could be doing everything wrong. The other code in this loop doesn't appear to affect speed all that much ( I commented it out to test).

Thanks again.

Kevin

[kilobravo3]

I think I slightly exagerated my numbers. I just ran another test with 700 lines in the file and the total time with DAO was about 3 secs and with the ADO about 3 minutes. So, I guess my 1500 line numbers weren't too far off, but the performance difference still seems odd.

Also, when the ADO code runs, you can see the hourglass flashing off and on when the pointer is on the form where the button to get the whole thing started is.

Kevin

[kilobravo3]

I have solved the problem (beginners mistake, although there really is no excuse for it). When I used the ADO, for some reason it would give me the Error 91 object not defined error eventhough it is in an event handler code (not form_load or initialize). So, I used the ADO.Refresh method, and that seemed to help the situation. The problem was that I mistakingly put the .Refresh inside the loop!!!!! OOPPPS!!!!!

But, I guess that is how you learn. Thanks again for all the replies.

Kevin

[Sastraxi]

Even so, I suggest using SQL inside of your ADO. An example:

ADO.asp 
oConn.Open [connection string]
oRec.Open "A Table", oConn, adOpenKeyset, adLockOptimistic, adCmdTable
or
oRec.Open "SELECT * FROM A Table", oConn, adOpenDynamic, adLockOptimistic

It's a bit more code (but only a bit more) but with this you can have aliases and queries (well dynamic ones anyway), which are all very powerful and fast. But, the choice is yours, and I'm glad you found your problem!

[garths]

the microscopic jet engine is significantly slower than any enterprise query processing engine within infastructures like sql server, oracle, sybase, informix, db2, foxpro, blah blah baa. access is for desktop users. performance with your 10 seconds is yeah great.. for you, and only you on your local computer using dao or odbcdirect to get 10 20 100 or even a thousand. Try 10 or more users with concurrent access on that access database using..dao, wohoo! grab a pillow for that excitement. Not to mention access can't handle more than 2gb of data, 1gb if your still playing with backwards compatability issues and wasting time with '97..
Anything else can hold terabytes upon terabytes of data along with a zillion more features. put simply:

access = desktop, personal, small scale user db's or a front end interface to any of the enterprise backends..

enterprise db(sql server for instance): = scalable, robust, reliant, centralized, powerful(very fast), feature-centric architecture that works best with ADO, and yes it's fast, if you use it right..even with hundreds to thousands of concurrent users...

access is a bad habit for alot people..but if you must, use dao for 97(it's optimized) and ado for 2k and xp....
till next time..
adios

[mvrp350]

I agree on your post garths, but sometimes it's unavoidable to use access.
If a small company with say 5 or 10 employees wants to have a DB, it's a big investment to purchase an 'enterprise DB' and develop something for it.
While access is pretty cheap, easy to manage and it's easy to write a program for it.

Even though you are absolutly right with your point, in some cases it's unavoidable. On the other hand, if that same company has grown to 100 employees it might be looking for another DB. In that case if the code is made transferrable, it isn't that hard to change in a later stage from Access to let's say SQLserver or Oracle. So the company is spreading it's investments over a period of a couple of years.

[peet]

maybe instead of using access, using MSDE ?

[garths]

"I agree on your post garths, but sometimes it's unavoidable to use access"

-> hence why i said, "if you must"...

Even though you are absolutly right with your point, in some cases it's unavoidable. On the other hand, if that same company has grown to 100 employees it might be looking for another DB. In that case if the code is made transferrable

-> covered this one too...one brief tip here. One of many mistakes junior's around the world make are thinking there's such a thing as 'transferrable' access code. If anything, there's salvagable access code...

Like i said, use access if you or your company lack money, data, knowledge and the hunger for an enterprise solution.

[mvrp350]

My point with transferrable code is that you are able to reuse as much of the code as possible.
100% transferrable code is an impossability, simple because of the differences in the DB progams.
i.e Oracle doesn't support spaces in a table name.
If I create a tablename in acces with a space, I will have to make some changes in my code if the DB is transferred to Oracle.
Those issues are impossible to cover in advance.

I think we both are pointing in the same direction, but my words are slightly different chosen.

Regards

[peet]

MSDE - > SQL Server = 100% transferrable code ? or am I wrong again?

[mvrp350]

Might be, I have never used SQL server, but it wouldn't surprise me