Speck CMS - A simple content management system for ColdFusion

you are here: home > documentation > optimising performance > database performance

• Getting Started
  • Speck Concepts
  • System Requirements
  • Installing
  • Upgrading
  • Hello Speck!
  • Articles
  • Caching
• Sample Application
  • Installing
  • Displaying Content
  • Managing Content
• Application Configuration
  • Settings
  • Keywords
  • Globalization
  • UI Customisation
• Building Applications
  • Application Initialisation
  • Creating Content Pages
  • Creating Content Types
  • Built-in Content Types
  • Built-in Property Types
  • Event Handlers
  • Context Sensitive Help
• Users and Security
  • Security Files
  • User Sessions
  • Access Control
  • Password Encryption
  • Reserved Roles
• Optimising Performance
  • Output Caching
  • Query Caching
  • Unnecessary Processing
  • Database Performance

Database Performance

A key feature of Speck is that it generally abstracts the database implementation from developers. Speck creates database tables and indexes for content types when applications initialise, and developers use Speck tags to retrieve and modify content. It supports multiple DBMSs by relying on stanard ANSI SQL where possible and using a databases configuration file to handle vendor specific requirements.

The dbtype application configuration setting determines which settings to take from the databases.cfg file to be used as the base database settings for the application. These settings can be overridden if required by adding a "database" section in the configuration file for an application.

To optimise your database performance you really need to know more about how Speck creates tables and indexes...

Tables and data types

Speck creates a database table for each content type, with a column for each property of the content type. There are performance, and other, considerations to take into account when choosing column data types. You'll have to refer to documentation for your database to compare different data types and how they impact on the efficiency of data retrieval, but seeing as Speck automatically creates the tables and columns, you should be aware of how it determines which data type to use for each property.

For text properties (Text, Html etc.), the data type of the column depends on the maximum length of the property and various database settings for the application. Most databases have a standard character varying data type with a maximum length of maybe a few thousand characters (usually varchar) and a character large object data type with a maximum length of millions of characters (clob, text etc.). To determine which column data type is to be used for a given property, Speck looks at the maximum length of the property and the maximum allowable length for a character varying type for the database. For example, if your database is SQL Server, the character varying type is VARCHAR, the long character varying type is TEXT and the maximum length of the VARCHAR type is 8000. If the column data type for a text property with a maximum length less than or equal to 8000 character will be VARCHAR(n), where n is the maximum length of the property. If the maximum length is greater than 8000, the column data type will be TEXT.

Note RE Unicode Support: Speck supports unicode character data if your ColdFusion server version is 6 or later, but by default it assumes that the character varying types can store unicode characters. If you need to override the default and force the use of national character varying types to achieve unicode support with your DBMS, you can do so by simply adding a database section to your application configuration file. For more information on unicode support, see the Globalization section.

For datetime/timestamp and numeric properties, the data type of the column depends on the tsDDLString, floatDDLString and integerDDLString values set for the database. All three are optional settings; if they are not explicitly set for the database, "timestamp", "integer" and "float" are used as the data types when creating columns. Your database may allow more specific types to be used where the range of values falls within certain limits. As part of your database optimisation process you could examine the property definitions in an application and consider changing the data type of some columns created by Speck if you think a more specific type, which might use less storage space and/or might be more efficient for data retrieval, could be used instead. For example, most databases use 4 bytes to store standard integer data, but if you know the allowed value range of an integer property in one of your content types is within -32768 and 32767, you might be able to change the column data type to something that only uses 2 bytes of storage (smallint, int2 etc.).

Indexes

An index provides the capability of associating a value in a column of a table with the stored locations of all the rows that contain the value. Indexes can greatly improve the speed of data retrieval, and you can use Speck to create them for your properties.

When Speck creates tables for content types, the primary key is always set to spId, spRevision. The DBMS should create an index for the primary key, enabling efficient retrieval of content based on matching id and revision. To ensure that the DBMS can use an index for retrievals based on matching label or keywords, Speck also creates an spLabelIndex column for each content type table (which stores an upper-case version of the label to allow an index on this column be used even with case-sensitive databases) and an spKeywordsIndex table is used to track which keywords have been set for which content items. The keywords index table is not used for content retrieval by default, because a full table scan is probably faster than running a subquery to access the keywords index if the number of rows in the content type table is only a couple of hundred. To use of the keywords index for content retrieval, you can set the useKeywordsIndex application setting to yes to enable application-wide use of the keywords index, or you can use the useKeywordsIndex attribute of cf_spContent[Get].

Speck will also create indexes for properties where the index attribute of cf_spProperty is set to "yes". You can use this attribute to create indexes on columns that you reference when you use the where attribute of the cf_spContent[Get] tag to define your own search criteria. Speck creates a single index for each indexed property. Multi-column or composite indexes on more than one column are not created automatically, but you may wish to consider creating these yourself as part of your database optimisation process.

Speck uses standard ANSI SQL create index statements to create indexes, so the indexes take on the default characteristics for the DBMS. This is usually fine, but it may not always be the optimal solution. Most DBMSs default to using a balanced tree index, but for columns that are to be used only for equality matches, a hash index would probably provide better performance, although not all DBMSs support hash indexes. The index on the spLabelIndex column is a perfect example, the index is only ever used for equality matches. Some DBMSs also allow one of the indexes on a table to define the order of the table. A column which is frequently used for sorting or range searches, like the publication date of an article, might be well suited to being indexed in such a way. Again, Speck does not create these kinds of indexes automatically but you may wish to consider creating them as part of your optimisation process.

If you need to create indexes with specific requirements, you can either drop the indexes Speck creates and re-create them according to your specific requirements, or you can add further indexes as required. Speck names the indexes it creates on properties using the name of the content type and the name of the property, so if you have a content type named "article" with an indexed property named "title", Speck will create an index named "article_title".