To issue the same query in ACT using the SQL LIMIT clause to limit results to 1000 rows:
SELECT * FROM FOO, BAR where FOO.A = BAR.A LIMIT 1000;
Here is how the query will execute:
- ACT issues the SQL statement to the queen.
- The queen planner identifies LIMIT 1000 and pushes down the following query to each of the workers:
SELECT * FROM FOO, BAR where FOO.A = BAR.A LIMIT 1000;
- Each worker computes the equi-join as dictated by the FOO.A = BAR.A LIMIT 1000 constraint. This allows for optimizations where the entire equi-join computation need not be done at each worker.
- The queen fetches results from the workers.
- ACT fetches results from the queen.
The difference between the two approaches occurs in step 3 of both examples. Using the SQL LIMIT clause (Example 2), the workers are allowed to compute with the constraint of LIMIT 1000, whereas in Example 1 they have to compute the entire equi-join.
So, as you can see, fetch-limit via server-side cursors does not translate into the workers doing a LIMIT 1000 on their individual slice of data. Therefore, if the use case calls for it, an Aster Database power-user should be aware that using the SQL LIMIT clause can speed up query execution dramatically in Aster Database.