OSCore Database Tips

The PropertySet implementations found in OSCore persist data in a relational database. Because the PropertySet module stores a wide variety of data that is unknown at development time, the resulting tables are not entirely in optimized (Boyce-Codd) normal form. Since the schema design is not "optimized" to any sorta of specific data structures, querying on these tables can be very slow if the database isn't aware of any functional dependecies. We recommend that you create the following indices on the OS_PROPERTYENTRY table, which will speed up queries by up to a factor of 20:

	CREATE UNIQUE INDEX os_PropertyEntry_keyidx ON os_PropertyEntry( entityName, entityId, key )
	CREATE UNIQUE INDEX os_PropertyEntry_allidx ON os_PropertyEntry( entityName, entityId )

Please note that these two SQL calls may or may not work with your database vendor and you may be required to modify them accordingly.

Besides indices, another speed optimization to take in to account is the key names that you choose when writing your application. If all your keys all look like com.acme.foo, com.acme.bar, and com.acme.baz, your database may not be able to properly partition data in the OS_PROPERTYENTRY table accurately. It is recommended that your key values be chosen such that they are evenly distributed, either by picking names such as foo, bar, and baz (essentially removing the common prefix), or my using a reverse key naming convention: oof.emca.moc, rab.emca.moc, and zab.emca.moc. By properly distributing your keys, your database should be able to have much faster access to ProperySets.