Slow script - database connections

[Gaborone]

Hi,

I have a database connected to my script and there seem to be some problems with it.

Sometimes, it takes a very long time to get the data out of the database, so the hub "dies". The script stops executing, and sometimes a pop-up window shows, and says that the script takes too long to execute.

The database connection I use is the ADODB one.

I' tried a couple of different ways of handling the database connection, but none of them seems to solve the problem.

1. Never set either the connection object nor the Recordset object to "Nothing"

2. Always set both the connection object and the Recordset object to "Nothing" after each query or update of the database.

3. Always have the connection object open, but set the Recordset object to "Nothing" after each query or update of the database.

Most often, I get the "Script is taking too long time to execute" pop-up when starting the hub (I do a query from the main sub in the script), but I don't get it every time.

When about 250 users have connected to the hub, the script stops executing (I add information to the database on connection)

I check that the database is not busy before trying to add anything to it. If it is busy, I queue the information to be added, and let the user connect anyway.

I have tried different values on the ConnectionTimeout and the CommandTimeout properties of the connection object (10 seconds, 5 seconds and 1 second) but it doesn't seem to make any difference. I have code to deal with the error that should ocurr if the timeout is raised, but it won't execute.

Please, is there anyone who can help me?

/Gaborone

[ButterflySoul]

Ok, I never had any problems with my own DB (once the code was working, that is), but then again, I never had over 100 users, except for a stress of a few minutes =p

The fact that you end up 'taking too long to execute" might be due to an endless loop, which are very easy to get into when piloting a database via scripting, especially if you have an "On Error Resume Next" to try and get an error code.

So the first thing I'd suggest doing is to (temporarly) put a check for EOF and BOF in all your loops where you do something in the lines of :

Code: Select all

With (recordset object)
  Do until (something)
    (statements)
    .MoveNext
  Loop
End With

You might be looking for a record that doesn't exist, and "kill" the safeguards of .MoveNext with an "On Error Resume Next" statement.

Another case is if you have something like that :

Code: Select all

  Do Until x+1 = iParam
     .MoveNext
     x=x+1
  Loop

(iParam being a number that you don't know in advance, but determined earlier in the code, and x being a freshly Dimed variable)
With an "On Error Resume Next" statement, the code above will always loop endlessly (as I found out myself when I started using VBS with ADODB =)
The first reason for this being that you need to make a CInt on iParam. iParam alone will never work. So the first line would need to be :

Code: Select all

Do Until x+1 = cInt(aParam(1))

Even with the first fix in place, it might still loop endlessly if iParam happens to be 1 =) That's because even if the equality following an "Until" is true, the loop is executed at least once. And after one loop, x+1 is equal to 2, and will never be equal to iParam again. So, the proper way to handle this would be :

Code: Select all

   If cInt(aParam(1))>1 Then
      Do Until x+1 = cInt(aParam(1))
         .MoveNext
         x=x+1
      Loop
   End If

When you work with recordsets via ADO, these kind of loops (or slight variations) are quite common and sometimes inevitable. I don't know what your code looks like, but these are the problems I ran into when I wrote my own script, so I thought I'd share the solutions with you, in case you ended up with an analog problem.

-----

As far as the Diming and Seting is concerned, I found out that the way working the best was to :
- Dim a connection object (and recordset object if needed) at a sub level

- Never set the recordset to nothing. Just let it die like any variable when it gets out of scope.

