Deferred Columns

Overview

Minimizing the attributes, is one of the first optimization techniques used by db admins.

1. minimize number of calls
2. minimize number of tuples returned
3. minimize number of attributes in those returned tuples.

In this discussion we will be focusing on number 1 and 3.

Defer individual columns

This example creates two individually deferred columns.

class Items(Entity):
    itemnmbr = Field(Unicode, primary_key=True)
    itemdesc = Field(Unicode(80))
    qty = Field(Integer, deferred=True)
    note = Field(Binary, deferred=True)

for itm in Items.query.all():
    print itm.itemnmbr, itm.itemdesc #generates a single sql call for the undeferred attributes
    print itm.qty                    #generates a single sql call for the value of qty attribute
    print itm.note                   #generates a single sql call for the value of note attribute

Discussion

The sql calls happen automatically for you when you access the value of a deferred column. Each singly deferred column is retrieved with a separate sql call to the database. Normally, you wouldn't want to have more than 1 or 2 individually deferred columns since it would cause an excessive number of sql calls and those calls are normally expensive. The primary candidate for using a singly deferred column would be with Binary/Blob type columns. The the cost of the extra sql call is often outweighed by the savings you create by returning a smaller result. This type of savings is magnified if your data must travel over the wire instead of being accessed locally (Fewer packets). You save on memory too as you are only loading needed data when you actually need it.

Defer a group of columns

To create a group of deferred values, set the deferred attribute to a string instead of a boolean value. using the earlier code as an example:

class Items(Entity):
    itemnmbr = Field(Unicode, primary_key=True)
    itemdesc = Field(Unicode(80))
    qty = Field(Integer, deferred='deferreds')
    note = Field(Binary, deferred='deferreds')

Creates a deferred group named, 'deferreds' Then in the calling code import the undefer_group function and modify your query as such:

from sqlalchemy.orm import undefer_group
...
Items.query.all()                                     # return just the itemnmbr and itemdesc 
...
Items.query.options(undefer_group('deferreds')).all() # return all attributes in a single sql call
...

Discussion

The members of the 'deferreds' group will be queried along with the itemnmbr and itemdesc. This is helpful when you have multiple deferred columns as accessing a singly deferred column creates an isolated sql call. Using the original example, to get the values of all 4 columns, would require 3 sql calls. One call for the itemnmbr, itemdesc and then a separate call each for the two remaining attributes. By using a deferred group, all of the data is returned in a single sql call.

A deferred group is best used when you are accessing an object that you only use a few values from, most of the time. This allows you to minimize the time/space for most needs while still allowing you the flexibility to request all of the data for the object those times that you do need it.

The name for the deferred group is set in the deferred attribute. You may have 0 or more deferred groups. You can mix deferred groups and singly deferred columns.

Caveats

Currently, using :

• sa.__version__ == '0.4.3'
• elixir.__version__ == '0.5.1'

There is a problem using deferred groups with a class the implements the synonym property in one or more of the columns. Use of a singly deferred column works as expected but trying to implement a deferred group results in an AttributeError: SynonymnProperty has not attribute 'strategy' Click here for more information