Here is a few tips to improve the performance for dataset while using "Manual Query" from connection.
1. Below document mentions how to use "Manual Query" to create dataset.
https://docs.oracle.com/en/cloud/paas/analytics-cloud/acubi/add-table-dataset-using-sql-statement.html#GUID-B80D059A-3D8D-4830-A7E7-BB77FDD22369
2. Apply the optimization tips to the Manual Query as needed.
a. Only pull the columns you need (SELECT [Column] FROM [Table]), avoid the use of * (SELECT * from [Table]).
b. Add required filters in the WHERE clause to exclude unwanted data. For example, WHERE [Column] IS NOT NULL
c. Remove unnecessary joins.
d. Use INNER JOIN instead of WHERE statement.
For example,
SELECT column2
FROM A, B
WHERE A.column1 = B.column1
Instead, change it to -
SELECT column2
FROM A
INNER JOIN B ON A.column1 = B.column1
e. Use WHERE instead of HAVING. Since the execution order for WHERE, GROUP BY and HAVING is WHERE, GROUP BY, HAVING. Have the conditions in WHERE statement will reduce the data volume before grouping.
For example,
SELECT [Column]
FROM [Table]
GROUP BY [Column]
HAVING [Column] <> 'A'
Instead, change it to -
SELECT [Column]
FROM [Table]
WHERE [Column] <> 'A'
GROUP BY [Column]
f. Run the SQL statement in database and check the execution plan to see which part takes more time and tuning accordingly.
