I need a FAST dictionary or alternative. 3

[Geates]

I'm creating a basic asset managment HTA for my organization. When a user logs onto a domain computer, a logon script audits the machine for system information and stores it in a SQLDB.

A portion of the program displays asset attributes and the "as of" timestamp. Currently, I'm storing the attributes and timestamps in a dictionary object to make use of the key=>value context. My current code does what it needs to do in .0156 secs per asset. However, when you got over 1500 assets, it tends to slow things down.

Aside from limiting the number of assets displayed, can anyone offer some insight on speeding up this code? Or an alternative solution?

set dicItems = CreateObject("Scripting.Dictionary")

for j = 0 to ubound(arrRows) - 1
   arrItem = arrRows(j)
   if (arrItem(0) = intAssetPK) then
      if (dicItems.Exists(arrItem(1)) = false) then
         dicItems.Add arrItem(1), arrItem(2)
      end if
   end if
next

NOTE: An assets list of attributes is dynamic and created based on the information available at audit.

NOTE: My DB is an EAV and thus has little to no literal context (just numbers pointing at numbers ).

-Geates

 

[HughLerwill]

for j = 0 to ubound(arrRows) - 1
arrItem = arrRows(j)
if (arrItem(0) = intAssetPK) then
On Error Resume Next
dicItems.Add arrItem(1), arrItem(2)
On Error Goto 0
end if
next

may be quicker

 

[strongm]

Have you considered a disconnected recordset?

 

[mrmovie]

good suggestion HughLerwill.

Geates, I presume the issue is the amount of time this script / process takes at user logon? I would suggest a few options?

+ have your audit script seperate from the logon script. have the logon script call your audit script with WshShell.Run 1, False, e.g. dont wait for it to finishs, it can carry on running the background and the user isnt delayed in logging on the system

+ remove this audit process from the logonscript. have it part of startup script? group policy script? scheduled job? sccm re-occuring package? etc


I Hear, I Forget
I See, I Remember
I Do, I Understand

Ronald McDonald

 

[strongm]

>I presume the issue is the amount of time this script / process takes at user logon?

Not from the way the OP described it. He has a (seperate) logon script that performs an audit and stores the data in a SQL database. The asset management program then later extracts data from this database, consolidates it and displays it. And it is the handling of multiple assets from the DB that slows things down.

At least that's the way it looks to me.

 

[mrmovie]

thanks strongm, i re-read the post and you are correct.


I Hear, I Forget
I See, I Remember
I Do, I Understand

Ronald McDonald

 

[Geates]

HughLerwill, nice simple suggestion. Removing the "dic.exists" command would save repeative dictionary interations (assuming I correctly understand the algorithym for dic.exists)

strongm, I am unfamiliar with a disconnected recordset. However, with my lack of knowledge on the concept, I can't see how it would save time.

strongm, you are very correct about the process. I may even use your verbage in describing the process to my colleague.

Maybe I can get a speed boost by eliminating rows of data from the arrRows as they are processed - eliminating unecessary iterations. Although, I can foresee slowness caused by the constent redimensioning of the array.

- Geates

 

[tsuji]

If the built-in exists() were that counter-productive, that would give (wrong) reason for on error as a shortcut rather than more control.

In fact, no... Use exists() for pre-test is more effective in terms of cycles, let alone the rest.

 

[strongm]

>I can't see how it would save time.

Ok ... how do you currently populate arrRows (which is in turn being used to fill your dictionary)?

 

[Geates]

Perhaps...questionably? This following is the first and only algorithm I've used. Its worked well so I've had no incling to change it.

function query(strQuery)	
   objRecordSet.open strQuery, objConnection, 3, 3
   if (objRecordSet.EOF) then
      query = array(arrEmpty)
   else
      strRecords = objRecordSet.GetString
      if (len(strRecords) = 1) then
         query = array(arrEmpty)
      else
         arrRecords = split(strRecords, vbCr)
         for i = 0 to ubound(arrRecords) - 1
            strRecord = arrRecords(i)
            arrRecords(i) = split(strRecord, vbTab)
         next
         query = arrRecords
      end if
   end if 
   objRecordSet.close
end function

-Geates

 

[strongm]

Right - so you are querying the database, and then using the results of that query (albeit rather long-windedly) to populate an array.

And then you take that array and use it to populate a dictionary - and populating that dictionary is slow ...

But what if you could just work with the original recordset (or, say, a cloned subset)?

 

[Geates]

That would work well, however, the resulting arrRecords does not have column names. This is why I use a dictionary, for the associative properties.

