[SQL Lab] Reduce number of queries needed to create a chart

[vylc]

The number of query executions needed to get from SQL->Dashboard is inefficient. Stresses databases (more redundant query executions), slows down data analysis (waiting for queries to complete)

Typical workflow to create a chart
1st SQL run --> SQL Lab to execute query and get result sets
2nd SQL run --> Clicking Explore, which instantiates the query as a datasource off of the result set
3rd and more SQL runs --> Changing viz type (because user lands on a count of # of rows upon clicking Explore) and subsequent changes on the datasource all require re-execution

Proposal:
(1) temporarily cache result sets for SQL queries run on a tab? Can be quickly discarded based on last query run on a tab. Persist as long as tab remains open or something reasonable like 24 hours?
(2) If user click on Explore, persist result set in cache, such that subsequent actions don't require re-execution

Open to other suggestions which solve the problem of too many SQL re-executions in order to explore/chart a result set which remains static.

Make sure these boxes are checked before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if there are any.
• I have reproduced the issue with at least the latest released version of superset.
• I have checked the issue tracker for the same issue and I haven't found one similar.

Superset version

Expected results

Actual results

Steps to reproduce

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[john-curry871]

This is pretty relevant. More generally, the results of an SQL query should probably be savable as a data source from the "save as" option. Then can just use alongside any database table as basis for charts.

[amathurcx]

+1