---
title: Caching, Paging and Refreshing
order: 5
layout: page
---

[[sqlcontainer.caching]]
= Caching, Paging and Refreshing

To decrease the amount of queries made to the database, SQLContainer uses
internal caching for database contents. The caching is implemented with a
size-limited [classname]#LinkedHashMap# containing a mapping from
[classname]#RowId#s to [classname]#RowItem#s. Typically developers do not need
to modify caching options, although some fine-tuning can be done if required.

[[sqlcontainer.caching.container-size]]
== Container Size

The [classname]#SQLContainer# keeps continuously checking the amount of rows in
the connected database table in order to detect external addition or removal of
rows. By default, the table row count is assumed to remain valid for 10 seconds.
This value can be altered from code; with
[methodname]#setSizeValidMilliSeconds()# in [classname]#SQLContainer#.

If the size validity time has expired, the row count will be automatically
updated on:

* A call to [methodname]#getItemIds()# method

* A call to [methodname]#size()# method

* Some calls to [methodname]#indexOfId(Object itemId)# method

* A call to [methodname]#firstItemId()# method

* When the container is fetching a set of rows to the item cache (lazy loading)



[[sqlcontainer.caching.page-length]]
== Page Length and Cache Size

The page length of the [classname]#SQLContainer# dictates the amount of rows
fetched from the database in one query. The default value is 100, and it can be
modified with the [methodname]#setPageLength()# method. To avoid constant
queries it is recommended to set the page length value to at least 5 times the
amount of rows displayed in a Vaadin [classname]#Table#; obviously, this is also
dependent on the cache ratio set for the [classname]#Table# component.

The size of the internal item cache of the [classname]#SQLContainer# is
calculated by multiplying the page length with the cache ratio set for the
container. The cache ratio can only be set from the code, and the default value
for it is 2. Hence with the default page length of 100 the internal cache size
becomes 200 items. This should be enough even for larger [classname]#Table#s
while ensuring that no huge amounts of memory will be used on the cache.


[[sqlcontainer.caching.refreshing]]
== Refreshing the Container

Normally, the [classname]#SQLContainer# will handle refreshing automatically
when required. However, there may be situations where an implicit refresh is
needed, for example, to make sure that the version column is up-to-date prior to
opening the item for editing in a form. For this purpose a
[methodname]#refresh()# method is provided. This method simply clears all
caches, resets the current item fetching offset and sets the container size
dirty. Any item-related call after this will inevitably result into row count
and item cache update.

__Note that a call to the refresh method will not affect or reset the following
properties of the container:__

* The [classname]#QueryDelegate# of the container
* Auto-commit mode
* Page length
* Filters
* Sorting


ifdef::web[]
[[sqlcontainer.caching.flush-notification]]
== Cache Flush Notification Mechanism

Cache usage with databases in multiuser applications always results in some kind
of a compromise between the amount of queries we want to execute on the database
and the amount of memory we want to use on caching the data; and most
importantly, risking the cached data becoming stale.

SQLContainer provides an experimental remedy to this problem by implementing a
simple cache flush notification mechanism. Due to its nature these notifications
are disabled by default but can be easily enabled for a container instance by
calling [methodname]#enableCacheFlushNotifications()# at any time during the
lifetime of the container.

The notification mechanism functions by storing a weak reference to all
registered containers in a static list structure. To minimize the risk of memory
leaks and to avoid unlimited growing of the reference list, dead weak references
are collected to a reference queue and removed from the list every time a
[classname]#SQLContainer# is added to the notification reference list or a
container calls the notification method.

When a [classname]#SQLContainer# has its cache notifications set enabled, it
will call the static [methodname]#notifyOfCacheFlush()# method giving itself as
a parameter. This method will compare the notifier-container to all the others
present in the reference list. To fire a cache flush event, the target container
must have the same type of [classname]#QueryDelegate# (either
[classname]#TableQuery# or [classname]#FreeformQuery#) and the table name or
query string must match with the container that fired the notification. If a
match is found the [methodname]#refresh()# method of the matching container is
called, resulting in cache flushing in the target container.

__Note: Standard Vaadin issues apply; even if the [classname]#SQLContainer# is
refreshed on the server side, the changes will not be reflected to the UI until
a server round-trip is performed, or unless a push mechanism is used.__

endif::web[]