Categories
Tips for Better Performance When a Dataset Using a SQL Statement
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.
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.
Comments
-
@Harriet Huang-Oracle I would call those rules rather than rules for creating data sets using manual SQL, rules for constructing SQL in general (executed via any mean - for example in SQL Developer or within PL/SQL code).
I have one addition , which is really purely related to writing SQL for usage in Data Sets:
Avoid using CTE construct (WITH clause ) in SQL. BI Server (as execution engine within OAC/OAS) by itself adds WITH clause to generated SQL passed to corresponding DB (I assume Oracle DB) and if you have WITH clause within your query in data set definitions, this ends up with final SQL (generated by BI Server) having "nested" WITH clauses and this is not supported in Oracle - you will get an ORA error when using such data set.
1 -
+1 Michal, also because point (d) isn't really true with an Oracle database: the query with the inner join will be transformed by the database removing the join and pushing the condition in the WHERE clause (the same applies to outer joins rewritten from ANSI to Oracle join syntax, the (+) one).
Keeping it simple:
Tips for Better Performance When a Dataset Using a SQL Statement = An optimal query (following the usual tips on how to optimize a normal SQL query) + avoiding the CTE construct (WITH clause) if using an Oracle database because not supported (in some other engines nested CTE works)
0
