Structuring Your Code for Database Performance

When looping over a database with many items, it is important to consider the effects on performance. There are also some default limits imposed that can be confusing if you're not aware of them.

Default Limit on Iterators

Consider, for example, a database of 500 businesses. If you want to display all of them, you should use pagination; a page that displays 500 items will be slow to render.

Because of the load that big pages such as this can place on the server, we limit the number of items in iterators to 200. You can override this limit by setting the limit parameter.

For example:

<w:kb:item:each type="business">
  <p><w:name /></p>
</w:kb:item:each>

Will list out the names of the first 200 businesses due to the default limit. If you're expecting all of them, this will be mysterious.

Overriding the Default Limit (But Please Don't!)

You can force it to display more:

<w:kb:item:each type="business" limit="500">
  <p><w:name /></p>
</w:kb:item:each>

Paginating for Better Performance

But this is bad for performance, and not recommended. Much better to paginate the list:

<w:kb:item>
  <w:paginate type="business" limit="10">
    <p><w:name /></p>
  <w:paginate>
</w:kb:item>

This will cause just 10 items to be read from the database for each page, yet allow the visitor to page through all of them.

Put Conditions in the Iterator

If you want to display only items that meet certain conditions, be sure to put the conditions in the iterator. Suppose, for example, you have a "featured" checkbox for your business item, and you want to display only the featured listings. Here's the proper code:

<w:kb:item:each type="business" condition="featured=1">
  <p><w:name /></p>
</w:kb:item:each>

It may seem like the same thing to approach it like this, but it is problematic:

<w:kb:item:each type="business">
  <w:if condition="featured=1">
    <p><w:name /></p>
  </w:if>
</w:kb:item:each>

The problem with this approach is that the system must fetch every listing from the database, and then check to see if it is a featured listing. If there are 100 businesses, but only 10 are featured, this will require 100 passes through the loop. Putting the condition in the iterator, as in the previous example, makes the condition part of the database query, so only 10 passes through the loop are required, making it roughly 10 times as fast.

Also, note that with this approach, the default limit of 200 iterations applies. So if there are 300 businesses, the code above will show featured businesses only in the first 200 entries; the last 100 will not be tested. With the condition in the iterator, however, as in the previous example, it will find up to 200 featured businesses.