ODBC Driver reliablity and performance issues - can data be bulk downloaded without ODBC?

[simonlawrence]

Hi everyone - Really could do with some advice, so here's hoping someone out there has encountered a similar issue to ourselves.

I run reports that use the INCIDENT table, and there are about 170000 records within it.   We run reports that analyse this data in many different ways. Originally folks here used to use the GUI and download/export to MSEXCEL - but this only downloads a maximum of 20000 records (and can take a while getting the data too). If different people were doing different reports, they'd all be doing the same thing - separately downloading and exporting to MSEXCEL.

Trouble is - 20000 records isn't enough for our analysis - especially for year on year comparisons, or for finding out answers to "when did 'x' start happening?" and the like. We did look at extending this, but we'd much rather have the data in a "proper" DB, where the contents of the INCIDENT table can be downloaded and stored automatically ona daily basis, requiring no user interaction at all.

Obviously, its efficient to have the data in a proper database anyway - its faster, and the results can be kept centrally and referred to by everyone who needs the data.     We're using the ODBC SERVICENOW driver to achieve this, and I have to say, our experience of it isn't very good at all (how do you guys find it, out of interest?)

Ok - that's the background - heres the issue:

I want all the fields (well, nearly all) the INCIDENT table.   Id like to do:

SELECT * INTO localtable from OPENQUERY (SNODBC,'SELECT * FROM INCIDENT')

..but I cant because some of the data types don't seem to like being imported into MSSQLSERVER owing to "precision" issues, and such. (it just means I have to do a query that specifies the fields by name so I can CAST them to datatypes that work).  

Ok so far, manually specifying the fields and CASTing the datatypes works well..     here's where it gets sticky:

I cant specify all the fields I want in the same query - it just times out.   So, my solution was to have 3 queries each pulling about 20 fields, join the results together on my MSSQLSERVER and so creating the day's INCIDENT table snapshot.

That does at least get me data, but not reliably.   Usually it fails - either one or all three queries fail - and the error message seems to be different each time.   it complains about the disk cache being too small (no it isn't, I've set it to 1GB in the ODBC admin tool), or it will just time out, or today's special "Unable to retrieve error message from OpenAccess SDK IP Layer".

The crazy thing is, I might as well specify all the fields I want (across multiple queries) because if I just ask for 5 fields, it seems to take the same amount of time as asking for 20 fields.   Each query can run for about 20-30 min before either actually delivering the data or just generating errors (in which case its try it again and again and again until eventually it works).

So, based on my experience so far - I'm finding it very hard to see how the ODBC driver is reliable enough to just do its job without constant intervention from technical resources that shouldn't have to be looking in on this -   is there any other way we can get our data?   Does SERVICENOW provide a daily data-dump-and-download-over-ftp service or similar?   I don't really mind HOW the data arrives here - but we need this daily - and the ODBC method just doesn't seem up to the job, and ive got a lot of fed up folks here who are depending on being able to use our data.

I've tried disabling the timeout on SERVICENOW ODBC, but this just means the queries don't work at all - they immediately fail.

Bandwidth to internet isn't a problem - we are on a 250Mbps connection .. guys I'm really running out of ideas,     if anyone has any suggestions,   I'm open to them.

Thanks!
-Simon

[simonlawrence]

Some progress.   By further splitting down my queries (ie: instead of 3 queries getting my data, there are now 6 retrieving less fields each), it seems to improve reliability.. but as always with things like this - there's something else in the way.     Now, for some reason (either a bug or some sort of SERVICENOW restriction?) some of the queries are returning 32000 results.. exactly.

Run the individual query by itself - and I get the full, expected 180000 results.   So there's got to be some sort of issue with the ODBC driver. When I write a query against a "real" database, my query returns the same data (or at least - correct expected data) every time. The only difference is this horrible middleware.     OH and it seems to "forget" its authentication too, so sometimes objects are just not visible - and the only way to get it working again is to deliberately give it bad credentials - refresh it - give it the correct credentials again, refresh it AGAIN, and then the objects come back.

this is a truly awful ODBC connector, its so unstable - I could nearly forgive this if the word "Alpha" was after the version info - but as a serious proposal for getting data out of SERVICENOW - its dire.   Seems to be the latest version, too.

