Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.os.linux.advocacy > #350517

Re: Fabian: 10,000 addresses processed in 0.53 seconds with VBScript

Show all headers | View raw

On 4/17/2016 9:33 AM, Norman Peelman wrote:


>    *Untested*, but see if this is any faster... This is your VB, just
> rearranged a bit. If it fails on the recordset stuff try just reverting
> the Open command to yours and reversing the comments on the
> .Update/.UpdateBatch methods.

After a few small fixes (noted in the code below), it runs in 0.33 
seconds, so good improvement from 0.53.



> #1 - Skips intermediate output file by writing to the 'dictionary'
> (removing duplicates) and the recordset at the same time.

Not long after the first post in this thread, I came to my senses and 
rewrote it to skip 2 unneeded file writes.  That code is in the thread: 
"Fabian: now I'm processing 10,000 addresses in 0.33 seconds"

See that post for a virtual $1 million if you can speed it up even further.



> #2 - Uses the recordset .UpdateBatch method instead of .Update

I'll have to remember that one.


> 'process_addresses.vbs
>
> Option Explicit
> Dim fso, dict, strKey, strLine
> Dim fIn, addressFile, addrList
> Dim fOut, newAddressFile
> Dim delim, business, street, citystatezip
> Dim startTime, endTime, addressCount
> Dim tmpkey As String

Compilation error: VBScript doesn't accept different data types.  In 
case you didn't know, they're all Variants.

I dropped the 'as String'


> Const ForReading =  1
> Const ForWriting =  2
> Const AdVarChar  =  200
> Const MaxCharacters =  255
>
> startTime = Timer
>
> '-----------------------------------------------------------
> 'OBJECTS
> '-----------------------------------------------------------
> Set fso      = CreateObject("Scripting.FileSystemObject")
> Set dict     = CreateObject("Scripting.Dictionary")
> Set addrList = CreateObject("ADOR.Recordset")
>
>
> '-----------------------------------------------------------
> 'SETTINGS
> '-----------------------------------------------------------
> delim = ";"  'don't use comma - code leaves 'City, State Zip' format in
> place
> addressFile    = WScript.Arguments.Item(0)
> newAddressFile = WScript.Arguments.Item(1)
>
>
> '-----------------------------------------------------------
> 'TRANSFORM DATA TO ONE ROW PER ADDRESS
> '-----------------------------------------------------------
> set fIn  = fso.OpenTextFile(addressFile , ForReading)
>
> addressCount = 0
> addrList.Fields.Append "Address", AdVarChar, MaxCharacters
> addrList.Open ,,, adLockBatchOptimistic,

Compilation errors: I removed trailing comma, and had to declare 
adLockBatchOptimistic as a Const (or put 4 in its place)


> Do until fIn.AtEndOfStream
>
>    business = fIn.ReadLine
>    fIn.SkipLine
>    strLine = Replace(fIn.ReadLine," Call Now!","")
>    street = Left(strLine, InStr(strLine,",")-1)
>    citystatezip = Mid(strLine, Len(street) + 3)
>
>    'Read file - removing dupes and populating recordset at same time
>    tmpkey = business & delim & street & delim & citystatezip
>    If Not dict.ContainsKey(tmpkey) Then

Runtime error.  VBScript dict object uses Exists method.  I replaced.


>      dict.Add tmpkey, business & delim & street & delim & citystatezip
>      addrList.AddNew
>      addrList("Address") = tmpkey
>      'addrList.Update
>    End If
>
>    addressCount = addressCount + 1
>
>    If Not fIn.AtEndOfStream Then fIn.SkipLine
>    If Not fIn.AtEndOfStream Then fIn.SkipLine
>
> Loop
>
> fIn.Close
>
> addrList.UpdateBatch
> addrList.Sort = "Address"
> addrList.MoveFirst
> strLine = ""
> Do Until addrList.EOF
>    strLine = strLine & addrList.Fields.Item ("Address") & vbCrLf
>    addrList.MoveNext
> Loop
> addrList.Close
>
> Set fOut = fso.OpenTextFile(newAddressFile , ForWriting)
> fOut.WriteLine("#Name" & delim & "Street" & delim & "CityStateZip" &
> vbCrLf)
> fOut.WriteLine strLine
> fOut.close
>
> '-----------------------------------------------------------
> 'FINISHED
> '-----------------------------------------------------------
> endTime = Timer
> WScript.Echo "Processed " & addressCount & " addresses in " &
> FormatNumber(endTime - startTime,2) & " seconds"
>

Back to comp.os.linux.advocacy | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Re: Fabian: 10,000 addresses processed in 0.53 seconds with VBScript Norman Peelman <npeelman@cfl.rr.com> - 2016-04-17 09:33 -0400
  Re: Fabian: 10,000 addresses processed in 0.53 seconds with VBScript DFS <nospam@dfs.com> - 2016-04-17 14:30 -0400
    Re: Fabian: 10,000 addresses processed in 0.53 seconds with VBScript Norman Peelman <npeelman@cfl.rr.com> - 2016-04-17 18:21 -0400

csiph-web