SnowMirror vs ODBC driver

[Casey12]

My company is experiencing performance issues with the ServiceNow ODBC driver.  As I understand it, this driver is not a real driver -- it is merely a wrapper around the out-of-the-box (OOTB) ServiceNow SOAP API, which is the cause of its poor throughput.  It was recommended to me to look into SnowMirror to resolve our throughput problem.  As I understand it, SnowMirror also uses the OOTB ServiceNow SOAP API.  But, somehow, it has better performance than the ODBC driver (or so it's claimed).  Is this true?  If so, how can this be, given that they both use the OOTB ServiceNow SOAP API (and are, therefore, effectively the same thing)?

[Casey12]

Let's take an identical query in SnowMirror (or similar tool) and the SN ODBC driver.  Say we've already tuned this query (e.g., to leverage concurrent threads), and that it's just deltas.  Which tool will have better throughput?  SnowMirror?  Or, ODBC?  Or, perhaps, will the throughput be the same?

[SanjivMeher]

I am not sure if you are doing a delta query or the complete database query while using ODBC. I think snow mirror does a delta query based on Updated field, which could be the reason it is faster.

[Casey12]

My data flow via ODBC is identical to that which I would use in SnowMirror.  That is, I use some API to take SN to an ODS, and then put my reporting tools on top of the ODS.  The SN ODBC driver and SnowMirror both use the OOTB SN SOAP API.  And, to be sure, both methods (ODBC and SnowMirror) are (would be) ran in the same pattern.  That is, I initially replicate the tables into ODS (the longest part in replication, but necessary), and then I just pick up deltas (via some update timestamp field).  However, even with the same architectural pattern, SnowMirror claims that it has faster throughput than ODBC.  I don't know how this can be true, since both ODBC and SnowMirror use the SOAP API.  I'd like to know if anybody can / has validate(d) this claim, as I am not able to test SnowMirror for quite some time.

[poyntzj]

So until now, I have taken it that you were using the ODBC and querying Servicenow directly each time and not doing some sort of data retrieval to a local DB

You can get a trial of SnowMirror so you can try this out - can even run on a local DB if you do not have another SQL server you can run

I may suggest that there could be performance issues between SOAP and REST as I am prettty sure that SnowMirror uses REST over SOAP

certainly with REST you can tell it to send the data as gzip.  Any better than HTTPS encryption ? not sure

[Casey12]

I almost never put my reporting tools directly on top of my applications (unless I truly need low latency).  I nearly always put some sort of mirror DB / ODS between the application and my reporting tool.  And, to load this ODS, I always follow a delta synch strategy (never a full synch strategy, except for the initial load of the ODS) to minimize load on the application and to boost performance.  My usage of the SN ODBC driver is no different.

Historically, SnowMirror has used the SN SOAP API.  As of v4.1, you can now use the SN REST API, but it still defaults to the SN SOAP API.  There may be a bit of a performance boost by using REST instead of SOAP, but I'm sure it's minimal.  In either case, though, the SN SOAP and REST APIs are transactional; they're not meant for bulk extraction.  In other words, they're not scalable.

My issue remains, though.  Because ODBC and SnowMirror both use the SN SOAP API (at least, by default), for identical replication strategies (i.e., delta synch) and concurrency leveraging (e.g., 3 parallel threads), how is it that SnowMirror can achieve higher throughput than ODBC?

I have read (and experienced) that ODBC's throughput can go as low as 10 rows / s (~3.6k rows / hr), whereas SnowMirror's throughput can achieve 1M+ rows / hr.  Why is there such a huge difference, since they are both based on the SN SOAP API?  I would test this myself, but, unfortunately, my company does not allow me to download the trial version of SnowMirror without significant security approval, which takes a long time.