SingleStore DB

Read Query Results in Parallel

In SingleStore, the results of a SELECT statement can be read in parallel by multiple readers. Each reader queries a unique partition, which contains a slice of the result set.

Parallel query reads are intended for use by client applications that query data from SingleStore. These client applications need to connect to the master aggregator to run parallel query reads.

A parallel query read can be coded in your client application logic using the following steps, in order.

  1. Create a result table to store the result set whose data the application will query in parallel.

    CREATE [MATERIALIZED] RESULT TABLE <result_table_name> AS SELECT ... FROM <table(s)>;

    The result table can be created in one of two read modes:

    • Single-read mode: Each reader can read the query results, from a specified partition, only once.

    • Multiple-read mode: Each reader can read the query results, from a specified partition, more than once. Also, multiple readers can read from the same partition. In this mode, creating the result table is slower and consumes more memory, as compared to single-read mode. To create the result table in multiple-read mode, specify the MATERIALIZED option.

    If the contents of <table(s)> change after the result table is created, the contents of the result table are not updated.

  2. From each reader, query a partition in the result table that was created in the previous step.

    SELECT * FROM ::<result_table_name> WHERE partition_id() = <partition_id>;

    Note

    In single-read mode, each reader needs to run in a separate parallel process or thread. Also, in single-read mode, each reader must initiate its read query before any readers will receive data.

    To find the number of partitions in a database, run:

    SELECT num_partitions FROM information_schema.DISTRIBUTED_DATABASES WHERE database_name = '<database_name>';
  3. When readers no longer read from the result table, drop the result table.

    DROP RESULT TABLE <result_table_name>;

    DROP RESULT TABLE can be run on the master aggregator or a child aggregator.