TI SQL performance issue

Post Reply
PeteB
Posts: 23
Joined: Sun Sep 23, 2012 2:30 am
OLAP Product: Planning Analytics Local, PAW
Version: PAL 2.0.6, PAW 2.0.45
Excel Version: 2010

TI SQL performance issue

Post by PeteB »

I am having a performance problem running a SQL statement from TI. The exact same query pasted into Microsoft Query will return the 78 rows in under a second, while in TI it is taking over 2 hours to preview the results. TI is using the same ODBC connection as Microsoft Query on the same Windows server TM1 sits on. The length of time TI takes for this query to preview is appears relative to the number of rows the query will return by changing the where criteria in the SQL statement
(e.g. ... AND AL3.department='6111501'
to ... AND AL3.department='7210201')
1 row takes 1 second, 5 rows 2 seconds, 11 rows takes 0 seconds, 9 rows takes 1 second, 29 rows takes 40 minutes. The SQL performance appears repeatable for each change in the departments tested.

The problem TI SQL is intended to be used as a drill-through process. There is already a TI process loading 10,000 summarised records into the cube from the same database using the same tables in a few seconds.

TI is using the ODBC connection to connect to a Sequel Server database. The SQL nor the Database do not appear to be a problem as Microsoft Query and Hyperion return the same rows using exactly the same SQL. The ODBC connection doesn't appear to be the problem as the other TI process is returning are significantly more rows based on similar SQL.

Does anyone have any thoughts?

For completeness here is a version of the problem SQL

Code: Select all

SELECT 
  AL1.age_bucket,  
  AL3.insprkey, 
  AL3.cr_officer_code, 
  AL3.department, 
  AL1.transaction_cat, 
  AL3.account_no, 
  AL3.account_name, 
  AL1.debt_type, 
  AL1.amount, 
  AL1.transaction_key 
FROM 
  dbo.debt_history AL1, 
  dbo.debt_history_info AL2, 
  dbo.account AL3 
WHERE 
  AL2.src_sys=AL1.src_sys 
  AND AL2.src_date=AL1.src_date 
  AND AL3.src_sys=AL1.src_sys 
  AND AL3.account_no=AL1.account_no
  AND AL3.reconc='01' 
  AND AL2.src_sys='POLISY' 
  AND AL2.src_year=2013 
  AND AL2.src_month=7 
  AND AL2.src_period='ACCT' 
  AND AL3.department='6111501'

Here is the SQL which is summarizing, however returning significantly more rows.

Code: Select all

SELECT 
  AL1.src_sys, 
  AL2.src_year, 
  AL2.src_month, 
  AL1.age_bucket, 
  AL3.insprkey, 
  AL3.cr_officer_code, 
  AL3.department, 
  AL1.transaction_cat, 
  SUM ( AL1.amount ) 
FROM 
  dbo.debt_history AL1, 
  dbo.debt_history_info AL2, 
  dbo.account AL3 
WHERE 
  AL2.src_sys=AL1.src_sys 
  AND AL2.src_date=AL1.src_date 
  AND AL3.src_sys=AL1.src_sys 
  AND AL3.account_no=AL1.account_no
  AND AL3.reconc='01' 
  AND AL2.src_period='ACCT' 
  AND AL2.src_month = '7'
  AND AL2.src_year = '2013'
GROUP BY 
  AL1.src_sys, 
  AL2.src_year, 
  AL2.src_month, 
  AL1.age_bucket, 
  AL3.insprkey, 
  AL3.cr_officer_code, 
  AL3.department, 
  AL1.transaction_cat
nick_leeson
Posts: 98
Joined: Sat Feb 11, 2012 11:13 am
OLAP Product: TM1 9x, BPC, Hyperion, HANA
Version: TM1 10
Excel Version: Excel 2003 - 2010

Re: TI SQL performance issue

Post by nick_leeson »

Stupid question but how long does this take if the offending SQL is not in a drill-through process !!

Also without understanding your Tables , cardinality of data and Indexes , I would write the offending SQL as below
-- Restrict the Number of rows and then Join 3 tables

