(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