ADO, DAO and Microsoft SQL performance problems

[tcollins]

I have been reading the threads concerning performance problems with ADO and Microsoft SQL Server. I have the same problem...and no solutions. Lets assume the hardware is ok because we can create and update LARGE MS Access 97 databases on the server from any client...FAST. The hardware/network isn't an issue. DAO with Access 97 isn't an issue...its fast...really fast. However, the need is now to move up the chain to something better...like SQL Server...to better fit the needs.

Here is where things get REALLY slow. For DAO and Access 97 to create a database file and store 10,000 record takes maybe 2 seconds. Using ADO and SQL to perform the same function takes forever. 1000 records takes 2 minutes. The destination files/databases are stored on our remote server. Now, we monitor the TCP network traffic on the server...we see that Access rails up to 25% using a sharp slope...SQL takes a broadband behavior at 25% over how ever long it takes to store our data.

I've tried direct SQL Statements "INSERT INTO...". I've tried record sets, I've tried DAO with SQL...very slow. I've tried NOT using the ODBC...very slow. Its not the server we set up because we checked ourselved against a know-to-be-fast server.

This is our little test program (a little edited but you got the idea):

Const DBDriver = "{SQL Server}"
Const DBServer = "SQLserver"
Const DBUid = "user"
Const DBPwd = "passwd"
Const DBdatabase = "pubs"
Const DBProvider = "SQLOLEDB"

Set adb = New ADODB.Connection
adb.ConnectionString = "Provider=" & DBProvider & ";" & "server=" & DBServer & ";" & "Uid=" & DBUid & ";" & "Pwd=" & DBPwd
adb.Open
SQLString = "CREATE DATABASE TEST2"
adb.Execute SQLString

SQLString = "USE TEST2"
adb.Execute SQLString

SQLString = "CREATE TABLE Counter (Count1 int)"
adb.Execute SQLString

For I = 1 To 1000
SQLString = "INSERT INTO Counter VALUES (" & I & ")"
adb.Execute SQLString
Next I
adb.Close

[tcollins]

Response to myself...

A couple of suggestions.
- it seems that if you use .TransBegin and .TransComit things speed up quite a bit faster.
- Apparently there's a way to 'disconnect' and 'reconnect' the recordset to the database? Lessens network traffic? I don't know how.
- The Size of your data may be an issue. 'Buddy' sends big 'chunks' of data and has no problem, but when he tries our many 'smaller chunks' he sees the same problem we are having.

Is sending MS SQL Server lots of smaller chunks of data fair to compare?

[pardede]

Hi, I'd like to share my humble thought on your case here..

Your last thought may be a meaningful one: sending large data in one transaction might indeed be faster then sending small data's in multiple transaction. In a way you can compare it with faxing: sending lots of pages in one call is faster than one page per call with many calls, just by considering that you have to dial the number again and again.

Me myself I haven't worked with SQL-servers with DAO/ADO but i have indeed read many comments on how slow it is. I think the point is, you need to examine the traffic that goes back and forth between the client en server. I'm coming to your other point now: using disconnected recordset. Once again I haven't really practiced with it but from what I've read it should be fairly easy to use. Just open an empty recordset and disconnect it, and do some "local transaction" with this recordset, reconnect with DB when finished and send all "local transaction" up to the DB.

As far as I know, opening an empty recordset and disconnecting it can be done by something like:
set rsMyRecordset = dbMyDatabase.OpenRecordset _
"select * from table_name where 1=0"
'1-0 returns 'false' that causes the recordset
'returned to be empty but have the structure of the table
rsMyRecordset.Disconnect
'here you can do some 'local transaction'
rsMyRecordset.AddNew 'and so on and so on

After this you can reconnect and send new changes to the database. I forgot how to do this but i think it's almost as easy as disconnecting, i.e. by calling a method of the recordset...

Well, I think that's all I could pour out for now. I hope it 's something... Let me know if you need more helping hand -or- helping mind in this case ;)

[Edited by pardede on 05-21-2000 at 01:33 AM]

[tcollins]

I don't seem to have a .Disconnect method. I'm using ADODB 2.0 (Visual Studio 6.0)


I also have concerns about disconnecting and reconnecting to the database because we will be using multiple clients to write their own records to the same database.