Depends what performance you are aiming on; Developer performance or app performance.

In general a direct SQL can perform better than XPath which needs to be converted in SQL. But why the hassle of implementing your own SQL queries, when XPath is out of the box available and performing well in most situations?
Also since XPath is available everywhere in Mendix and most easy to maintain.

Thus my best practice; always use XPath.

If there is a reduction performance; 

1. Optimize XPath
2. Optimize order of queries involved
3. Optimize processing of data, batches or smarter retrieves
4. Identify if use of Indexes are of added value
5. If all above doesn’t work; I might consider SQL. (but even then...)