I have an API endpoint that runs a query. For some reason, when I hit the production endpoint the query times out (after 2 minutes) but when I hit the same endpoint locally with my local environment connected to the same production DB the query runs in a couple of seconds. Is there a reason why this might happen? The issue is definitely not a connectivity issue because when I hit the same endpoint (on prod) with a different token it doesn't timeout. It's only some tokens that timeout on prod (yet when I run it locally connected to my prod DB it runs in a couple of seconds)
5
-
1How does the data between prod and your local server compare? Is there more data in production?– SE1986Commented May 5, 2022 at 21:10
-
I'm not using my local DB I'm connecting my local web server to the production DB– MosheCommented May 5, 2022 at 21:13
-
1@Ronaldo There is no local DB at play here, OP stated "connected to the same production DB". Moshe, are there different connection strings at play, particularly between the different tokens?– J.D.Commented May 5, 2022 at 21:43
-
No. Both using the same connection string– MosheCommented May 5, 2022 at 22:18
-
Hmm unfortunately this one's leaving my head scratching a little bit. By any chance are you using EF Core or some other ORM that generates the SQL query for you? If so you should try to get a copy of the generated SQL in the slow and fast case. Sometimes ORMs can do cheeky things when generating the SQL code. Other than that, you can review Slow in the application, fast in SSMS? which is a comprehensive guide on things that can result in similar outcomes as what you're experiencing.– J.D.Commented May 6, 2022 at 0:33
Add a comment
|
1 Answer
Assuming your prod environment is setup with AG listener scaled across multi subnet, with such multi subnet setup the intermittent timeouts would happen without MultiSubnetFailover=True setting as part of your connection string.
Also, setting appropriate values for RegisterAllProvidersIP and HostRecordTTL will help controlling the default behavior, especially with environments that are setup with multi subnet.
When RegisterAllProvidersIP = 1, any clients whose connection strings do not use MultiSubnetFailover = True, will experience high latency connections. This occurs because these clients attempt connections to all IPs sequentially. In contrast, if RegisterAllProvidersIP is changed to 0, the active IP address is registered in the Client Access Point in the WSFC cluster, reducing latency for legacy clients. Therefore, if you have legacy clients that need to connect to an availability group listener and cannot use the MultiSubnetFailover property, we recommend that you change RegisterAllProvidersIP to 0.
Hope this helps and resolves your problem.
