Theo Ekelmans (11/13/2012)
LOL, gimme thor class hammer!!!The project i'm working on is a roof of concept SNMP MIB database, that is so fast it can do (near)realtime SNMP data packet to SQL data conversion
Just in case your new to the extensible SNMP MIB tree:
If E-mail addresses were OIDs... user@ordina.org would have been something like: user@ordina.enterprises.private.internet.dod.org.iso
or in non-translated notation: user@99999.1.4.1.6.3.1
except that we write the top-most part at the left: 1.3.6.1.4.1.99999.117.115.101.114
An OID is just a unique key (within one managed device) for one piece of information Ensures vendors don't have conflicting OIDs
OID corresponds to a label .1.3.6.1.2.1.1.5 => sysName (in windows this is the servername)
The complete (translated) path: .iso.org.dod.internet.mgmt.mib-2.system.sysName
The info needed to get from one to an other is "mib (database) files", that are supplied with each new device / software version.
The provided MIB files all have a part of the ginormous jigsaw MIB tree, and that needs to be searched between 500 to 20.000 times a minute.
Fun project indeed 🙂
I tried this once, so I feel your pain.
The best I got was to store the OIDs flat in an indexed VARCHAR(1000) column and my metadata sparsely around that. Given that OIDs are essentially a materialized-path hierarchy, anyway, I was able to do parent>child navigation by `WHERE oid LIKE @parent_oid + '%'` and child>parent by `WHERE oid = LEFT(@child_oid, LEN(@child_oid) - CHARINDEX('.', REVERSE(@child_oid)))` (plus/minus a 1, here or there!).
Maybe you could get some benefit from the binary approaches Jeff is using instead of the string-based approach I once took. I can't remember if there is an upper limit to the size of each 'part' of an OID but, if I were to guess, it would be a 0-64K 'word', which would convert to SMALLINT and on to BINARY(2) well enough (if it is actually a 0-4G dword, you would need to convert to INT and on to BINARY(4)).
As part of your MIB-loading routine, split the OIDs by '.' [Jeff's earlier article on string-splitting (and the great discussion around it) at http://www.sqlservercentral.com/articles/Tally+Table/72993/ might be of help here], then convert each word to SMALLINT, then BINARY(2) and store the materialized path as concatenations of BINARY(2) into a indexed VARBINARY(2000).
With a materialized path of fixed-size binary elements instead of variable-length string elements, you could get 'level' metadata from `DATALENGTH(oid) / 2`, child>parent from `SUBSTRING(@oid, 1, DATALENGTH(@oid) - 2)` and parent>child from `WHERE SUBSTRING(oid, 1, DATALENGTH(@parent_oid)) = @parent_oid`.
If binary sorts are that good (and people here give me every reason to think that they are), you may be performant enough at this point.