Notifications

118 views

Following are few best practises to be considered while using ODBC driver. We recommend anyone, who is currently using ODBC Driver or planning to use it to go through these best practises and design / modify the solution accordingly.

  • ODBC seamlessly connects to Servicenow and fetches data requested. It is mainly intended for backing up required tables data.


  • The customer can run the reports/analytics on top of this data. It is fine to use  ODBC driver as a data source in the reporting tools which request smaller chunks of data. But, It may not be a good idea to use ODBC driver directly as a datasource in external reporting tools which may request huge data.  In the cases where customer need to query large data (may be more than 10K records) at once or repetitively request same data again and again, it is suggested to get the data backed up to a local database and run reports on top of it.


  • The customer may tend to run BI reports on top of ODBC driver. This is perfectly fine. But, if the customer has too many parallel queries running at once or a very fast refresh of data (due to which they run queries with very less / no time gap) it may not be a good idea to use ODBC driver directly from tool. The customer may consider running the queries in a sequential manner and try to have a reasonable refresh rate of data to avoid errors/ timeouts. If it is not acceptable, they may consider scheduling jobs to fetch latest data and write onto a local database on top of which the BI tools run.


  • ODBC is mainly intended to provide customer data. Sometimes, customers tend to use this tool directly to query all of their huge data at once for the purpose of taking backups. But, this is not suggested. Querying huge data using ODBC can cause timeouts. It is suggested to query data in chunks. For this, customer may use sys_created_on or sys_updated_on fields and query a week’s data or a month’s data once.


  • It is sincerely advised to avoid Joins / Conditions on Display Values as much as possible. You may face issues with queries while upgrading from older versions of ODBC to newer versions.


  • Try to avoid using UPPER() and LOWER() in query conditions. It is perfectly fine to use them in select.


  • Table joins are supported in ODBC. You can join 2 tables and fetch results. But, it is very inefficient do it using ODBC. Instead, we suggest you to create a view in Servicenow including all conditions and then use the view to fetch data.


  • When ODBC driver is used as a datasource for some reports, It can happen that the reporting tools issue very big query with huge number of conditions. Default supported length of query is 32768 characters. Configuration changes can be done to increase this limit. Also, default maximum supported number of conditions in a query is 1000. Configuration changes can be done to increase this limit. It is certainly not a good idea to increase this limit as such big number of conditions in a single query can cause performance issues and can cause timeouts. In this kind of case it is suggested to check if the query can be broken down to smaller queries. If it is not possible to break queries or reconsider the use case, the customer is advised to pull all necessary data onto a local database and then run reports on top of it.


  • Try to avoid queries like select number form incident where sys_id NOT IN("SYS_ID_1", "SYS_ID_2","SYS_ID_3",....). Queries with huge number of entries with NOT IN are known for very slow performance. Excessively using these kind of queries with more number of entries in NOT IN can cause performance issues and timeouts. Also, due to the limit on the number of conditions, one can not use more than 1000 sys ids. These kind of queries exhaust both length limit and condition limit of the queries. In this kind of case it is suggested to check if the query can be broken down to smaller queries. If it is not possible to break queries or reconsider the use case, the customer is advised to pull all necessary data onto a local database and then run reports on top of it.


  • ODBC driver supports parallel processing. It is possible to use tools like MS SQL Server Management Studio and perform jobs parallelly using ODBC driver. It is advised to restrict maximum number of parallel running jobs at any time to 6. Running more jobs can create bottlenecks at ODBC driver causing performance problems. Also, it is advised to make sure that jobs do not overlap as much as possible.


  • If the customer is running jobs parallelly and each of the job connects to different datasources, the customer must start using 1.0.14_01 driver. Using lower version of drivers can cause data corruption.

Article Information

Last Updated:2020-06-23 05:48:13
Published:2020-06-23