ODBC Extremely Slow

[francois_bergh]

Hi Guys

We've got a SQL Server linked server connection up and running for the ODBC connector and we're able to query it successfully, but there are severe performance issues on the queries.

For example running a simple count(*) query on a table with 5700 rows took 40 seconds to return, and it only gets worse - returning the top 100 records in our domain from the incident table took 17 minutes.

Are there anything that will cause this kind of performance degradation?

Thanks in advance.

[Kostya]

We have the same problem. The ODBC driver is too slow, even with one result row. It takes ca. 10-15 seconds to make a connection. If we fire the request one after another the result is coming quickly.

[randycaplan]

the SELECT * FROM OPENQUERY(linkedservername, 'query') is the passthru syntax that executes the SELECT SQL on the server from what i read.

I am seeing extremely slow results trying to do the most basic queries with small reult sets.

Please let me know if anyone has found the solution to this.

[natalliar]

The aggregate queries (like count) will work faster if you have the Aggregate Plugin activated on your instance and you are using 1.0.7.3 ODBC driver (the aggregate queries are broken in 1.0.8 and 1.0.9, the fix will be available in 1.0.10).

The first query after the connection has been established might be slow because the driver first sends the schema request and if your schema is pretty large it will take time to retrieve it. You can run in your browser <instance>?SCHEMA to cache the DB schema and then it will be faster when the driver pulls it.

Hope it helps.

[jimg1]

Any idea when 1.0.10 will be released?

We are about to upgrade our instance to FUJI and I want to ensure we are getting the best performance out of our daily data extraction.

Thanks!

[pavelmuller]

Hi,

how about looking at it from a different perspective? Why accessing the data live using the ODBC driver? We have very good experience with replicating the data into an on-premise database using SnowMirror and then using standard tools to work with them.

Here is a real-world example from one large enterprise:

Copying Service-Now data on-site for reporting purposes

Regards,

Pavel