During a techsupport call, we found that Connection Pooling may help a lot with the PIOLEDB Linked server performance, this is the situation:
The PI Server and SQL server resides in different locations and has a high PING (around 150ms in average). It takes around 10 seconds to run a simple query to get the tag name and descriptor of tag "sinusoid". Any other query we ran timed out...
See the PIOLEDB provider properties and "Allow inprocess" is already checked. Adding "Connection Pooling=True;" into the provider string of the linked server helps us resolve the performance issue.
The customer, Kirt, asked me to post this in case you run into similar problem and may get help from this.