[Snort-devel] Ideas about DB logging

Kreimendahl, Chad J Chad.Kreimendahl at ...1167...
Mon Apr 15 10:02:01 EDT 2002


Mostly I'm going to be talking about placeholders, preparing, binding, and
execution... And my ideas about improving performance.

My primary concern is there is no real use of prepare once, bind and execute
multiple.   The use of this should greatly improve performance of all
queries done on the database.  Being that I'm an Oracle guy, I'll use that
in my examples.  My first being:  In the Insert function, and for every
single insert, a statement is prepared and then executed.   This works fine
under very small load, but can cause problems for Oracle if you're trying to
insert too much too fast.  If, on the other hand, we do a single
OCIStmtPrepare (for each unique query), at the time just prior to
connection... And then in the Insert function, simply Bind and Execute the
data... There would be a vast performance improvement.   Now, admitedly,
nobody's likely to ever log fast enough into Oracle to bring it to its
knees.

So, we'd prepare, in the beginning... Statements such as the following:

Let's assume
Sql = INSERT INTO event (sid, cid, signature, timestamp) VALUES (:sid, :cid,
:signature, :timestamp)

To be done just prior to connection:
	OCIStmtPrepare(...., sql,....)

To be done every time Insert() is called:
	OCIBindByName(.., &<sid>, .., ":sid", .....)
	OCIBindByName(.., &<cid>, .., ":cid", .....)
	OCIBindByName(.., &<signature>, .., ":signature", .....)
	OCIBindByName(.., &<timestamp>, .., ":timestamp", .....)

	OCIStmtExecute(...)


The only real simple way I see to do this, is to add another dimention to
the array that is passed to the functions that do (would do) the binding and
execution.   Since we know what all the queries are going to be from the get
go... The prepares should be fine.  We'd have to add some basic info about
how many columns and how they're defined (their placeholder name ie...
":sid")... 

A quick look out there shows me that this is very possible even with the
other libraries (MySQL, Postgres, SQL Server...)


Some docs:
http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89857/oc
i04sql.htm#420200
http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89857/oc
i05bnd.htm#420516




More information about the Snort-devel mailing list