In in case it helps anyone out there - or is useful - each query is returning about 10 fields, over 180000 records or so in the OAUSER.INCIDENT table. Getting that data is taking about 8-10min, if this helps you compare your experience with mine (performance-wise).

So, because I'm having to pose 6 queries to ODBC , each one taking anywhere between 8-12min to run (assuming they run first time - they usually don't..) this means that 6 x 10min - an *HOUR* to pull down 200000 records.

To say this is appalling performance is something of an understatement.     Of course - once the data is actually here in a decent database system, I can retrieve that data in less than 20sec.   What on earth is the performance bottleneck at SERVICENOW datacentre???

[simonw · Accepted solution]

Hi Simon,

I've only used the ODBC driver to download delta records to a local database and query from that but agree that getting data out in bulk is challenging.   I would suggest focusing on approaching so keep your downloads smaller instead of asking for every incident in one go (the updated field could be a good start here).   There is also the SNOWMirror product which handles a similar kind of process but in a nicely wrapped product, I've never used it nor do I work for the company who sell it but I hear many good things about it.  

The following link ServiceNow Share allows you to schedule data exports to FTP from your instance.   You are stick record limits again, so you need to look to maybe download records changed every day and import them to a database locally.  

A further alternative is to use SOAP queries into the platform, but you'll still have to page your results depending on your instance settings.

Kind regards,

Simon

[simonlawrence]

Hi Simon -

Thanks for your response.   I'm glad other folks out there have had similar issues to mine.     Is this just "how things are" when one uses a SaaS product like SERVICENOW? In my previous position, the Service Management was kept on a server on-premises - I suppose a bit "old skool" now that everything is being shunted into the cloud, but it did at least mean that we could focus on getting the reports right, instead of having to work out how to get the data in the first place - maybe this is just how things are now - but the ODBC connector just doesn't give the "feeling" of being robust at all.         Anyway - I'll (try to) stop going on about the ODBC thing - i'll only end up ranting.

Interesting you mentioned delta downloading.     The thought has crossed my mind a few times. I suppose this would involve getting a complete download of the data to begin with, and then find the highest sysUpdatedOn field (or whatever it is, cant remember without looking it up), and store it in some local table - future downloads only get records where the remote sysUpdatedOn field is higher than my local "max high date" field,   collect that into some temp processing table, and merge the results into the main "local master" dataset ?

Do you find this is as near to rock-solid as its possible to be, reliability-wise?   (ie: if it fails - its a really unusual event over a few weeks or so?)

