meier-online

[Zu Teil 1]

Verknüpfungen mit dem SQL-join

Relationale Datenbanken erlauben es, Tabellen miteinander zu verknüpfen. In SQL gibt es für diese Verknüpfung das Schlüsselwort “JOIN”. Eine solche Verknüpfung kann theoretisch sehr frei spezifiziert werden, in der Praxis wird man fast immer an der Gleichheit bestimmter ID-Spalten verknüpfen.

In unserem Beispiel eines Schiffsinformationssystems haben wir eine Tabelle”vessels” mit den Schiffsdaten und eine Tabelle “countries” mit den Daten zu einem Flaggenstaat. Typischerweise wollen wir die Schiffsdaten zusammen mit der Daten des dazugehörigen Flaggenstaates ausgeben. Dazu verknüpfen wir die Tabellen an Hand des Fremdschlüssels “legal_country_id”.

In unserem Beispiel liefert der SQL-Join eine Tabelle, in der Schiffe gleich mit den dazugehörigen Flaggenstaaten eingetragen sind. Die Daten der Tabelle kann man zum Beispiel auf einer Übersichtseite darstellen. Es ist nur eine Datenbankabfrage nötig, obwohl man Daten aus zwei Tabellen anzeigt.

Es stellt sich dann allerdings die Frage, was eigentlich mit Schiffen ohne Flaggenstaat passiert?

Um das steuern zu können gibt es verschiedene “Geschmacksrichtungen” des Joins:

INNER JOIN: Beide Datensätze müssen vorhanden sein. Wenn einem Schiff (noch) kein Flaggenstaat zugewiesen wurde, wird es auch nicht geliefert.

OUTER JOIN, LEFT JOIN: Es wird auch dann ein Ergebnis geliefert, wenn es keinen verknüpften Datensatz gibt. Es werden also auch Schiffe ohne Flaggenstaaten geliefert. Dabei werden Daten aus der ersten (in Leserichtung linken) Tabelle geliefert.

RIGHT JOIN Es wird auch dann ein Ergebnis geliefert, wenn es nur in der zweiten Tabelle einen Datensatz gibt. Ein RIGHT JOIN würde also Staaten liefern, in denen kein Schiff registriert sind. In der Praxis ist das öfter verwirrend als nützlich, so dass man besser die Reihenfolge umdreht und dann einen LEFT JOIN nimmt.

Wie nutzt man join von Rails

Zunächst gibt es in Ruby schon eine Methode, die “join” heißt, die aber semantisch etwas ganz anderes macht. Sie verbindet die Elemente eines Array zu einem String:

[1, 2, 3, 4].join(‘-’)
’1-2-3-4′

Zusätzlich bietet ActiveRecord bzw ActiveRelation eine Methode “joins”. Wichtig ist das “s” am Ende. Die Methode sieht zunächst sehr vielversprechend aus.

company = Company.find_by_name(‘Hapag-Lloyd’)

company.container_vessels.joins(:legal_country)

SQL:

SELECT “container_vessels”.*

FROM “container_vessels”

INNER JOIN “countries” ON “countries”.”id” = “container_vessels”.”legal_country_id”

WHERE “container_vessels”.”company_id” = 3

Leider liefert die Magie von Ruby-on-Rails diesmal nicht das gewünschte. Es wird zwar ein SQL-Join erzeugt, die Spalten der anderen Tabelle werden jedoch weggeschmissen. Active-Record ist ein sogennanter Object-Relation-Mapper (ORM), und als solcher darauf optimiert, immer Objekte einer Klasse zu liefern, in unserem Fall also ContainerVessel. Wenn man später auf die verknüpften Objekte zugreift, werden diese doch wieder einzeln geholt.

ActiveRecord-Abfragen liefern Objekte der jeweiligen Klasse zurück!

Einziger Effekt des generierten INNER JOIN ist, das nur Schiffe mit existierenden Flaggenstaat geliefert werden.

Das zeigt auch auf, wofür die joins() Methode trotzdem gut: Man kann mit ihr Objekte aufgrund von Eigenschaften von verknüpften Objekten auswählen.

Beispielsweise kann man so alle Schiffe einer Reederei auswählen, die einen EU-Flaggenstaat haben.

