> the server would hang with the default 50 and 1000
Which server would hang? Do you have a threaddump or other information to confirm what you were seeing?
> Does teiid have a bulkload facility that we could leverage to insert to a temp table? I saw max insert statements on the translator properties. Would we need to write a Java program to do this (read something around max prepared statements), or is this something we could accomplish in SQL?
Teiid does handle insert into with query expressions - insert into tbl select ...
This will load the target table using prepared insert statement batches and may not have the same performance as a specific bulk load facility.
The server that failed was Teradata, it failed in two ways.... Unfortunately, I don't have a thread dump.
(again I was able to get around this by limiting the IN clause to 100 instead of the 1000 default, and 5 dependent instead of 50 dependent joins ; not sure if that helped besides the IN changes, but I did set that too and it seem to work)
1. The query generated by Teiid when I cut/copy/pasted it to test simply hung the SQL Assistant, when trying to execute it. It was really long. In fact when I simply try to paste it into the editor here, it will crash chrome.
2. For the second way. A different error was produced from Teradata - I had to redact this:
2016-10-23 18:19:15,555 WARN [org.teiid.CONNECTOR] (Worker19_QueryProcessorQueue144) LqnGMFsLQlNX Connector worker process failed for atomic-request=LqnGMFsLQlNX.0.78.39: org.teiid.translator.jdbc.JDBCExecutionException: 3919 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values:  SQL: SELECT g_0."A" AS c_0, TRIM(TRAILING FROM g_0."B") AS c_1, g_0."C" AS c_2, TRIM(TRAILING FROM g_1."D") AS c_3, g_1."E" AS c_4, TRIM(TRAILING FROM g_1."F") AS c_5, TRIM(TRAILING FROM g_2."G") AS c_6, TRIM(TRAILING FROM g_2."H") AS c_7, g_1."I" AS c_8, TRIM(TRAILING FROM g_1."J") AS c_9, g_1."K" AS c_10, g_1."K" AS c_11, TRIM(TRAILING FROM g_0."L") AS c_12, g_0."M" AS c_13, g_0."N" AS c_14, g_0."O" AS c_15, g_0."P" AS c_16 FROM "Q"."R" AS g_0, "Q"."S" AS g_1, "Q"."T" AS g_2 WHERE g_1."E" = g_2."E" AND TRIM(TRAILING FROM g_1."F") = TRIM(TRAILING FROM g_2."F") AND TRIM(TRAILING FROM g_1."D") = TRIM(TRAILING FROM g_2."D") AND g_1."E" = g_0."E" AND TRIM(TRAILING FROM g_1."F") = TRIM(TRAILING FROM g_0."F") AND TRIM(TRAILING FROM g_1."D") = TRIM(TRAILING FROM g_0."D") AND g_0."A" IN <clause continues with various ANDs for 600000 characters>
Caused by: java.sql.SQLException: [Teradata Database] [TeraJDBC 15.10.00.22] [Error 3919] [SQLState HY000] Table has too many columns.
... 64 more
Thanks for the tip regarding the insert!!!
I'm less concerned if it won't be as fast, I'm more concerned about having a solid DV approach without having to leverage all of the native bells and whistles of the RDBMS.
Steven - bulk loading from an insert in Teiid to a temp table in Teradata helped considerably. It only took a couple of minutes to get everything that was in the IN clause over to the server, then the server was able to work on it more productively and quicker.
Thanks for your help!
Glad to hear it's working for you. I'll look to see if the default settings for the Teradata translator need changed to prevent the exception you are showing above.