What is the most efficient way to read tile data (MBTiles) from SQLite table by using AnyDAC (FireDAC)?

Background:

I'm working on a SQLite tile cache database (similar to MBTiles specification), consisting for now just from a single table Tiles with the following columns:

X [INTEGER] - horizontal tile index (not map coordinate)
Y [INTEGER] - vertical tile index (not map coordinate)
Z [INTEGER] - zoom level of a tile
Data [BLOB] - stream with a tile image data (currently PNG images)

All the coords to tile calculations are done in the application, so the SQLite R*Tree Module with the corresponding TADSQLiteRTree class have no meaning for me. All I need is to load Data field blob stream of a record found by a given X, Y, Z values as fast as possible.

The application will, except this database, have also a memory cache implemented by a hash table like this TTileCache type:

type
  TTileIdent = record
    X: Integer;
    Y: Integer;
    Z: Integer;
  end;    
  TTileData = TMemoryStream;    
  TTileCache = TDictionary<TTileIdent, TTileData>;

The workflow when asking for a certain tile while having X, Y, Z values calculated will be simple. I will ask for a tile the memory cache (partially filled from the above table at app. startup), and if the tile won't be found there, ask the database (and even if there won't the tile be found, download it from tile server).

Question:

Which AnyDAC (FireDAC) component(s) would you use for frequent querying of 3 integer column values in a SQLite table (with, let's say 100k records) with an optional loading of the found blob stream ?

Would you use:

query type component (I'd say executing of the same prepared query might be efficient, isn't it ?)
memory table (I'm afraid of it's size, since there might be several GB stored in the tiles table, or is it somehow streamed for instance ?)
something different ?

Solution

Definitely use TADQuery. Unless you set the query to Unidirectional, it will buffer all the records returned from the database in memory (default 50). Since you are dealing with blobs, your query should be written to retrieve the minimum number of records you need.

Use a parameterized query, like the following the query

SELECT * FROM ATable
WHERE X = :X AND Y = :Y AND Z = :Z

Once you have initially opened the query, you can change the parameters, then use the Refresh method to retrieve the next record.

A memory table could not be used to retrieve data from the database, it would have to be populated via a query. It could be used to replace your TTileCache records, but I would not recommend it because it would have more overhead than your memory cache implementation.