Jump to content
Sign in to follow this  
Ghost21

Database speed issues.

Recommended Posts

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

Share this post


Link to post
Share on other sites
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 by weaponx

Share this post


Link to post
Share on other sites
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 ?

Share this post


Link to post
Share on other sites
weaponx

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

Share this post


Link to post
Share on other sites
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..

Share this post


Link to post
Share on other sites
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


Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Ghost21

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

Thanks Again...

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×