Sluggish ODBC recordset
Posted: Sun Aug 19, 2018 9:33 am
I have a basic process on a DEV TM1 server to retrieve a set of records from a SQL server via ODBC.
The query returns roughly 18,000 records, 20 columns of numeric and text data. We are talking a fairly small data set here, no huge text chunks, binary objects etc.
If I run the process it seems to hang on Completing Prolog but looks like it is actually trying to start feeding the records.
Takes quite a while before I can see the SQLAPI method being logged to the tm1server.log
For test purposes, metadata has no commands and data has ITEMSKIP.
The process takes about an hour to complete. Progress indicator counts up by around 13 records every 5 seconds or so.
If I run the same query in the Datasource preview, we have our sample in mere seconds.
Running the same query in SQL Server Management Studio, the recordset is returned in about 7 seconds.
If I use Excel to bring in the records vis MS Query and use an ODBC connection configured exactly the same as on the server, the records are returned fairly quickly too, roughly 30 seconds.
If I change my query so do a SELECT TOP 1000, the process completes in a few seconds.
Unicode was off as is not required, setting to checked/on takes even longer.
All of the above relate to the SQL server instance being hosted in the client's DEV environment.
If I replicate the queries on the PROD environment, I get roughly the same performance in SSMS, Excel/MS Query.
TM1 however on the entire record set is also mere seconds.
I tried running the same TI process from another TM1 server and results are consistent with those on the DEV server.
The DBA's confirm that the DEV server is performing as it should, memory is available etc. etc.
We have also tried changing the SQL Fetch method in the TM1S.cfg but FetchScroll still seems the most efficient.
Has anyone seen this kind of behaviour before where it seems like the recordset is being throttled?
Other info:
TM1 Server is 10.2.2 FP4, model memory is running at 2.5Gb, server has 35Gb free. Disk space does not look to be an issue either 500Gb plus available.
SQL is 2012 or later, ODBC is configured as SQL Server, not Native Client - would need to post the exact version and file if this is required.
The query returns roughly 18,000 records, 20 columns of numeric and text data. We are talking a fairly small data set here, no huge text chunks, binary objects etc.
If I run the process it seems to hang on Completing Prolog but looks like it is actually trying to start feeding the records.
Takes quite a while before I can see the SQLAPI method being logged to the tm1server.log
For test purposes, metadata has no commands and data has ITEMSKIP.
The process takes about an hour to complete. Progress indicator counts up by around 13 records every 5 seconds or so.
If I run the same query in the Datasource preview, we have our sample in mere seconds.
Running the same query in SQL Server Management Studio, the recordset is returned in about 7 seconds.
If I use Excel to bring in the records vis MS Query and use an ODBC connection configured exactly the same as on the server, the records are returned fairly quickly too, roughly 30 seconds.
If I change my query so do a SELECT TOP 1000, the process completes in a few seconds.
Unicode was off as is not required, setting to checked/on takes even longer.
All of the above relate to the SQL server instance being hosted in the client's DEV environment.
If I replicate the queries on the PROD environment, I get roughly the same performance in SSMS, Excel/MS Query.
TM1 however on the entire record set is also mere seconds.
I tried running the same TI process from another TM1 server and results are consistent with those on the DEV server.
The DBA's confirm that the DEV server is performing as it should, memory is available etc. etc.
We have also tried changing the SQL Fetch method in the TM1S.cfg but FetchScroll still seems the most efficient.
Has anyone seen this kind of behaviour before where it seems like the recordset is being throttled?
Other info:
TM1 Server is 10.2.2 FP4, model memory is running at 2.5Gb, server has 35Gb free. Disk space does not look to be an issue either 500Gb plus available.
SQL is 2012 or later, ODBC is configured as SQL Server, not Native Client - would need to post the exact version and file if this is required.