hapag.container_vessels.joins(:legal_country).where(‘countries.in_eu’ => ‘true’)

hapag.container_vessels.joins(:legal_country).where(:countries => {:in_eu => true})

SELECT “container_vessels”.*

FROM “container_vessels”

INNER JOIN “countries” ON “countries”.”id” = “container_vessels”.”legal_country_id”

WHERE “container_vessels”.”company_id” = 3 AND “countries”.”in_eu” = ‘t’

Der SQL-Join führt dazu, das die passenden Datensätze schon in der Datenbank ausgewählt werden, der Rails-Prozess sieht die Country-Datensätze noch nicht mal. Und was nicht da ist, kostet keinen Hauptspeicher und auch keine Performance.

In der Syntax muss man aufpassen: In der Spezifikation der Bedingungen bewegt man sich auf SQL-Ebene (‘countries’), während man in der joins-Methode die Rails-Association angibt(‘legal_country’).

(Demnächst geht es weiter mit echten Joins in Rails…)

Reduce the number of database queries

A typical web application does many database queries before it delivers the response page to the web browser. The application needs to wait for the response of each of these database queries. It gets an additional slow down because of process switching. The database server must analyze each request, and also the communication between the database and the application takes time. So less database queries reduce the overall load on the system. The system scales better.

Container vessels and flag states

s an example, we implement a marine information system. The data model includes shipping companies (companies), vessels (container_vessels) and flag states (countries). A shipping company has a number of vessels, each vessel is registered in a flag state.

class Company < ActiveRecord::Base
    has_many :container_vessels
end
 
class ContainerVessel < ActiveRecord::Base
    belongs_to :company
    belongs_to :legal_country, :class_name => 'Country'
end

We now want to show a vessel table for a given shipping company. The table should contain all their vessels together with the flag state of the vessel. A straight-forward implementation just uses the associations of the model.

In the controller, the vessels will be retrieved:

@company = Company.find(params[:company_id])
@container_vessels = @company.container_vessels.order(:name)

And in the view we just access to the country through the association

<% @container_vessels.each do |vessel| %>
    <td><%= vessel.name %></td>
    <td><%= vessel.legal_country.name if vessel.legal_country.present? %></td>
<% end %>

The solution works and is easy to understand. Let’s see what database queries are triggered:

SELECT "companies".* FROM "companies" 
        WHERE "companies"."id" = ? LIMIT 1 [["id", "3"]]
SELECT "container_vessels".* FROM "container_vessels" 
        WHERE "container_vessels"."company_id" = 3 ORDER BY name
SELECT "countries".* FROM "countries" WHERE "countries"."id" = 4 LIMIT 1
SELECT "countries".* FROM "countries" WHERE "countries"."id" = 10 LIMIT 1
SELECT "countries".* FROM "countries" WHERE "countries"."id" = 9 LIMIT 1

All vessels of a shipping company are retrieved in a swing with a single database query. The country data sets, however, are retrieved individually. The more countries, the more database queries. Obviously there is potential for improvement.

Eager loading

Active Record offers a simple improvement for these situations: the includes method. When we request the vessel-objects we are able to tell Rails that we are also interested in the countries-objects:

@container_vessels = @company.container_vessels.order(:name).
        includes(:legal_country)

That was all, no additional changes needed! As a result, these database queries are triggered::

SELECT "companies".* FROM "companies" 
        WHERE "companies"."id" = ? LIMIT 1 [["id", "2"]]
SELECT "container_vessels".* FROM "container_vessels" 
        WHERE "container_vessels"."company_id" = 2 ORDER BY name
SELECT "countries".* FROM "countries" 
        WHERE "countries"."id" IN (8, 7, 4)

So instead of retrieving each country individually, all countries are loaded in a single query (eager loading). The advantage can be huge for table-heavy information pages.

Unfortunately, there still are some problems:

1. The query of related records is difficult to customize. For example, one can not simply restrict the data fields with “select”.
2. The optimization potential is not exhausted. All vessel records are retrieved in one step, only then the country data sets are retrieved, and each is identified individually by ID. But SQL would make it possible to get the records together in only query.

So there is still enough material for the continuation of this article..