Page 1 of 1

SQL Query Length Limit for TI

Posted: Thu Jul 02, 2020 9:28 am
by mce
Hello,

Is there any limit about the length of the final SQL statement that is passed from Turbo Integrator process to an Oracle DB?
Thanks in advance for any responses.

Regards

Re: SQL Query Length Limit for TI

Posted: Thu Jul 02, 2020 9:32 am
by David Usherwood
Per this thread, a TM1 string cannot be > 32k so that would limit an ODBCOutput statement.
https://www.tm1forum.com/viewtopic.php? ... ize#p70027

Re: SQL Query Length Limit for TI

Posted: Thu Jul 02, 2020 9:48 am
by mce
David Usherwood wrote: Thu Jul 02, 2020 9:32 am Per this thread, a TM1 string cannot be > 32k so that would limit an ODBCOutput statement.
https://www.tm1forum.com/viewtopic.php? ... ize#p70027
My Query is not for ODBCOutput. Rather it is for SQL when importing data to TM1.
Actuall I do not need to put all SQL query into a single String variable. I can have multiple string variables that are being used in the SQL statement for SELECT, such as ?SQLPart1? and ?SQLPart2?...etc. Does this 32K limit still apply to the overall final SQL statement?

Re: SQL Query Length Limit for TI

Posted: Thu Jul 02, 2020 11:12 am
by lotsaram
mce wrote: Thu Jul 02, 2020 9:48 am My Query is not for ODBCOutput. Rather it is for SQL when importing data to TM1.
Actuall I do not need to put all SQL query into a single String variable. I can have multiple string variables that are being used in the SQL statement for SELECT, such as ?SQLPart1? and ?SQLPart2?...etc. Does this 32K limit still apply to the overall final SQL statement?
Even if you are holding the components or the query in different string variables ultimately you need to concatenate them together, e.g.
DatasourceQuery = sSELECT | sFrom | sWhere;

There's a bit of conjecture over whether the limit is 32k, 64k or something in between. It does depend whether unicode character sets are used or not, but whatever the exact ceiling the size limit is still going to apply to the DatasourceQuery query variable.

Re: SQL Query Length Limit for TI

Posted: Thu Jul 02, 2020 12:15 pm
by HighKeys
Hi,

why you dont create a SQL View and just import the View?

You can also build 2-6 or more views and combine them to one view for the import.

Thats how we handle big SQL Statements to bring it into a Importable format for TM1.

BR

Re: SQL Query Length Limit for TI

Posted: Thu Jul 02, 2020 1:28 pm
by scrumthing
HighKeys wrote: Thu Jul 02, 2020 12:15 pm why you dont create a SQL View and just import the View?
I second that. I would keep the sql query for the data source as simple as possible. Besides string variable limits, etc. it is about performance as well. depending on your sql database views are cached for example.

Re: SQL Query Length Limit for TI

Posted: Thu Jul 02, 2020 1:42 pm
by tomok
HighKeys wrote: Thu Jul 02, 2020 12:15 pm why you dont create a SQL View and just import the View?
Better yet, use a stored procedure. Each individual INSERT INTO statement is going to have to be compiled before execution. Depending on how often you call the query this can really add up. Stored procedures don't have to be compiled each time which CAN result in significant time savings if you are uploading thousands of records to SQL.

Re: SQL Query Length Limit for TI

Posted: Thu Jul 02, 2020 2:45 pm
by mce
HighKeys wrote: Thu Jul 02, 2020 12:15 pm Hi,

why you dont create a SQL View and just import the View?

You can also build 2-6 or more views and combine them to one view for the import.

Thats how we handle big SQL Statements to bring it into a Importable format for TM1.

BR
1 - Need to keep the logic in TM1 for further changes and additions due to segregation of duties.
2 - My view code is actually short and simple but has some TM1 variable that are populated during execution which ends up in a big SQL Query statement
3 - We prefer to use Union All in SQL with multiple individual conditions for each table or view, rather than having one where condition for all, expecting to perform better.

Hope this clarifies.
Regards,

Re: SQL Query Length Limit for TI

Posted: Thu Jul 02, 2020 4:35 pm
by mce
lotsaram wrote: Thu Jul 02, 2020 11:12 am There's a bit of conjecture over whether the limit is 32k, 64k or something in between. It does depend whether unicode character sets are used or not, but whatever the exact ceiling the size limit is still going to apply to the DatasourceQuery query variable.
Thanks for the info. I am still within the boundaries, it looks.

Re: SQL Query Length Limit for TI

Posted: Thu Jul 02, 2020 10:01 pm
by HighKeys
tomok wrote: Thu Jul 02, 2020 1:42 pm
HighKeys wrote: Thu Jul 02, 2020 12:15 pm why you dont create a SQL View and just import the View?
Better yet, use a stored procedure. Each individual INSERT INTO statement is going to have to be compiled before execution. Depending on how often you call the query this can really add up. Stored procedures don't have to be compiled each time which CAN result in significant time savings if you are uploading thousands of records to SQL.
Thats right! Our SQL Developer told me he is working on that (porting the views to stored procedures).

Re: SQL Query Length Limit for TI

Posted: Fri Jul 03, 2020 6:22 am
by David Usherwood
tomok wrote: ↑Thu Jul 02, 2020 1:42 pm
HighKeys wrote: ↑Thu Jul 02, 2020 12:15 pm
why you dont create a SQL View and just import the View?
Better yet, use a stored procedure. Each individual INSERT INTO statement is going to have to be compiled before execution. Depending on how often you call the query this can really add up. Stored procedures don't have to be compiled each time which CAN result in significant time savings if you are uploading thousands of records to SQL.
Thats right! Our SQL Developer told me he is working on that (porting the views to stored procedures).
But you did say that you were not updating your database - so compiled INSERTs are not relevant. And you also said that there was a requirement to keep the logic in TM1.