Time taken to run SQL over 56K line.

[gordonl]

When I try to run my program on a 56K line I have the following problem:

I have the following delcared as a Public variable:

Set dbconn = New ADODb.Connection
dbconn.ConnectionString=
"provider=microsoft.jet.oledb.4.0;data source=p:\mro\newsystem.mdb;"
dbconn.CursorLocation = adUseClient
dbconn.Open

It takes about 30secs to open the database connection.

I then have the following code in a button on a form.

Dim comtest As ADODb.Command
Dim rstest As ADODb.Recordset
Set comtest = New ADODb.Command
comtest.CommandText = "select contactid from tblcontacts"
comtest.CommandType = adCmdText
comtest.ActiveConnection = dbconn
Set rstest = New ADODb.Recordset
Set rstest.Source = comtest
rstest.CursorType = adOpenStatic
rstest.LockType = adLockReadOnly
rstest.Open
rstest.close
set rstest=nothing
set comtest=nothing


The problem is that the first time when I click the button it takes 40 secs to open the recordset. However, any subsequent times, it just takes 2 secs to open the recordset.

WHY???

The problem is that no matter how many recordsets I open on the form, the first one after the program starts always takes 40 secs. All subsequent ones take 2 secs.

Any suggestions would be very much appreciated.

 

[chiph]

It's because the first time SQL server must authenticate you, either internally (if you're using SQL server authentication), or via Active Directory (if you're using Windows Authentication).

Chip H.

 

[gordonl]

Thanks chiph,

Do you a know of a way of overcoming this so you that this delay happens only once at the beginning of a program. At the moment it appears to happen once each Form. However, on some forms it happens once each recordset.

I am connected to an Access Database at the moment, presume this will go through the same authentication process.

I would have thought authentication would have just happened at the beginning when you opened the database and you would stay authenticated until the database connection was closed.

Do you know any good sources where I can read up on this sort of thing, as I really don't know whay goes on in the background, I just know the commands to achieve it.

 

[gordonl]

The data itself is only 5,948 bytes so should only take a second or so over a 56K line.

 

[chiph]

I would have thought authentication would have just happened at the beginning when you opened the database and you would stay authenticated until the database connection was closed.

True: you're going to get authenticated every time you open a new connection to the database. As long as you aren't using any multi-threading (through a DoEvents or another technique), or asynchronous ADO calls, you can use a global variable holding your connection object.

If you're using (shudder) data controls, then this may not apply, as they do some odd things. If you are using data controls, take some time to learn ADO, as (1) You will be better prepared for .NET, and (2) Have more control over when objects/connections get created (which helps in situations like this).

The data itself is only 5,948 bytes so should only take a second or so over a 56K line.

Size matters not, young Jedi!


Actually, once you get authenticated, the normal expectations of how long it takes to transmit data over a modem take over. So 5.8k should be around .15 seconds (give or take)

Chip H.

 

[gordonl]

Thanks again,

I do use ADO commands and not data controls and the database connection is declared as a public variable. So what you are saying is that I should not get vaildated each time??

I tested the program over 56K using RAS on NT server and I am 99.9% sure it worked OK. However, the company has now switched to a VPN, so I do not know if this acts in a different way.

I am now trying to set up a test NT computer with RAS so I can see if it works OK. However, I am having problems trying to get the NT server to validate my user name and password. NT is not my strong point so I am away to look through the NT Threads...

 

[gordonl]

After a day of messing about I managed to log onto a test NT server using RAS and I found that the exact same program that was taking 10 mins to using the VPN connection, took 30 secs for the first time using RAS, and then 2 secs each other time - even if you shut the program completely down.

Still do not understand why, buy I have proved my program is workable using 56K.

Thanks for your suggestions.