Can you suggest an alternative query algorithm?

-Geates

 

[PHV]

What is the value of strQuery ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tsuji]

If you want speed that bad, you port the vbscript to simple a vb program with early binding. That would be the major factor to speed things up. If name/value pair is all that's needed for data manipulation, I don't see recordset will be that crucial besides making the script being decorated with a look of sophistication. It comes with a substantial overhead over dictionary... So the discussion seems sterile, as far as I am concerned.

 

[strongm]

>the resulting arrRecords does not have column names

>the resulting arrRecords does not have column names
Sure. You've effectively deliberately thrown that information away.

I'm guessing that strQuery reads something like:

"SELECT * FROM Assets"

or possibly

"SELECT AssetPK, Attribute, Timestamp from Assets"

So you've got a recordset (objRecordSet) which already has column names. And a recordset has Filter and Find methods. So, for example, if you only wanted the attributes for a particular asset, for example asset number 1:

objRecordset.Filter = "AssetPK=1"

You also do a bit of data reconcialition in your process, it would appear, to ensure that you only have one record of an attribute (presumably you'd want the most recent audit record, i.e the one with the latest timestamp, although your code

if (dicItems.Exists(arrItem(1)) = false) then

doesn't necessarily guarantee that.

I'd wrap that functiuonality up into the original query. If we assume your original strQuery is

"SELECT AssetPK, Attribute, Timestamp from Assets"

then I'd modify it to something like:

"SELECT DISTINCT AssetPK, Attributes, Max(Timestamp) AS MaxOfTimestamp FROM Assets GROUP BY AssetPK, Attributes"


These are just some pointers. There is plenty of info on the internet about using doisconnected recordsets, so you might like to do some research. If you get stuck or cannot find anything apprpriate ask again, and we can provide some further guidance

 

[Geates]

I'll explorer the other RecordSet methods. I might be able to squeeze some speed out of them.

The logical solution, as Tsuji pointed out, is to program it in a less restrictive language. However, the simplicity of this program doesn't warrant the change; we've got SCCM to do the heavy lifting.

Thanks you all for the input,

- Geates

 

[Geates]

PHV -
strQuery is the var containing the SELECT statement. e.g.

strQuery = "SELECT pk FROM Asssets"

-Geates

 

[PHV]

strQuery = "SELECT pk FROM Asssets"
How this query may return 3 columns per row ???

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Geates]

it doesn't. it's an example.
strQuery could be any SQL statement.

 

[strongm]

>with early binding

Early binding has little to do with the speed issues the OP has mentioned. It is related to the key hashing and the insertion speed of the dictionary object which, whilst faster than a collection, is still slow, particularly as the dictionary grows bigger. Early binding won't fix that.

>It comes with a substantial overhead over dictionary

Since the OP is already using ADO to get at the database this really isn't a concern.

>I don't see recordset will be that crucial

Assuming the 1500 assets that OP mentions and assuming an underwhelming 5 unique attributes per asset (and I'm guessing it is significantly more) ... using a recordset would avoids 7500 splits, and thus avoid the creation of 15000 strings for arrRecords (and string manipulation is a slow, expensive operation in VBScript), and then filtering against every element in that array to insert (potentially)numerous key/value pairs into a dictionary for each unique asset (again slow).

We can eliminate all of that with a disconnected recordset. Without providing all the support code (as per the OP) we can make Function Query something like:

[blue]Function query(strQuery)
   objRecordset.CursorLocation = 3 'adUseClient - required if we want to disconnect
   objRecordset.Open strQuery, objConnection, 3, 3
   Set objRecordset.ActiveConnection = Nothing ' Here's where we disconnect
   Set query = objRecordset.Clone
   objRecordset.Close
End Function[/blue]

which looks cleaner and shorter and is a bit faster than the current version. The difference is that it returns a doisconnected recordset containing all our asset info (our unique asset info if you use something like my suggested query above rather than a basic query)

You can now completely ignore the entire bit of code that sticks this into a dictionary, and thus the entire slow section.

We can retrieve values directly from the disconnected recordset in much the same way as we can from the dictionary object, only with much more flexibility and none of the overhead of populating the dictionary

Just want info on Asset with a PK vakue of 1?

objDiconnectedRS.Filter = "AssetPK=1"

Job done. Want to see what the Timestamp is for a particular attribute for that asset?

objDisconnectedRS.Find "Attribute='<myattribute>'
MsgBox objDisconnectedRS("MaxOfTimeStamp")