First, what version are you playing with and are you using Tableau Server as well?
Tableau is very scalable, horizontally and vertically. Much about performance is how the report is created so it sounds like the issue is with the report creation. Using Custom SQL is definitely something you wouldn't want to do, even if you are a master at the specific language for the data source you're connecting to. Tableau isn't leading the Gartner reports and becoming more and more enterprise-ready just on it's good looks
We are using IBM DB2 Blu to stage some of our data and use Tableau to report on it. Billions of rows in less than 3 seconds. Tableau is up to the task more than Cognos and TM1 -- we know because they failed and with less data!
Tableau does have caching and on the Tableau Server it is adjustable.