Script advice

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Script advice

Logged in as: Guest

arrSession:exec spGetSession 2,2,51637

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Script advice
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Script advice - 9/6/2007 9:50:36 PM

chris_cs

Posts: 95
Score: 0
Joined: 10/9/2006
Status: offline

Guys,

I have written a script that will update a database with a load of values from another but when it runs it uses 100% CPU. When I try and invoke this script from within IE it causes IE to ask if I want to end the script because IE may become unresponsive.

Each of the tables in the separate DBs have a filed that I'm using as an identifier to compare. I'm using the getRows() method to get values from each table and then loop through the data to find matching values.
When a match is found the required values are entered into the required DB table.

The script I'm using is shown below:

[/font]
[font=arial]set conn = createobject("ADODB.Connection")
conn.open dsn, dbuser, dbpass[/font]
[font=arial]strsql = "SELECT AssetItem, NetBookValue, LastDepnAmount FROM AR_01_MainAssetHeaderFile order by AssetItem ASC"
Set oRs1 = Conn.Execute(strsql)
records = oRs1.getRows()
set conn = nothing[/font]
[font=arial]set conn=CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Server4\wwwroot\Dev\hawkeyeOld\hawkeye.mdb"
strsql2 = "SELECT assetNo FROM tblAsset1 order by assetNo ASC"
set oRs2 = conn.execute(strsql2)
p = oRs2.getRows()
'set conn = nothing[/font]
[font=arial]'msgbox records(0, 0)
dim total : total = 0
dim iRowLoop, iColumnLoop, pRowLoop, pColumnLoop
for iRowLoop = 0 to Ubound(records, 2)
for iColumnLoop = 0 to 0
 for pRowLoop = 0 to Ubound(p, 2)
  for pColumnLoop = 0 to Ubound(p, 1)
   if p(pColumnLoop, pRowLoop) = records(iColumnLoop, iRowLoop) then
    'total = total + 1
    strsql3 = "UPDATE tblAsset1 SET NetBookValue = '" & records(1, iRowLoop) & "', LastDepnAmount = " & records(2, iRowLoop)
    strsql3 = strsql3 + " WHERE assetNo = " & p(pColumnLoop, pRowLoop)
    set oRs3 = conn.execute(strsql3)
   end if
  next
 next
next
next
set conn = nothing
msgbox "Done"

Basically I'm asking if there are any other ways I could do this to use less CPU? Any advice would be much appreciated.

Post #: 1

RE: Script advice - 9/7/2007 12:27:45 AM

CaffeineAddiction

Posts: 144
Score: 0
Joined: 2/9/2005
From:
Status: offline

I remember back in the day when I wrote programs in vb 6 instead of VBS there was a command that in so many words pauses your program and allows the OS to catch up before it was passed back control again. Im not sure if that functionality exists in vbs ... although a work around that you could try would be a wscript.sleep 1 somewhere in one of your loops. This would basicly pause your program for 1ms on every time it was hit in the loop, thus freeing up some cycles for the browser / os to catch up instead of just hanging untill its done.

Edit: Confirmed

vs

press [ctrl] + [alt] + [del] and watch the diff in processor cycle maxing on the first and not maxing on the second

It will take a little longer to run the over all process ... but it shouldnt hang your computer

< Message edited by CaffeineAddiction -- 9/7/2007 12:45:32 AM >

_____________________________

Thanks for Answering my Questions ... I hope my Answer to your questions help!

(in reply to chris_cs)

Post #: 2

RE: Script advice - 9/7/2007 12:37:08 AM

chris_cs

Posts: 95
Score: 0
Joined: 10/9/2006
Status: offline

Thanks for the advice. I'll give this a go and let you know how I get on.

(in reply to chris_cs)

Post #: 3

RE: Script advice - 9/7/2007 1:07:14 AM

chris_cs

Posts: 95
Score: 0
Joined: 10/9/2006
Status: offline

Ok I've given this a try and it has a massive effect in the processing used. The script takes alot longer to run but that's fine.

The problem I have now is that when I invoke my script from my asp page it has an issue with the wscript command.

Any ideas? I think I won't be able to use this command as it cannot be used asp

(in reply to chris_cs)

Post #: 4

RE: Script advice - 9/7/2007 1:40:11 AM

CaffeineAddiction

Posts: 144
Score: 0
Joined: 2/9/2005
From:
Status: offline

I dont know anything about asp, but this => http://classicasp.aspfaq.com/general/how-do-i-make-my-asp-page-pause-or-sleep.html might help

< Message edited by CaffeineAddiction -- 9/7/2007 1:44:07 AM >

_____________________________

Thanks for Answering my Questions ... I hope my Answer to your questions help!

(in reply to chris_cs)

Post #: 5

RE: Script advice - 9/7/2007 1:45:17 AM

chris_cs

Posts: 95
Score: 0
Joined: 10/9/2006
Status: offline

Thanks for the link but I think I may have to admit defeat on this unless anyone else has any other ideas?

I don't really have the time to start looking into 3rd party products.

(in reply to chris_cs)

Post #: 6

RE: Script advice - 9/7/2007 2:36:09 AM

ebgreen

Posts: 5250
Score: 31
Joined: 7/12/2005
Status: offline

Does it have to run in IE? Would it be possible to pull the DB update out into a seperate VBS?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to chris_cs)

Post #: 7

RE: Script advice - 9/7/2007 2:44:43 AM

chris_cs

Posts: 95
Score: 0
Joined: 10/9/2006
Status: offline

Ideally I'd invoke the script from within IE using :

<script language="vbscript" src="depData.vbs">
</script>

Then reference the function but if I'm unable to do this I'll have to get theuser to execute a script.

(in reply to ebgreen)

Post #: 8

RE: Script advice - 9/7/2007 3:18:36 AM

CaffeineAddiction

Posts: 144
Score: 0
Joined: 2/9/2005
From:
Status: offline

Other things i saw while doing a google search for asp delay / sleep encluded people creating java or python functions and executing them from asp ... might be worth a try, but again no idea ... asp is something i have never realy messed with all that much

_____________________________

Thanks for Answering my Questions ... I hope my Answer to your questions help!

(in reply to chris_cs)

Post #: 9

RE: Script advice - 9/7/2007 4:55:07 AM

Rischip

Posts: 510
Score: 2
Joined: 3/26/2007
Status: offline

quote:

Guys,

I have written a script that will update a database with a load of values from another but when it runs it uses 100% CPU. When I try and invoke this script from within IE it causes IE to ask if I want to end the script because IE may become unresponsive.

For the unresponsive script popup in IE look here http://support.microsoft.com/kb/175500

I set mine to something like ffffffff

This basically gets rid of it, but keep in mind other scripts won't timeout effectively any longer either. If it's not one thing it's another.
Microsoft's method of getting rid of Global variables in code is to put them in the registry.