DataFrame API versus Spark SQL: is there a performance difference?

Summary

• Spark documents one execution engine for every API: "the same execution engine is used, independent of which API/language you are using to express the computation."
• SQL text and DataFrame calls are both planned by the Catalyst optimizer, and EXPLAIN EXTENDED shows the parsed, analyzed and optimized logical plans plus the physical plan.
• If two versions of a query run differently, compare their plans first. Python UDFs, RDD code, many withColumn calls, and different hints or caching are documented causes, not the API itself.
• You can mix the two: spark.sql() returns a DataFrame, and createOrReplaceTempView makes a DataFrame queryable from SQL.
• On Databricks, Photon accelerates SQL workloads and DataFrame API calls, and Catalyst still plans the query.

DataFrame API versus Spark SQL: is there a performance difference?
Not from the API itself. The Spark SQL guide says: "When computing a result, the same execution engine is used, independent of which API/language you are using to express the computation." What changes performance is what the query contains, so compare plans before you rewrite code.

How does each API become an execution plan?

Spark SQL's optimizer is Catalyst, "based on functional programming constructs in Scala". The Catalyst post says Spark SQL "begins with a relation to be computed, either from an abstract syntax tree (AST) returned by a SQL parser, or from a DataFrame object constructed using the API", so both kinds of code reach analysis the same way. The Databricks blog post that introduced DataFrames says that "DataFrames go through the Catalyst optimizer".
The four phases are analysis, logical optimization, physical planning and code generation. Logical optimization "applies standard rule-based optimizations to the logical plan", such as constant folding, predicate pushdown, projection pruning, null propagation and Boolean expression simplification. Code generation "involves generating Java bytecode to run on each machine".
You can see these stages. EXPLAIN EXTENDED "generates parsed logical plan, analyzed logical plan, optimized logical plan and physical plan", and DataFrame.explain has simple, extended, codegen, cost and formatted modes. Because spark.sql() returns a DataFrame, you can start in SQL, continue with DataFrame calls and inspect the combined plan:

df = spark.read.json("examples/src/main/resources/people.json")
df.createOrReplaceTempView("people")

adults = spark.sql("SELECT name, age FROM people WHERE age > 21")
adults.groupBy("age").count().explain(mode="formatted")

What is the same and what is different between the two?

Aspect	DataFrame API	Spark SQL
Execution engine	The same engine	The same engine
Optimizer	Catalyst	Catalyst
How you write it	Method calls such as filter, select and groupBy, in Python, Scala, Java or R	A query string passed to spark.sql()
Join hints	.hint("broadcast")	/*+ BROADCAST(r) */
Python UDFs	udf() from pyspark.sql.functions	spark.udf.register registers a Python function "as a SQL function"
Plan inspection	explain()	EXPLAIN

The practical difference is how you write the query, not how Spark runs it.

When do two versions of a query run differently?

Compare the plans first. These documented causes do not depend on the API you picked:

• Python UDFs. They work from either API. Scalar Python UDFs "rely on cloudpickle for serialization and deserialization, and encounter performance bottlenecks, particularly when dealing with large data inputs and outputs." Arrow-optimized Python UDFs use Apache Arrow for "swift data exchange between JVM and Python processes", and you enable them with the useArrow parameter or spark.sql.execution.pythonUDF.arrow.enabled.
• RDD code. The interfaces Spark SQL provides give Spark "more information about the structure of both the data and the computation being performed", which the basic RDD API does not.
• Many withColumn calls. This one is specific to the DataFrame API. Calling it in loops "can generate big plans which can cause performance issues and even StackOverflowException", so use select() with multiple columns at once.
• Different hints, caching or settings. These change the plan in either API.

How does Databricks fit in?

For supported operations, "Photon replaces the JVM-based Spark SQL execution engine with a native C++ runtime", while Catalyst still plans your query. Photon accelerates SQL workloads and DataFrame API calls, and it "works with your existing code with no changes required." It doesn't support UDFs, RDD APIs or Dataset APIs, and it supports stateless streaming only. When a workload hits an unsupported operation, the compute switches to the Spark runtime for the remainder of that operation.

FAQs

Which API should I choose?

Choose the one your team reads and maintains best. The Spark guide says "developers can easily switch back and forth between different APIs based on which provides the most natural way to express a given transformation."

What about the typed Dataset API?

The Dataset API is for Scala and Java. Dataset encoders "use a format that allows Spark to perform many operations like filtering, sorting and hashing without deserializing the bytes back into an object." On Databricks, Photon doesn't support Dataset APIs.

Is DataFrame code in Python slower than in Scala?

For DataFrame operations, the Databricks DataFrame post says: "Since the optimizer generates JVM bytecode for execution, Python users will experience the same high performance as Scala and Java users." Python UDFs are the exception covered above, because scalar Python UDFs "operate one row at a time".

How do I inspect a slow query?

Run explain(mode="formatted") on the DataFrame or EXPLAIN FORMATTED in SQL. The formatted output "generates two sections: a physical plan outline and node details." Then compare it with the plan of the version you expected to behave the same.