Currently, we have SQL Federated DB, divided into 10 fragments in approximately equal parts of the data, filtered by client ID.
We are currently experiencing performance issues that perform filtered queries, for example, when executing a query for a particular client, it may take more than 3 minutes to return 4,000 rows in some shards. However, executing the exact same request in an unfiltered connection with the same fragment returns within 4 seconds. One noteworthy aspect is that slow-moving fragments tend to contain more customers, albeit with less data. The most likely performance inhibitor (I believe) will also index something related to a filtered / unfiltered compound.
When searching around, I did not find much information about query performance at different stages / specific indexing strategies for shards (except for Azure, apparently, indexed views are not supported). My impression (and therefore the need for clarification) is that the indices apply to all members of the shard, and not to the member by element.
If the first, then we have a bit of a pickle, except that he is reviewing this special fragment, which does not make sense, given the only difference is the number of customers, not the size of the data. A few things we're going to try explicitly add a filter to the indexes or even add a filter to each query. It is safe to say that we are not happy moving away from the filtered connection.
Has anyone else encountered this problem, or could have indicated some direction that the unfiltered connection is significantly superior to the filtered connection?
Thanks in advance...
source
share