SELECT
AL1.age_bucket,
AL3.insprkey,
AL3.cr_officer_code,
AL3.department,
AL1.transaction_cat,
AL3.account_no,
AL3.account_name,
AL1.debt_type,
AL1.amount,
AL1.transaction_key
FROM
dbo.debt_history AL1,
dbo.debt_history_info AL2,
dbo.account AL3
WHERE
AL3.reconc='01'
AND AL2.src_sys='POLISY'
AND AL2.src_year=2013
AND AL2.src_month=7
AND AL2.src_period='ACCT'
AND AL3.department='6111501'
AL2.src_sys=AL1.src_sys
AND AL2.src_date=AL1.src_date
AND AL3.src_sys=AL1.src_sys
AND AL3.account_no=AL1.account_no

Hope this helps.
PeteB
Posts: 23
Joined: Sun Sep 23, 2012 2:30 am
OLAP Product: Planning Analytics Local, PAW
Version: PAL 2.0.6, PAW 2.0.45
Excel Version: 2010

Re: TI SQL performance issue

Post by PeteB »

Hi Nick,

Sorry, I should have said I had already tested the SQL both as a drill-through process and a standard process, just in case there was difference. As far as I can tell there is no difference between the drill-through process and a standard process from a TM1 perspective.

Thanks for the SAL tips, I will check out your suggested changes to the SQL. I hadn't been as concerned with tuning the SQL given its excellent performance via Hyperion and Microsoft Query.

I am currently testing to see if one the additional columns is causing the problem.
nick_leeson
Posts: 98
Joined: Sat Feb 11, 2012 11:13 am
OLAP Product: TM1 9x, BPC, Hyperion, HANA
Version: TM1 10
Excel Version: Excel 2003 - 2010

Re: TI SQL performance issue

Post by nick_leeson »

From my experience and we use Drill Through's extensively to give users access to GL Data from their ERP's. The initial SQL request by TI generally takes a few seconds and the subsequent ones are generally few seconds.
Any luck with the ODBC Log ?
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: TI SQL performance issue

Post by Steve Rowe »

I'm by no means an expert but isn't SQL type sensitive?

You have

AND AL2.src_year=2013
AND AL2.src_month=7

AND AL2.src_year='2013'
AND AL2.src_month='7'

in the two queries? Are you forcing SQL to do something different under the hood?

Cheers,

Steve
Technical Director
www.infocat.co.uk
User avatar
wissew
Posts: 54
Joined: Tue Jun 17, 2008 7:24 pm
OLAP Product: TM1
Version: 9.5.2; 10.2.2; 11
Excel Version: 2003 SP3 - 2013
Location: Beaverton, OR

Re: TI SQL performance issue

Post by wissew »

Steve is on to something. The ' key is need to encapsulate the month and year code.
PeteB
Posts: 23
Joined: Sun Sep 23, 2012 2:30 am
OLAP Product: Planning Analytics Local, PAW
Version: PAL 2.0.6, PAW 2.0.45
Excel Version: 2010

Re: TI SQL performance issue

Post by PeteB »

Hi Nick and Steve,

Thanks for your suggestions, but so far no luck. My main concern is why the same SQL code using the same ODBC connection returning the same data takes significantly longer in TM1 TI versus both Microsoft Query and Hyperion. In fact both Microsoft Query and Hyperion are too quick to see any discernible difference between them.

I confirmed the performance is not related to the data (columns) being returned by replacing the columns with text. Changing the code as Nick suggested
-- Restrict the Number of rows and then Join 3 tables
made no difference to performance either in TI or Microsoft Query.


Also, the ODBC log is not recording anything
Any luck with the ODBC Log ?
– the trigger is 30000 milliseconds, which should be triggered by the SQL in question.


I investigated Steve’s observation
You have

AND AL2.src_year=2013
AND AL2.src_month=7

AND AL2.src_year='2013'
AND AL2.src_month='7'
as I was surprised both would even work. However, both return the same data and there is no changed to the performance. I removed the dates as a cause in the test code below. Current there is only one months data.

I think the problem is related to the SQL and TI may be multiplying, by a magnitude, a potential performance issue in the SQL? I have run the following SQL code as tests – Microsoft Query has a performance problem with any of the following code.

The following code performs poorly in TI only

Code: Select all

