Database speed issues.

[Ghost21]

I'm pulling data from approx 200+ devices and I get a respone with computer name , and Description . I then go and ping the device first of all to see if its online or not and second to get a Response time hopefully in milliseconds... I then take the data from each PC and send it to a database where I then Query it back into a Combo Box.. for selection.. Now everything works great but I'm finding its taking approx 2:00mins to do all of this..

I do however open and close the database for every device that it adds to the database is there a way of slimming this down... ??

Local $oRS

Local $oConn

$oConn = ObjCreate("ADODB.Connection")

$oRS = ObjCreate("ADODB.Recordset")

$oConn.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=sw.mdb")

$oRS.Open ("DELETE * FROM ACTIVE WHERE PC='" & $PC & "'", $oConn, 1, 3)

$oRS.Open ("SELECT * FROM ACTIVE WHERE PC<>'" & $PC & "'", $oConn, 1, 3)

$oRS.MoveLast

$oConn.Execute("INSERT INTO ACTIVE (PC,USER,PING) VALUES ('" & $PC & "','"& $USER &"','"& $PING &"');")

$oConn.Close

$oConn = 0

Thanks

[weaponx]

Don't close the database connection till after the last computer is queried.

Pseudocode:

Open DB

Loop through systems

Retrieve results

Execute query / queries

EndLoop

Close DB

EDIT: Also why are you using "Open" with some queries and "Execute" with others? Are those "Open" queries even successful?

Edited January 18, 2008 by weaponx

[Ghost21]

Don't close the database connection till after the last computer is queried.

> Local $oRS

> Local $oConn

> $oConn = ObjCreate("ADODB.Connection")

> $oRS = ObjCreate("ADODB.Recordset")

> $oConn.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=\\cassiar\apps\systems\dbs\sw.mdb")

$oRS.Open ("DELETE * FROM ACTIVE WHERE PC='" & $PC & "'", $oConn, 1, 3)

$oRS.Open ("SELECT * FROM ACTIVE WHERE PC<>'" & $PC & "'", $oConn, 1, 3)

$oRS.MoveLast

$oConn.Execute("INSERT INTO ACTIVE (PC,USER,PING) VALUES ('" & $PC & "','"& $USER &"','"& $PING &"');")

> $oConn.Close

> $oConn = 0

so do I move those marked with a > or of the function ?

[weaponx]

That is correct. Also what is the point of the SELECT query? Nothing is returned.

[Ghost21]

That is correct. Also what is the point of the SELECT query? Nothing is returned.

Just to make sure that it doesn't overwrite a entry .. even thought the movenext statement is in there... Your right I probley don't need it..

[flyingboz]

you should have a function that opens the database, ideally returning a reference.

use that reference for any operations involving the open database.

you should have a function that closes the database.

i.e.

func _dbOpen ()
  ;open the db
  ;return $db_reference
endfunc

func _dbClose($db)
  ;close the db
endfunc

EndFunc

[weaponx]

Just to make sure that it doesn't overwrite a entry .. even thought the movenext statement is in there... Your right I probley don't need it..

But there is no condition, it just sends the SELECT result to Null doesn't it? Anyways since you have the DELETE query first there can't be a duplicate in there.

[Ghost21]

That's GREAT EVERYONE thanks for thw quick respones... I will work on this...

Thanks Again...