03 / 08

Code optimization example

Here is a concrete example that applies these ideas. You build a small app that stores a set of NFTs. Every NFT has a type plus some associated data, and each NFT belongs to at most one user. You can model it like this.

In Rell, that model looks like this:

src/main/entities.rell

entity nft {  key id: integer;  index type: nft_type;  data: byte_array;  mutable expired: boolean = false;}enum nft_type { A,B }struct nft_a {  condition: boolean;  data: integer;}struct nft_b {  data: byte_array;}entity nft_owner {  key nft;  index owner: user;}entity user {  key name;}

Every NFT has a unique ID and a type. The type maps to one of two structures, nft_a or nft_b, and each holds a different kind of data. User names must be unique. The nft_owner entity connects the two: it puts a unique constraint on the NFT and a joint constraint on the NFT and user together. As a result, one user can own many NFTs, while each NFT has only one owner.

Suppose you need a query that returns every type A NFT belonging to a given user for which some condition is true. The obvious approach might look like this:

src/main/queries.rell

query get_valid_a(user_name: name): list<(id: integer, data: integer)> {  val user = user @ { user_name };  val nfts = nft_owner @* { user }.nft;  return nfts @* { is_valid_a($) } (.id, data = nft_a.from_bytes($.data).data);}

src/main/functions.rell

function is_valid_a(nft): boolean {  if (nft.type != nft_type.A) return false;  if (nft.expired) return false;  val data = nft_a.from_bytes(nft.data);  return data.condition;}

This query loads the user, then loads each of that user's NFTs. It checks every NFT for validity and returns the matches as a list of named tuples that pair each ID with its data. The condition logic lives in its own function to keep the query short.

To see how much database work this query does, add a few helper operations:

src/main/operations.rell

operation create_user(name) {  create user(name);}operation create_nft(id: integer, type: nft_type, data: byte_array, owner_name: name) {  val owner = user @ { owner_name };  when (type) {    nft_type.A -> nft_a.from_bytes(data);    nft_type.B -> nft_b.from_bytes(data);  }  val nft = create nft(id, type, data);  create nft_owner(nft, owner);}

Next, write a test that creates one user and 10 NFTs, then runs the query:

src/test/masterclass_test.rell

@test module;import ^^.main.{ create_user, create_nft, nft_type, nft_a, get_valid_a };function test_nft() {    print("Creating user");    rell.test.tx().op(create_user("Alice")).run();    print("Creating nfts");    for (id in range(10)) {        rell.test.tx().op(create_nft(id, nft_type.A, nft_a(true, id*10).to_bytes() ,"Alice")).run();    }    print("Perform query");    get_valid_a("Alice");}

Run the test with SQL logging enabled:

shell

chr test --sql-log --tests test_nft

In the output, everything after the Perform query line looks like this:

sql

Perform querySELECT A00."rowid" FROM "c0.user" A00 WHERE A00."name" = ? ORDER BY A00."rowid"SELECT A02."rowid" FROM "c0.nft_owner" A00 INNER JOIN "c0.user" A01 ON A00."owner" = A01."rowid" INNER JOIN "c0.nft" A02 ON A00."nft" = A02."rowid" WHERE A01."rowid" = ? ORDER BY A00."rowid"SELECT A00."type" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."expired" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."type" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."expired" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."type" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."expired" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."type" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."expired" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."type" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."expired" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."type" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."expired" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."type" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."expired" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."type" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."expired" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."type" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."expired" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."type" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"SELECT A00."expired" FROM "c0.nft" A00 WHERE A00."rowid" = ? ORDER BY A00."rowid"

The query needed 22 round trips to the database. The first request fetches the user's rowid, and the second is a long query joining all three tables. After that, the same two lines repeat for each NFT. The number of database requests therefore grows linearly with the number of NFTs the user owns.

You can reduce this to one database query by joining all the tables up front and selecting every NFT field directly. The revised query:

rell

query get_valid_a(user_name: name): list<(id: integer, data: integer)> {  val nfts = (user, nft_owner) @* { // <-- 1.1 Join the tables directly    user.name == user_name,         // <-- 2. Criteria sorting, most distinct condition first    user == nft_owner.owner         // <-- 1.2 Join cont.  } (    id = nft_owner.nft.id,    nft = nft_owner.nft.to_struct() // <-- 3. select all fields from the nft (be more specific if needed)    );  return nfts @* { is_valid_a(.nft) } (.id, data = nft_a.from_bytes(.nft.data).data);}function is_valid_a(nft: struct<nft>): boolean { // <-- 4. Takes a struct as argument  if (nft.type != nft_type.A) return false;  if (nft.expired) return false;  val data = nft_a.from_bytes(nft.data);  return data.condition;}

The changes are:

The user and nft_owner tables are joined. This is safe because the user lookup returns at most one row.
The most specific criteria come first.
to_struct() selects all attributes in one step.
The helper function now receives an in-memory copy of the NFT, so it no longer triggers its own selects.

Running the test again gives this output:

sql

Perform querySELECT A03."id", A03."id", A03."type", A03."data", A03."expired"    FROM "c0.user" A00, "c0.nft_owner" A01    INNER JOIN "c0.user" A02 ON A01."owner" = A02."rowid"    INNER JOIN "c0.nft" A03 ON A01."nft" = A03."rowid"    WHERE (A00."name" = ?) AND (A00."rowid" = A02."rowid")    ORDER BY A00."rowid", A01."rowid"

The query now makes exactly one database round trip, no matter how many NFTs the table holds.

Written for this site from Chromia’s public course. Code samples are the course’s own.