SELECT 
  AL1.amount
FROM 
  dbo.debt_history AL1, 
  dbo.account AL2, 
  dbo.debt_history_info AL3 
WHERE 
 (AL2.department='6521401')
  AND (AL2.src_sys=AL1.src_sys 
 AND  AL2.account_no=AL1.account_no
 AND  AL3.src_sys=AL1.src_sys
 AND AL3.src_date=AL1.src_date)  
 
However removing a table works fine in TI and returns the same results it removes a table

Code: Select all

SELECT
 AL1.amount 
FROM 
 dbo.debt_history AL1, 
 dbo.account AL2 
WHERE 
 (AL2.department='6521401')
 AND (AL2.src_sys=AL1.src_sys 
 AND  AL2.account_no=AL1.account_no)  
The following code performs fine in TI it returns the same results

Code: Select all

SELECT 
  AL1.amount 
FROM 
  dbo.debt_history AL1, 
  dbo.account AL2, 
  dbo.debt_history_info AL3 
WHERE   
  (AL2.account_no IN ('A1280000002', 'A1280000004', 'A128T888888'))
  AND (AL2.src_sys=AL1.src_sys 
  AND AL2.account_no=AL1.account_no 
  AND AL3.src_sys=AL1.src_sys 
  AND AL3.src_date=AL1.src_date)
I will talk to my DBA about indices to improve the SQL performance, but I don't get the performance difference in TI.
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: TI SQL performance issue

Post by tomok »

Is the Use Unicode opton checked in TI? I have seen SQL performance issues in TI when the database has Unicode data in it and this option is not checked.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
PeteB
Posts: 23
Joined: Sun Sep 23, 2012 2:30 am
OLAP Product: Planning Analytics Local, PAW
Version: PAL 2.0.6, PAW 2.0.45
Excel Version: 2010

Re: TI SQL performance issue

Post by PeteB »

Hi Tomok,

Thank you for your response
tomok wrote:Is the Use Unicode opton checked in TI? I have seen SQL performance issues in TI when the database has Unicode data in it and this option is not checked.
Yes the Use Unicode option is checked
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: TI SQL performance issue

Post by lotsaram »

PeteB wrote:Yes the Use Unicode option is checked
Try unckecking it. I have also seen factor 10 or more improvement by doing this. Although the unicode option is the default for ODBC datasources in TM1 the reality is that if you're not dealing with extended character sets then you don't need it.
PeteB
Posts: 23
Joined: Sun Sep 23, 2012 2:30 am
OLAP Product: Planning Analytics Local, PAW
Version: PAL 2.0.6, PAW 2.0.45
Excel Version: 2010

Re: TI SQL performance issue

Post by PeteB »

lotsaram wrote:
PeteB wrote:Yes the Use Unicode option is checked
Try unckecking it. I have also seen factor 10 or more improvement by doing this. Although the unicode option is the default for ODBC datasources in TM1 the reality is that if you're not dealing with extended character sets then you don't need it.
Hi Lotsaram,

Thank you. Yes, unticking the "Use Unicode" has made a definite improvement on a number of queries. The one query which was taking about an hour and forty minutes is now down to about 10 minutes. But still not comparable to Microsoft Query is doing the same in about 1 second. I am now searching the forum for threads containing Unicode and ODBC - I had only looked for TI and SQL previously. There seems to be a number of options in the following threads
http://www.tm1forum.com/viewtopic.php?f ... ode#p42109 and http://www.tm1forum.com/viewtopic.php?f ... ode#p35743
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: TI SQL performance issue

Post by rmackenzie »

Hi PeteB -

The issue you are facing is definitely one of the TM1 bugbears that crops up every now and then; I find it most frustrating as well. Although you've found a performance boost by not using the Unicode option, as the guys pointed out, the performance is still not matching other query tools - why? When you run a TI that uses an ODBC data source, TM1 does two things: firstly, it issues the query via the driver and is returned a result. Second, AIUI, it caches some (or all) of the results into memory for processing by the TI script (depends on size).

