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

[simonw]

Hi Simon,

What I do with the delta download process is hold a table which tells me the point a previous table begin to synchronise and use this to look at this to query the updated field in each of the tables I'm synchronising.   As I do this in code, it just throws an exception if anything happens and I don't update the last synchronised table.   Although I only download quarter of the fields in the incident table, we're running at 5-10 minutes behind the live platform about 95% of the time, and any catch up is quite neatly handled.

I'd have to double check the code but I seem to recall just using paging and a loop, so we did something like select the first 10,000 records ordered by sys_updated_on and loop through this.

Kind regards,

Simon

[simonlawrence]

I'll bookmark this.. I suspect I'll be coming back to this in the future.     Well, so far - my 6 queries and the "try , try and try again!" logic seems to be holding. For how long - don't know.   If the problems I'm having with ODBC are primarily down to data volumes being transferred - then eventually as we generate more and more INC records the volumes returned by the queries will begin to become unreliable again.

Your proposal sounds much more robust - so now this is.. apparently.. working (don't want to jinx it..) I'll be able to look at this later and not under "distress" conditions. I do like the bit where you said about being just 5-10min behind live - that's nifty.. and that's just through continual "whats changed?"   .. update local... "whats changed?" .. update local     every minute or so via ODBC?

[simonw]

Pretty much, yep.   To keep it as simple as possible I retrieve all updated records, delete the local copy and insert the new.   That saves having to work out which field actually changed and the same logic can apply to any table on the platform which has an updated field without any modification.  

[simonlawrence]

Si -

Youre a star.   I'm liking that method. Nifty sidestep of the problem of working out what actually changed. Heh.

Added to my "stuff I'm gonna do" list.

Rock On!

-S.

[simonlawrence]

HI Simon -

Just thought id give you a quick update - ive followed your advice and made a delta-update method.   Its vastly superior - its more robust as it needs less data per query, I just run it very frequently (currently set to every 5min), but I add or update about 30-40ish columns at a time - and it seems stable.

(at least, a LOT more stable than my previous approach).

Frankly I think SERVICENOW should be publishing this method (or at least some example code) as standard, as I would expect this is a standard "thing" most folks are going to want to do, but it works - and just like your approach, its pretty darn near real-time accurate too.   Awesome.

Anyway - that was it - just thought id update you.

-Si