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.

[michael_perkins]

I have tremendous performance issues too. A count of incident records for January 2012 takes over six minutes to return to Microsoft Query for insertion into Excel. Anybody have some optimization tricks for improving performance?

[stuart_goodrick]

ISQL> select count(*) from incident where ucase(dv_opened_by) = ucase('Paul') and cast(opened_at AS DATE) = {d '2013-2-19'}
Cont> ;
COUNT(*)

22

Rows selected = 1
SQL: Operation successful.
Elapsed time 29813 ms.
Elapsed time: Prepare 1553 ms. Execute 28242 ms. Fetch results 18 ms.

But I can cut this dramatically by removing the ucase function ......



ISQL> select count(*) from incident where dv_opened_by = 'Paul' and cast(opened_at as DATE) = {d '2013-2-19'};
COUNT(*)

23

Rows selected = 1
SQL: Operation successful.
Elapsed time 1546 ms.
Elapsed time: Prepare 33 ms. Execute 1511 ms. Fetch results 2 ms.

[gyedwab]

If you want to be able to report live against ServiceNow data and also connect it to data that's stored in local SQL, you can use Explore Analytics to get a lot of the reporting that ServiceNow isn't capable -- live, automated, and embedded into ServiceNow. The ODBC driver isn't optimized for being able to get live visibility, it's really intended for copying data out of SN on a scheduled basis. We've focused on making that live reporting very performant using our own certified integration.

[martijn3]

Just my 2 cents:
Is it possible that all data is retrieved to your workstation before the query is run?
In that case you may want to change your query to "select count(sys_id) ...", and select the attributes that you really need for your top 100 incidents.

I think there is an ODBC "pass through" option. This may help to force the query to run server side and only return the data that you really want. (to be honest: I don't know how to enforce this).