The difference between TI and other SQL query tools is that it only knows about numeric (real) and string data types. Therefore it has to convert the data values in the results to the correct type. E.g VARCHAR becomes string, INT and FLOAT become numeric etc. Sometimes it is going to encounter column types that aren't supported (TM1TypeSQLNotSupported). This all takes time and other SQL query tools probably handle this issue in a different, more efficient way. As TI processes its way through the results, this post-processing is, IMO, the reason for the performance issue.

So, I would try re-writing the query in such a way that each column is cast into a well-known type (CHAR, VARCHAR, DATETIME, INT, REAL, FLOAT etc) that is easily parsed by TI. Also, I would re-write the query such that the joins between tables are explicitly defined and that the constraints are expressed per the data type. E.g. if the column is INT then var=2013 and not var='2013'.

I've encountered one or two queries that simply wouldn't process over the years so heres hoping this isn't the first for you ;)
Robin Mackenzie
PeteB
Posts: 23
Joined: Sun Sep 23, 2012 2:30 am
OLAP Product: Planning Analytics Local, PAW
Version: PAL 2.0.6, PAW 2.0.45
Excel Version: 2010

Re: TI SQL performance issue

Post by PeteB »

Hi RMackenzie,

Thank you for your great explanation on how TI was working differently to the other tools in regards the ODBC connection. And thanks for your suggestions as well.
rmackenzie wrote:
So, I would try re-writing the query in such a way that each column is cast into a well-known type (CHAR, VARCHAR, DATETIME, INT, REAL, FLOAT etc) that is easily parsed by TI. Also, I would re-write the query such that the joins between tables are explicitly defined and that the constraints are expressed per the data type. E.g. if the column is INT then var=2013 and not var='2013'.

I have tested out your suggestions but did not get the results I was expecting, so I just wanted to clarify if I have followed them correctly. Firstly, I hoped to eliminate the parsing issue by simply returning a 1 in each row. Secondly, in the examples below, I have matched the constraints and their data types. Finally, I have performed the explicit joins. However I am still getting unusual performance results.

Code: Select all

-- This SQL is taking over 10 seconds and returns 1 million number of rows
SELECT 
   1
FROM 
  dbo.debt_history AL1 INNER JOIN dbo.debt_history_info AL3     ON AL3.src_sys=AL1.src_sys AND 
  AL3.src_date = AL1.src_date 
   INNER JOIN   dbo.account AL2  ON
  AL2.src_sys=AL1.src_sys AND 
  AL2.account_no=AL1.account_no
WHERE 
  AL2.reconc='01'

-- This code is taking over 500 seconds to return 800 rows
SELECT 
   1
FROM 
  dbo.debt_history AL1 INNER JOIN dbo.debt_history_info AL3     ON AL3.src_sys=AL1.src_sys AND 
  AL3.src_date = AL1.src_date 
   INNER JOIN   dbo.account AL2  ON
  AL2.src_sys=AL1.src_sys AND 
  AL2.account_no=AL1.account_no
WHERE 
  AL2.reconc='02'
I am still hoping this is not going to be one of those queries you mentioned.
kugors
Posts: 24
Joined: Tue Dec 06, 2011 10:21 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2010

Re: TI SQL performance issue

Post by kugors »

Hi,

I had similar issue while loading result data to TM1. I used workaround to deal with it. I created table in SQL with columns from select and function that's populate this table with data. I placed this function in prolog of TI and put simple select * from table in query. In My case it worked.

HTH
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: TI SQL performance issue

Post by rmackenzie »

PeteB wrote:Firstly, I hoped to eliminate the parsing issue by simply returning a 1 in each row.
Although I agree TI shouldn't have much trouble understanding that 1 is a number, I'd still be tempted to assign explicit types to the columns simply to eliminate the possibility that TI is looking at the column type itself and not the type of the field value in order to discern the type for TI. I.e. value of 1 could still be in a field of a non-integer type.
PeteB wrote:Secondly, in the examples below, I have matched the constraints and their data types. Finally, I have performed the explicit joins. However I am still getting unusual performance results.
Looks good - amazing that AL2.reconc='01' and AL2.reconc='02' are causing such a difference in performance - at this point it looks like you've hit one of those problem queries. Although it clearly looks like a TM1 issue, are you able to test with a different ODBC driver, or even different RDB stack?
Robin Mackenzie
Post Reply