- Permanent connections Dimed at script level are fine, but I used them all at Sub level without any problems. Since they end up as independant connections, it also lessens the risk of coliding with other Subs. By keeping them at Sub level, you can have a connection triggered by an OP command reading a user's record while at the same time, another connection trigered by a new login updates someone else's record.
(the catch being that you end up Diming and killing a lot of connection objects, but generally speaking, it's safer)

- Open the connection in a dedicated sub.
example :

Code: Select all

'----------
Sub Something()
'----------
Dim objDBCon
   OpenCon objDBCon, "file.mdb"

'----------
Sub OpenCon(objConnection, sFileName)
'----------
   Dim fso
   Set fso = CreateObject("Scripting.FileSystemObject")
   If fso.FileExists(".\Scripts\Config\"&sFileName) Then
      Set objConnection = CreateObject("ADODB.Connection")
      With objConnection
         .ConnectionTimeout = 10
         .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\Scripts\Config\" &sFileName
         .Open
      End With
   Else
      MsgBox "Warning... A database is missing. Couldn't open : " &sFileName,16,"Critical : Config Database Missing"
   End If
End Sub

- Always pass update queries directly as an SQL string, rather than retreiving a recordset, changing values, and using the update/batchupdate method to comit the changes (like it's done in Aphrodite, for example). Never request or use a recordset if you don't need to actually read values. Slam the command directly on the connection object, then continue your sub as if nothing ever happened (kind of a "Fire and Forget" mindset). Use the SQL command Update to change the field(s) of an existing record, Insert to create a record, and Delete to erase (a) record(s).
Resort to a recordset and the SQL Select command only if you need to actually read some values from the DB.
Example :

Code: Select all

Sub OpConnected (curUser)
   Dim objDBCon
   OpenCon objDBCon, "dcdb.mdb"
   objDBCon.Execute("Update UsrDynamic Set LastLogin=#" &Now &"#, LastIP=" &Chr(34) &curUser.IP() &Chr(34) &" Where UserName=" &Chr(34) &curUser.sName &Chr(34))
End Sub

- If your database often has new records added, or old records deleted, compact it every now and then. It will reduce its size, and improve perf (sometimes quite a lot, too). You can do so directly from your script by calling the Jet Replication Objects library, either in the Main Sub, while no connection is opened, either in another Sub, after a delete on several records (and after doing a .Close of your connection object, of course)
Example :

Code: Select all

Sub CompactDB(sFileName)
   Dim fso, JetEngine
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set JetEngine = CreateObject("JRO.JetEngine")
   JetEngine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=.\Scripts\Config\" &sFileName, "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=.\" &sFileName
   fso.CopyFile ".\"&sFileName, ".\Scripts\Config\"
   fso.DeleteFile ".\"&sFileName
End Sub

- If you're storing a lot of different infos about users in your database, don't store everything in the same table. Store the info which needs to be updated (last IP, last login, etc) in a dedicated table, and store the static info (such as Registration date, password, etc) in another table, to speed things up. Use relationships and enforce referential integrity to keep them in Sync.

------

If it doesn't solve anything, give us a bit of code to look at =)

[Tang]

Firstly I would recommend using mysql.

I've implimented a mysql connection from a hub script. My web server also uses the mysql database so I can essentially make a nearly seamless connection between the hub and the web server.

To use this script you'll have to download the mysql odbc driver.. select one from this list that supports your os. http://www.mysql.com/downloads/api-myodbc-3.51.html

Code: Select all

Dim l_DB, l_RS, bConnect

' set the db connection and recordset objects.
SET l_DB = CreateObject("ADODB.Connection")
SET l_RS = CreateObject("ADODB.Recordset")

Sub Main()
    ' connect to a local database.
    l_DB.Open "DRIVER={MySQL ODBC 3.51 Driver}; DESC=; DATABASE=database_name; SERVER=localhost; UID=root; PASSWORD=password; PORT=3306; OPTION=; STMT=;"

    ' if the connection was complete.
    If l_DB.Errors.Count = 0 Then
        Set l_RS = l_DB.execute("SELECT * FROM table WHERE something=0 ORDER BY RAND() LIMIT 1;")
        msgbox l_RS("ID").Value
    End If

    ' set the connected state to true.
    bConnect = True

End Sub

A few small things to note with the example I gave above. If the database connection was not complete (if connecting to an offline remote database or something) the script will drop at the connection line. So put it at the end of your Sub Main and then set a variable to true if connected.. if the connection failed then bConnect will = false. If the connection is a success bConnect will = true.

hope that helps a few people. =).