1 of 1 people found this helpful
This is kind of the plight of EF scalability issues plus some performance issues in OLEDB Enterprise. My first recommendation for you would be to check out the new RTQP engine which is releasing in the upcoming release of the PI Server but is available now as a CTP feature. There was a lot of effort put into query scalability and overall performance. However, if you want to dig deeper into your current query, enabling level 3 logging on the provider would be the first step. That would give you insight into which join is rate limiting the query and from there we can determine if there are steps that can be taken to improve the query.