My current approach is to download the entire dataset in 6 passes, which seems to be ok-ish - sometimes some of the queries fail - so I was putting some "filthy hack" logic around it in a stored procedure which basically looks like (..and don't laugh..   I know its kinda cheating..):

1 - erase local capture table

2 - download SERVICENOW data to local capture table

3 - was the error for step 2 *not* zero?   if so - go to step 2

4 - was the number of records greater than 170000?   If no -   go to step 2   (this is slightly different for queries 2-6 : "are there >=99.9% of records from query 1? if no - go to step 2)

.. and It seems to be edging closer to reliability.   Obviously there are issues with this approach - if one of the queries doesnt work - my logic (as it stands) puts it into a constant retry.   I'll try tweaking this a little further, and if I cant get this to do what I want - I'll switch my efforts to your recommendations (theres only so long one can bang ones head against a wall, so i'll switch to your wall heheh!)

Thanks again Simon - take care!

[simonlawrence]

Just for anyone's reference, in case it helps with "readers of this thread in the future",     my stored procedure that does the retry in the event of a query failure is posted below, in case it helps, or is useful to someone somewhere somehow:

alter procedure spGetServiceNowCache
as

begin

declare @errorCode int
set @errorCode =0

get1a:
print 'getting 1a'
if exists (select name from sys.all_objects where type= 'u' and name='inc1a') drop table inc1a   --drop INC1A cache table
if not exists (select name from sys.all_objects where type= 'u' and name='inc1a') select * into inc1a   from sn_IncidentManagement1a --create INC1A cache table
set @errorcode = @@error --get errorcode into variable
if @errorCode <> 0 goto get1a --check for failure - if so - try again!   (this is an attempt to mitigate against poor SERVICENOW ODBC reliability)
if   (select count (*) from inc1a) <= 170000   goto get1a --to few records?   try asgain!

set @errorCode =0 --reset eerrorCode

get1b:
print 'getting 1b'
if exists (select name from sys.all_objects where type= 'u' and name='inc1b') drop table inc1b
if not exists (select name from sys.all_objects where type= 'u' and name='inc1b') select * into inc1b   from sn_IncidentManagement1b
set @errorcode = @@error
if @errorCode <> 0 goto get1b
if (select count (*) from inc1b) <= (select cast((count(*)*0.999)as int) from inc1a) goto get1b --records must be within 99.9% of record count of INC1A otherwise.. refetch!

set @errorCode =0

get2a:
print 'getting 2a'
if exists (select name from sys.all_objects where type= 'u' and name='inc2a') drop table inc2a
if not exists (select name from sys.all_objects where type= 'u' and name='inc2a') select * into inc2a   from sn_IncidentManagement2a
set @errorcode = @@error
if @errorCode <> 0 goto get2a
if (select count (*) from inc2a) <= (select cast((count(*)*0.999)as int) from inc1a) goto get2a

set @errorCode =0

get2b:
print 'getting 2b'
if exists (select name from sys.all_objects where type= 'u' and name='inc2b') drop table inc2b
if not exists (select name from sys.all_objects where type= 'u' and name='inc2b') select * into inc2b   from sn_IncidentManagement2b
set @errorcode = @@error
if @errorCode <> 0 goto get2b
if (select count (*) from inc2b) <= (select cast((count(*)*0.999)as int) from inc1a) goto get2b

set @errorCode =0

get3a:
print 'getting 3a'
if exists (select name from sys.all_objects where type= 'u' and name='inc3a') drop table inc3a
if not exists (select name from sys.all_objects where type= 'u' and name='inc3a') select * into inc3a   from sn_IncidentManagement3a
set @errorcode = @@error
if @errorCode <> 0 goto get3a
if (select count (*) from inc3a) <= (select cast((count(*)*0.999)as int) from inc1a) goto get3a

set @errorCode =0

get3b:
print 'getting 3b'
if exists (select name from sys.all_objects where type= 'u' and name='inc3b') drop table inc3b
if not exists (select name from sys.all_objects where type= 'u' and name='inc3b') select * into inc3b from sn_IncidentManagement3b
set @errorcode = @@error
if @errorCode <> 0 goto get3b
if (select count (*) from inc3b) <= (select cast((count(*)*0.999)as int) from inc1a) goto get3b

end

The SN_INCIDENTMANAGEMENTxx queries are a little different from each other - but they all follow the same pattern, so I'll include one here too:

ALTER view sn_IncidentManagement1a
as
--V1000
--Fetches main SERVICENOW table data for problem management records via ODBC and presents them to MSSQLSERVER as an easy-to-use VIEW object.
select
number,
description,
short_description,

priority,
dv_priority,
impact,
dv_impact,
opened_at,
dv_opened_by,
dv_assignment_group,
dv_assigned_to,
reassignment_count,
closed_at,
sys_updated_on, --when last modified
sys_updated_by, --who last modified
current_timestamp as sqlInsertDate
from
openquery --numerics need to be used with CAST other MSSQLSERVER has "precision exceeds max" error which is why not using [SELECT * FROM OPENQUERY(....   ]
  (
    z1,
    'select
      number,
      description,
      short_description,
      cast(priority as int) as priority,
      dv_priority,
      cast(impact as integer) as impact,
      dv_impact,
      opened_at,
      dv_opened_by,
      dv_assignment_group,
      dv_assigned_to,
      cast(reassignment_count as integer) as reassignment_count,
      closed_at,
      sys_updated_on,
      sys_updated_by
      from incident'
    )

Note the the CAST operations - if you are getting data over SERVICENOW ODBC and try a SELECT * FROM..     operation on the tables - chances are you'll get an error from MSSQLSERVER about a precision error. To deal with that, you need to CAST the field to a datatype that works - INTEGER in most cases).   As youre having to CAST the field, you cant use "SELECT *.. " in the OPENQUERY statement.

Anyway - there you go guys.   Good luck!

-Si