Spotfire Ideas Portal
Please find the attached pdf.
TDV Studio generates following strange SQL on SQL tab when making INNER JOIN on multiple tables on Model tab.
Suppose there are view A, B, C in line on Model panel, and join them with INNER JOIN.
TDV generates nested inner joins as follows:
SELECT
*
FROM
view_C INNER JOIN
view_A INNER JOIN
view_B
ON view_A.id = view_A.id
ON view_B.num = view_C.num
In many cases, when inner-joining multiple tables, tables are placed in the order of number of records each one has for improving performance on RDB like this:
SELECT
*
FROM
view_A INNER JOIN
view_B
ON view_A.id = view_A.id INNER JOIN
view_C
ON view_B.num = view_C.num
So, we would like TDV Studio to generate the latter style of SQL.
For your information, we expected TDV genrates the latter style of execution plan from the former SQL, however, it seems it does not optimzes it in this way.
Given the declarative nature of SQL and commutative nature of joins order does not matter in this case with regards to performance.
Hello Trevor,
For answering your question, we are not seeing performance issue at this moment, this is a theoretical one that we believe it more efficient.
Best Regards,
T.Miyamura
Question for Takuya:
Are you seeing actual performance differences if you hand code your queries using both forms or are you just suggesting a different syntax order because you believe it to be more efficient?
The reason I ask is because for this particular example, the performance should be the same in both cases. If we know the cardinality of the tables, further optimization can occur.
Here's a clip from an internal discussion related to this:
The SQL is equivalent to the customer actions
Which is the same as the following
The TDV SQL Engine has cardinality based optimizations that can reorder the joins as necessary
So, the real question is whether or not you're seeing performance differences related to how you structure your SQL. If not, then this isn't a problem. If it is different, then we'll want to investigate this further.
Thanks!