There will be better performance with In-memory table for sure. (Hence if you are going with Live connection then the in-memory table will be handy)
But if you really want to compare then kindly take help of Performance Recording
Why Use a Tableau Data Extract? --->TDE or Live? When to Use Tableau Data Extracts (or not) | Drawing with Numbers
Please mark the answer if helpful
Thanks Ritesh. But, the answer I am looking for is some real examples/ scenarios and Tableau developer's perspective on where it shines and where it does not from somebody who has used used SQL in memory tables in Tableau. Currently I don't have access to SQL server version that supports the in memory and this would be one of the reasons that I would want to move to newer versions of SQL server and use it with Tableau.