DataFrame API versus Spark SQL performance?

Summary

• Spark SQL and the DataFrame API run on one engine. The Spark SQL guide says "the same execution engine is used, independent of which API/language you are using to express the computation."
• Both go through the Catalyst optimizer, so check any two versions of a query by comparing their plans with explain() or EXPLAIN.
• Caching, join hints, partitioning and session settings apply to both APIs. The Spark tuning guide covers "DataFrame or SQL workloads" together.
• Performance differences come from what is inside the query: Python UDFs, RDD code, or a plan built up with many withColumn calls.
• Choose by readability and team skills, and mix the two with spark.sql() and temporary views.

DataFrame API versus Spark SQL performance?
Spark documents no engine-level performance difference between the two. 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." The Databricks post that introduced DataFrames adds that "DataFrames go through the Catalyst optimizer, enabling optimized execution similar to that of Spark SQL queries." Choose by readability and team skills, and look inside the query when performance matters.

Do the DataFrame API and Spark SQL use the same engine?

Yes. Spark SQL is a Spark module for structured data processing, and its interfaces give Spark "more information about the structure of both the data and the computation being performed", which Spark uses "to perform extra optimizations." A DataFrame is "a Dataset organized into named columns", available in Python, Scala, Java and R, and spark.sql() returns its result as a DataFrame. DataFrame operations and SQL both go through the Catalyst optimizer.
To compare two versions of a query, print both plans:

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

df.filter(df["age"] > 21).groupBy("age").count().explain()
spark.sql("SELECT age, count(*) FROM people WHERE age > 21 GROUP BY age").explain()

explain() prints only the physical plan by default. Pass mode="extended" to print logical and physical plans, or run EXPLAIN EXTENDED in SQL.

Which tuning techniques apply to both APIs?

The Spark tuning guide opens with "Spark offers many techniques for tuning the performance of DataFrame or SQL workloads", and lists caching data, altering how datasets are partitioned, selecting the optimal join strategy, and giving the optimizer additional information. Caching, join hints and session settings each have a form in both APIs.

Technique	DataFrame API	Spark SQL
Cache	dataFrame.cache() or spark.catalog.cacheTable("tableName")	CACHE TABLE
Broadcast join	.hint("broadcast")	/*+ BROADCAST(r) */
Session settings	spark.conf.set	SET key=value
Inspect the plan	explain()	EXPLAIN

The broadcast hint looks like this in each API:

spark.table("src").join(spark.table("records").hint("broadcast"), "key").show()

SELECT /*+ BROADCAST(r) */ * FROM src s JOIN records r ON s.key = r.key

Settings are per session, so they apply to both. spark.sql.shuffle.partitions defaults to 200, spark.sql.autoBroadcastJoinThreshold defaults to 10485760 bytes (setting it to -1 disables automatic broadcasting), and Adaptive Query Execution (spark.sql.adaptive.enabled) has been on by default since Spark 3.2.0.

When can performance differ between the two?

If two versions of a query run differently, compare their plans first. These documented causes do not depend on the API you picked.

Cause	Applies to	What the docs say
Python UDF	Both, because spark.udf.register registers a Python function "as a SQL function"	Scalar Python UDFs "rely on cloudpickle for serialization and deserialization, and encounter performance bottlenecks, particularly when dealing with large data inputs and outputs"
Dropping to the RDD API	Neither, because you leave both APIs	The Spark SQL interfaces give Spark extra information about the data and the computation, which the basic RDD API does not
Many withColumn calls	DataFrame API	Calling it in loops "can generate big plans which can cause performance issues and even StackOverflowException"; use select() with multiple columns at once
Different hints, caching or settings	Both	These change the plan, so compare it with explain()

How does Databricks fit in?

Photon is "the Databricks-native vectorized query engine that accelerates your SQL workloads, DataFrame API calls, ETL pipelines, and stateless streaming workloads." Per the Photon docs, "the Apache Spark query optimizer (Catalyst) still plans your query, but Photon takes over at the execution layer", so the API you write in does not decide whether Photon applies. Photon "doesn't support UDFs (User Defined Functions), RDD APIs, or Dataset APIs", and when a workload hits an unsupported operation the compute switches to the Spark runtime for the remainder of that operation.

FAQs

Should I use SQL for performance?

No. Use the API your team reads best, and compare plans with explain() if a query is slow.

Can I mix both in one job?

Yes. spark.sql() returns a DataFrame, and createOrReplaceTempView registers a DataFrame as a session-scoped view that SQL can query.

Which explain modes exist?

DataFrame.explain accepts simple, extended, codegen, cost and formatted. In SQL the statement is EXPLAIN [ EXTENDED | CODEGEN | COST | FORMATTED ] statement.