SQL Query Length Limit for TI

Post Reply
User avatar
mce
Community Contributor
Posts: 352
Joined: Tue Jul 20, 2010 5:01 pm
OLAP Product: Cognos TM1
Version: Planning Analytics Local 2.0.x
Excel Version: 2013 2016
Location: Istanbul, Turkey

SQL Query Length Limit for TI

Post 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
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: SQL Query Length Limit for TI

Post 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
User avatar
mce
Community Contributor
Posts: 352
Joined: Tue Jul 20, 2010 5:01 pm
OLAP Product: Cognos TM1
Version: Planning Analytics Local 2.0.x
Excel Version: 2013 2016
Location: Istanbul, Turkey

Re: SQL Query Length Limit for TI

Post 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?
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: SQL Query Length Limit for TI

Post 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
HighKeys
Posts: 117
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Office 365

Re: SQL Query Length Limit for TI

Post 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
User avatar
scrumthing
Posts: 81
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 11.x
Excel Version: MS365

Re: SQL Query Length Limit for TI

Post 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.
There is no OLAP database besides TM1!
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: SQL Query Length Limit for TI

Post 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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
User avatar
mce
Community Contributor
Posts: 352
Joined: Tue Jul 20, 2010 5:01 pm
OLAP Product: Cognos TM1
Version: Planning Analytics Local 2.0.x
Excel Version: 2013 2016
Location: Istanbul, Turkey

Re: SQL Query Length Limit for TI

Post 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,
User avatar
mce
Community Contributor
Posts: 352
Joined: Tue Jul 20, 2010 5:01 pm
OLAP Product: Cognos TM1
Version: Planning Analytics Local 2.0.x
Excel Version: 2013 2016
Location: Istanbul, Turkey

Re: SQL Query Length Limit for TI

Post 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.
HighKeys
Posts: 117
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Office 365

Re: SQL Query Length Limit for TI

Post 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).
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: SQL Query Length Limit for TI

Post 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.
Post Reply