Sluggish ODBC recordset

Post Reply
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Sluggish ODBC recordset

Post by gtonkin »

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.
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Sluggish ODBC recordset

Post by paulsimon »

Hi George

Sometimes SQL will adopt a different query strategy depending on whether it is being asked to return the first n records or the full record set. I have certainly seen issues around this with DB2/400, but not so much with SQL Server. If you know there are 20000 records then I would try running it with Top 20000 to see if that makes any difference.

Another issue I have encountered in the past is an issue due to character set conversion. This can mean that the query is unable to use predefined indexes because although it looks like the fields will match, they don't because the character set is different. Is it possible that the TM1 Server is running in a different locale to the SQL Server?

Have you checked to see if there is any implied conversion eg from String to Numeric and vice-versa being forced by the Variables Tab? If so then converting in the SQL SELECT with CAST might cure the issue.

If all else fails I would change the ODBC Driver to Native Client

Regards

Paul Simon
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Sluggish ODBC recordset

Post by gtonkin »

Thanks for the reply Paul. Tried the SELECT TOP 17549 records from the query but made no difference.
There appears to be only varchar and decimal columns in the recordset, as well as the filters-does not look like anything out of the ordinary.
The table was re-indexed but no changes.

Apologies,what I failed to mention in my original post is that this was working fine a few days ago then suddenly it all went to pot. This leads me to believe that it must be something on the SQL side.
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Sluggish ODBC recordset

Post by paulsimon »

Hi George

The other possibility other than the SQL database is that it is the network between the SQL Server box and the TM1 box. That might explain why querying it from a client PC using ODBC is much faster. Have you tried running the query on the TM1 Server via Excel using the same ODBC DSN as the TM1 Server? If that is also slow, then that would point to the network.

Regards

Paul
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Sluggish ODBC recordset

Post by gtonkin »

Hi Paul,
There is no Excel on the server but querying the table via HeidiSQL portable (no SSMS on server either), I get the same results as from the workstation, roughly 7 seconds. I know this is not a like-for-like comparison as I am not using ODBC but at least gives an indication that the network seems fine.
We asked them to reboot the SQL DEV server over the weekend so hoping tomorrow will be like someone waved a magic wand. If not, will keep checking, googling and trying alternatives.
Will post back any feedback.
Post Reply