-
1. Re: Query speeds when using "custom SQL" vs datavase view vs native tableau data source
Matt LuttonJan 8, 2014 7:10 AM (in response to Martin H.)
I assume you created an extract from your Custom SQL? The issue is when the data is not extracted--when using an extract, performance should be similar/same regardless of the data source, I believe.
There's some info from Joshua Milligan at this thread which I think will help you:
http://community.tableau.com/thread/124148
"If connecting live, custom SQL (as opposed to setting up the joins and letting Tableau understand the metadata and then blending) would probably not yield better performance as Tableau will execute the custom SQL every time it needs to get new data from the source. The custom SQL will be wrapped in whatever SQL Tableau needs to aggregate the data to use to generate your view. Of course, Tableau will optimize this process as much as possible, using cache, temp tables, etc... Since you are using an extract, I don't think custom SQL is going to improve or degrade your performance. This is because the custom SQL will only be run once to generate the extract. After that, Tableau will generate and execute its own queries against the extract as determined by your view."
-
2. Re: Query speeds when using "custom SQL" vs datavase view vs native tableau data source
Eric McDonald Jan 8, 2014 7:10 AM (in response to Martin H.)Which database are you using as this will make a difference to the answer?
-
3. Re: Query speeds when using "custom SQL" vs datavase view vs native tableau data source
Martin H. Jan 8, 2014 7:13 AM (in response to Matt Lutton)Extract is clearly the fastest, but I am rather curious on the the other options - I ran the tests with expectations that Tableau's native data sources would be fastest, while the reality was different an CustomSQL/View based data sources performed best after the initial run. It is true that the SQL I used in the view/custom is perfectly optimal select+joins and no fancy stuff, probably the recommendation NOT to use custom SQL comes out of fear that some non-techical person may issue flawed SQL request
-
4. Re: Query speeds when using "custom SQL" vs datavase view vs native tableau data source
Martin H. Jan 8, 2014 7:13 AM (in response to Eric McDonald)Backend is Oracle
-
5. Re: Query speeds when using "custom SQL" vs datavase view vs native tableau data source
Matt LuttonJan 8, 2014 7:28 AM (in response to Martin H.)
2 of 2 people found this helpfulNot sure if you've read this--its pretty clear:
Connecting Through Custom SQL Causes Slow Performance | Tableau Software
My new favorite is using Stored Procedures--they work superbly in Tableau 8.1!
More good info on the topic here: http://community.tableau.com/message/231860?et=watches.email.thread#231860 Pay attention to Robert Morton's comments.
Here's an interesting discussion on Oracle specifically: http://community.tableau.com/thread/126970?start=15&tstart=0
-
6. Re: Query speeds when using "custom SQL" vs datavase view vs native tableau data source
Eric McDonald Jan 8, 2014 7:30 AM (in response to Martin H.)1 of 1 people found this helpfulOracle - should have asked which version...?
From 10g, Oracle caches queries efficiently, so if it's seen the SQL before it uses it's previously parsed version, even if bind variables differ. I would always expect extracts to work faster anyway, and extract times may even be the same. It's really tricky to test this as you have to reset the Oracle shared pool between extracts (warning! - this affect all users on the database). If you are using a direct connection (same shared pool issue applies), then Oracle should keep parsed query in memory, but you may also get a speed improvement if Tableau decides to cache the result as well. There have been some improvements on this front in version 8.1. Tableau doesn't always have to go back to the database every time.
You can also inspect the log files in the Server to see the SQL which is being generated.
I think I would always prefer to connect to database objects directly and avoid custom SQL. Poor performance is usually always due to bad dashboard design rather than database query performance.
There are some useful links below:
Tuning Tableau: http://www.tableausoftware.com/sites/default/files/pages/tcc11-tuningtableaudb.pdf
Tableau Performance Analyzer: http://www.interworks.com/services/business-intelligence/tableau-performance-analyzer
Optimizing Tableau Server Performance: http://kb.tableausoftware.com/articles/knowledgebase/optimizing-tableau-server-performance
-
7. Re: Query speeds when using "custom SQL" vs datavase view vs native tableau data source
Martin H. Jan 9, 2014 12:29 AM (in response to Matt Lutton)Matthew thanks for the links, interesting discussion there as well. I think that simple custom SQL (i.e. with no ODER BY, GROUP BY, sub-queries ) is not a problem and I have been observing exactly that with my experiments.
Further more custom SQL can produce a "narrower" data source that would be faster, one of the links you provides discussed that deselecting columns from T's connection builder does not improve performance, while custom SQL made a difference. I have Tableau desktop v8, maybe it would be worth to try the same with 8.1 and check if it makes difference.
-
8. Re: Query speeds when using "custom SQL" vs datavase view vs native tableau data source
Martin H. Jan 9, 2014 12:31 AM (in response to Eric McDonald)Eric thanks for the details, I should have mentioned Oracle version 11 (not sure about the sub-rev). I will check how 8.1 behaves