Slow downloads using ODBC driver to connect to Shopify

nrgins

Member
Local time
Today, 09:43
Joined
Jul 12, 2016
Messages
46
I'm connecting to Shopify via the Devart ODBC driver and Microsoft Access. I'm finding some very long download times.

For example, using a pass-through query in Microsoft Access, with my DSN name in the connection string, I am sending this statement to Shopify:

Code:
Select Sku, InventoryQuantity From ProductVariants
Where Id=32459335565411;

And that takes 80 seconds to return a single value!

I find the times are about the same if I use a regular query connected to a linked table

Any idea what's going on or what I could try to get this to work better?

Note that ID is the primary key field in that table, and the table only has about 500 records in it. Also, my Internet connection is at about 250 Mbps.

Thanks!
 
Last edited:
Have you considered using their API instead?
 
Have you considered using their API instead?
I have. But I would prefer to use the odbc tables instead. There should be a reason why it's this slow. And I checked the Devart support forum and I didn't see a lot of posts about slowness. So there must be something going on, either with my machine or something I'm doing wrong. I have a hard time believing that a popular product like their odbc drivers would have such horrendous results and still be a saleable product.
 
I experienced this with a few hosting providers, it turned out they were blacklisting the IP of the computers on and off because they were receiving random requests on multiple ports of them. Those computers were part of a botnet.

You might want to ask Shopify why your queries are taking so long, they probably know.
 
I experienced this with a few hosting providers, it turned out they were blacklisting the IP of the computers on and off because they were receiving random requests on multiple ports of them. Those computers were part of a botnet.

You might want to ask Shopify why your queries are taking so long, they probably know.
Thanks, I'll do that. I did post a message in the Shopify help forum. I don't know if a staff member will reply. They don't seem to have a place to open a support ticket, only a place to chat with support (which I hate). So I'll see what happens.

Interesting, the first time I ran the query, it took 2 seconds to return a value, which is what you'd expect. Then I tried it for a dozen values at once, using IN(). That took 80 seconds. After that, even doing it for one value took 80 seconds. So I wonder if that was it?

Also, I had a thing running on my computer (Net Uptime Monitor) which tracks when the network is down, because I wanted to see if my ISP was having downtimes. It pings Google, Level2, and Cloudfare every 2 seconds to test connectivity. I wouldn't think that that would be seen by Shopify as bot activity, but perhaps?
 
They don't seem to have a place to open a support ticket, only a place to chat with support (which I hate). So I'll see what happens.
Their support team has helped me a lot in the past, but they can't do anything if it's a third party app. If so, then try asking the developers of that third party app.

Interesting, the first time I ran the query, it took 2 seconds to return a value, which is what you'd expect. Then I tried it for a dozen values at once, using IN(). That took 80 seconds. After that, even doing it for one value took 80 seconds. So I wonder if that was it?
Those delays sounds like the delays I experienced. First it was nice, then it was slow and then I just couldn't connect anymore. I can't tell you whether it is the same.

Also, I had a thing running on my computer (Net Uptime Monitor) which tracks when the network is down, because I wanted to see if my ISP was having downtimes. It pings Google, Level2, and Cloudfare every 2 seconds to test connectivity. I wouldn't think that that would be seen by Shopify as bot activity, but perhaps?
If it only pings Google, Level2 and Cloudfare, but not Shopify, I don't see why that would be the culprit. But it wouldn't hurt if you get a free tier of Fiddler to check what your computer is sending and receiving under the hood.
 
Their support team has helped me a lot in the past, but they can't do anything if it's a third party app. If so, then try asking the developers of that third party app.
I've also contacted the support team of the app. But, still, if my IP is blacklisted or graylisted, then Shopify would be the ones to know, not the 3rd party app, right?
 
I've also contacted the support team of the app. But, still, if my IP is blacklisted or graylisted, then Shopify would be the ones to know, not the 3rd party app, right?
Yes, the Shopify guys are the ones to know if your IP is listed somewhere. You really want to discard that. If they're not to blame, then it's good to check what the developers of the 3rd party app know about that issue. I suppose you've already conducted a few Google searches about that. If you still haven't, then go ahead and see what others know.
 
Yes, the Shopify guys are the ones to know if your IP is listed somewhere. You really want to discard that. If they're not to blame, then it's good to check what the developers of the 3rd party app know about that issue. I suppose you've already conducted a few Google searches about that. If you still haven't, then go ahead and see what others know.
Yeah, I couldn't find anything through Google or ChatGPT.
 
You could continue troubleshooting this by using another computer and seeing if the same happens there while you get answers from both Shopify and the 3rd party app.
 
Yes, I considered that. Only thing is, the license is for only one computer. And the only other license is on the client's computer, and I don't want to bring him into this yet.

But... you just gave me an idea. I could use my phone as a hotspot and connect through my phone with a different IP, rather than through my home's wifi. So, yeah, I'll try that. Back in a minute or two.....
 
ODBC drivers I have looked into connect to online resources via their API. I thought that was the case with Devart and Salesforce but I don't know about Shopify.

I don't have any direct experience but some ODBC drivers to online resources will download the whole table when selecting one record when using a four part (Server-Database-Schema-Table) name from SQL Server as a linked server. The recommendation is to use OPENQUERY instead.

Maybe Access just doesn't have the capacity to do a pass though like that via the ODBC? Do you have SQL Server installed to see if OPENQUERY makes any difference?
 
You could continue troubleshooting this by using another computer and seeing if the same happens there while you get answers from both Shopify and the 3rd party app.
Nope. No difference. Using a different IP with my phone's hotspot the results were the same. I even created a new database and tried it there. Same. So it's not the IP. Of course, it could be the MAC address or something else. But doesn't seem to be the IP.
 
ODBC drivers I have looked into connect to online resources via their API. I thought that was the case with Devart and Salesforce but I don't know about Shopify.

I don't have any direct experience but some ODBC drivers to online resources will download the whole table when selecting one record when using a four part (Server-Database-Schema-Table) name from SQL Server as a linked server. The recommendation is to use OPENQUERY instead.

Maybe Access just doesn't have the capacity to do a pass though like that via the ODBC? Do you have SQL Server installed to see if OPENQUERY makes any difference?
Yes, the Devart ODBC driver uses Shopify's API and translate that into tables for linking into Access or connecting through a pass-through query.

ODBC drivers will interpret the command given to them and only pull the data that's needed. Unless there's a heterogeneous join. Then it'll pull the entire table.

As noted above, the first time I ran the query, it returned a result in 2 seconds. Then I tried multiple IDs at once using IN() and it slowed down at that point and afterwards, even for a single ID pull.
 
Well, here's an interesting development.

I tried getting the same product variant info from shopify using the API, and it returned 404 -- not found -- even though the ODBC driver pass-through query returned a value in 80 seconds.

So then I tried getting product info, rather than product variant info, through the API, and it returned values. (And I confirmed that the ProductVariantID I was using with the first API call was the same as one of the variants that the second call returned.)

So I tried using a pass-through with ODBC to get product info, and it returned a value immediately, though a similar query for product variant took 80 seconds and failed completely via the API.

And, as noted previously, the product variant pass-through query initially only took 2 seconds the first time it was run.

So this is interesting.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom