Hey @Rocky When doing a large insert does it help to order the results of the insert query to match the destination tables partition and then ORDER BY, or is it better to just supply the data unordered and just let the internal insert logic order the data as needed?

Hi there! I'm Rocky, and I'd be happy to help you optimize your large data inserts in StarRocks. The short answer is: It is generally better to let the internal logic handle it and avoid adding an explicit

ORDER BY

to your

SELECT

query. Here is the breakdown of why and how you should handle large inserts for the best performance: 1. Why manual

ORDER BY

is often a bottleneck When you add an

ORDER BY

to a

SELECT

clause in an

INSERT INTO ... SELECT

statement, StarRocks may attempt a global sort. In an MPP (Massive Parallel Processing) architecture, a global sort is a very expensive operation that often funnels data through a single node or requires heavy coordination, creating a performance bottleneck. Furthermore, even if you sort the data in the

SELECT

stage, that order is usually lost during the "shuffle" phase of the insert. StarRocks must shuffle the data across the network to the correct Backend (BE) nodes based on the target table's

DISTRIBUTED BY

(bucket) keys. 2. How StarRocks handles sorting internally StarRocks has two internal "sorting" mechanisms that are more efficient than a manual `ORDER BY`: * Data Distribution (Shuffle): StarRocks automatically shuffles data so that rows land on the BE nodes responsible for specific buckets. * Local Sort for Storage: Once the data reaches the destination BE, StarRocks performs a local sort based on the table's

KEY

columns (the sort keys defined in your

CREATE TABLE

statement) before writing the data to disk. This ensures the data is stored in the optimal order for query performance without the overhead of a global sort. 3. The best way to optimize for many partitions:

load_shuffle_data

If your "large insert" is struggling because it is writing to many partitions or buckets simultaneously (which can lead to "too many open files" or high memory usage), you should use the following session variable instead of a manual sort:

sql
SET load_shuffle_data = true;
INSERT INTO target_table SELECT * FROM source_table;

What this does: * When

load_shuffle_data

is enabled, StarRocks adds an optimized shuffle step to the execution plan that groups data by both partition and distribution keys before sending it to the writer nodes. * This ensures each BE node receives data for only a few partitions at a time, significantly reducing memory pressure and improving the stability of large inserts. Summary Recommendation * Do not use

ORDER BY

in your

INSERT INTO ... SELECT

statement; it creates an unnecessary bottleneck. * Do use

SET load_shuffle_data = true;

if you are inserting into a table with many partitions. * Trust the Sort Keys: Ensure your destination table has appropriate columns in its

DUPLICATE/UNIQUE/AGGREGATE KEY

definition, as StarRocks will handle the local sorting for storage efficiency automatically. Hope this helps! Let me know if you have more questions about your table design! References • loading/InsertInto.md • best_practices/partitioning.md • table_design/data_distribution/expression_partitioning.md