SQL Query Length Limit for TI
- 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
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
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
-
- Site Admin
- Posts: 1454
- Joined: Wed May 28, 2008 9:09 am
Re: SQL Query Length Limit for TI
Per this thread, a TM1 string cannot be > 32k so that would limit an ODBCOutput statement.
https://www.tm1forum.com/viewtopic.php? ... ize#p70027
https://www.tm1forum.com/viewtopic.php? ... ize#p70027
- 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
My Query is not for ODBCOutput. Rather it is for SQL when importing data to TM1.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
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?
-
- MVP
- Posts: 3654
- 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
Even if you are holding the components or the query in different string variables ultimately you need to concatenate them together, e.g.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?
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.
-
- 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
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
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
- 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
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!
-
- MVP
- Posts: 2832
- 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
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.
- 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
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,
- 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
Thanks for the info. I am still within the boundaries, it looks.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.
-
- 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
Thats right! Our SQL Developer told me he is working on that (porting the views to stored procedures).tomok wrote: ↑Thu Jul 02, 2020 1:42 pmBetter 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.
-
- Site Admin
- Posts: 1454
- Joined: Wed May 28, 2008 9:09 am
Re: SQL Query Length Limit for TI
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.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).