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):
    using_options(tablename="items")

    itemnmbr = Field(Unicode, primary_key=True)
    itemdesc = Field(Unicode(80))
    cost = Field(Integer, deferred=True)
    note = Field(Binary, deferred=True)

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):
    using_options(tablename="items")

    itemnmbr = Field(Unicode, primary_key=True)
    itemdesc = Field(Unicode(80))
    cost = 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.filter("itemdesc like :itemdesc").params(itemdesc=parm).options(undefer_group('deferreds')).all()
...

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.