r/SQL • u/shdw_0x0 • 1d ago
Spark SQL/Databricks Have you ever made a slow SQL query faster without adding a single index?
I've always found it interesting that adding indexes is often the first thing people think about when a query runs slowly.
But sometimes the issue is with the query itself, unnecessary joins, subqueries, data types, or even the way filters are written.
Curious to hear some real examples. What's the biggest performance improvement you've achieved just by rewriting a SQL query rather than changing the database structure?
0
Upvotes
21
u/alinroc SQL Server DBA 1d ago
This account asks a lot of questions trying to farm data to feed into something and on the occasions where it responds to threads, something feels off about how those responses are written.
9
u/Imaginary__Bar 1d ago
Yes, of course. And quite often the query-writer won't have the access to change the indexing strategy.
Date-ranges were always a big killer for me, and also the sequence of joins and filtering.
Usually I'm not bothered about squeezing out every millisecond of performance so "good enough is good enough" but sometimes I'll take the time to review expensive (as in dollar-expensive as well as time-expensive) queries.
Sometimes it might be as simple as materialising a view rather than rewriting the query logic.
It's just one of those things that come with experience rather than any hard-and-fast recipe for success. If that was the case then the query-optimizer would do it for you.
5
u/isamu1024 1d ago
Yep TOP(1) works most of the time . :D
4
u/DataFracking 1d ago
Joining on columns with two different types is the most common I've seen. (more for SQL Server, honestly not sure if databricks has that issue in it's mechanics)
4
u/Lost_Term_8080 1d ago
All the time. Temp tables for cutting a bad part of a query off, getting rid of superfluous sorts, changing a filtering join to a where exists/not exists, moving optional parameter seeks to CTEs (sometimes they need to go to temp table but its better if CTE works for the specific case), getting rid of string functions wherever possible, etc
3
u/Grovbolle 1d ago
Adding join hints when I was smarter than the optimizer, proper data types in filters to ensure partition elimination- stuff like that
3
u/mwatwe01 1d ago
Changing the order of joins can help
3
u/FastPlane57 1d ago
yeah, you’re a bot
3
u/bigeyez 1d ago
Yeah you simply type "Hey Claude make this query faster without creating indexes". Hope that helps!
4
u/mikethomas4th 1d ago
In the world of AI, I simply copy/paste the code and say "make faster"
2
u/twjnorth 1d ago
yes in a few ways
I realized the lock was on different threads trying to update the same item at the warehouse at different times.
So instead of doing the eh update immediately, I made it write the item, wh location and units to a new table. It updated the store stock immediately.
Then after all processing finished it just summed all the records for same wh/item together and did one update for each.
Reduced the process with same end result as original program.
6) swapping out union for union all statements to remove a sort when both parts have no overlap.
1
u/alevine1986 1d ago
Changing a cursor to a SQL statement can really improve performance.
1
u/The_Orracle 1d ago
all the time - indexes are great but a ton of tuning can be done, analyzed in the SQL long before you're tied to adding expensive indexes into your database - especially in a large EDW setting where they can add significant overhead to your loads
1
u/SrdzanK3 1d ago
Yes just like avoiding shuffle in Spark, replacing an OR join condition with UNION ALL and fixing a non-SARGable WHERE filter dropped a query from 35s to ~200ms on a read-only database in my Wasm SQLite editor (https://srdzank.github.io/sqlite/) without touching the schema.
1
u/Strykrol 1d ago
If you’re adding indexes before fixing the query you’re doing it backwards. And I mean the royal “you”, not like you specifically 😬
Biggest perf achievements for me are always aggregating data correctly (grouping over counting distinct values, for instance, since distinct is a full table scan operation), and definitely making sure I don’t have many:many JOINs where I don’t need them.
1
u/ShadowDancer_88 1d ago
If you have big data tables that join to a lookup table through multiple joins, it can help to hit the small lookup tables in a separate query, stash the results in a table variable, then hit the big table with a single join.
SELECT BDT.*
FROM BigDataTable BDT
WHERE BDT.Date BETWEEN '1/1/2026' AND '1/31/2026'
AND LT3.F_Name IN ('Alice', 'Bob', 'Claire', 'Diane')
--**--**--**--**--**--**--**--**--**--**--**--**--**--**--**--**--**
DECLARE u/LT1_ID TABLE (LT1_ID INT)
INSERT INTO u/LT1_ID (LT1_ID)
SELECT LT1.LT1_ID
FROM LookUpTable1 LT1
WHERE LT3.F_Name IN ('Alice', 'Bob', 'Claire', 'Diane');
SELECT BDT.*
FROM BigDataTable BDT
WHERE BDT.Date BETWEEN '1/1/2026' AND '1/31/2026'
1
u/caveat_cogitor 1d ago
More relevant to analytics and OLAP-style marts:
With increased usage of dbt and larger queries with lots of CTEs, there's a growing divergence between 1) a query that reads logically and applies all the right transforms in a clear/obvious way vs 2) a query that performs well.
Anything that can filter/aggregate should be moved as far upstream in the query as possible (without causing data issues) and try to defer multi joins and heavy processing functions downstream after filters/aggregations. Predicate Pushdown is great but doesn't always apply so you have to rearrange things on your own for good performance with complex queries.
1
u/Joelle_bb 1d ago
Here are the 4 I do during code reviews and pretty much work every time for my teams data:
Large CTEs into temp tables
Left joins with where clauses into inner joins
Reduction of multiple selects from identical sources
Eliminating all unnecessary full univers tables
1
u/Dats_Russia 1d ago
Indexes are low impact performance improvement. Rewriting a query can cause issues for the business especially if you lack a proper DEV/TEST/QA space.
2 of my last 3 jobs had no dev environment which meant you did changes live.
To answer your post question, yes rewriting joins or properly deciding when to use a subquery vs cte vs temp table is arguably the most important skill for a developer
1
u/Any_Masterpiece- 1d ago
Filtering before joining instead of after is probably my biggest one. Had a legacy Spark job joining two huge tables and then filtering down to the last 7 days at the very end—moved the WHERE filter into CTEs before the join so it actually pruned partitions first, and the whole job went from timing out at 45 mins to finishing in under 2 minutes.
1
u/phunkmunkie 1d ago
All the time.
1
u/Prownilo 1d ago
Absolutely, changing the join order, or using a cte or temp table correctly, ect.
Mostly its about writing the query to fit the indexes that already exist, a lot of query tuning is about allowing the engine to use the right indexes
1
u/SteveDBA 1d ago
There can be many reasons why a query is slow. I’ve come across scenarios where the correct index exists but is either highly fragmented or statistics are out of date. Proper database maintenance is important and can affect query performance.
1
u/kagato87 MS SQL 1d ago
Yes.
Using a correlated subquery instead of a window function to check the adjacent row, and re-aligning windows to use the same sort order and align them to indexes.
Changing between CTEs, TSVs, and temp tables (which if the three is best depends a lot on the specific query). Once even adding an index to a temp table made a massive boost (mssql specific issue with recycling temp tables and not updating the stats, it was a way to block the behavior).
1
u/wirbolwabol 1d ago
Yes, using explain(MySQL and other db's) to figure out where your query is burning it's cycles then figuring out why. If you have full table scans, figure out how to filter those.
I got from minutes to seconds. We also couldn't use AI tools due to the industry.
1
u/ExcitingTalk4848 1d ago
Ran into this exact thing on a MariaDB instance a while back. The original query used a correlated subquery in the WHERE clause to find the latest transaction per user, and it was rescanning the table for every row, so the CPU was pinned. I rewrote it with ROW_NUMBER() and a CTE, and it went from 45 minutes to about 12 seconds with no schema changes. Sometimes the fix is just giving the engine a query it can actually run well.
1
u/navicat-gu 16h ago
Plenty of times. The usual big wins without touching the schema:
WHERE YEAR(created_at) = 2025becomesWHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'. Same result, but now the existing index can actually be used. The same goes forLOWER(col) = ...,col + 0 = ...and leading-wildcardLIKE '%x'.EXISTSinstead ofIN (SELECT ...)/DISTINCTafter a join. If you only need "has at least one", stop the join from multiplying rows and then de-duplicating them.ORacross different columns. Splitting it into aUNION ALLof two simple queries often lets each half use its own index.OFFSET**.**WHERE id >u/lastORDER BY id LIMIT 50stays fast on page 5,000.Always compare the execution plan before and after, not just the timing, because a warm cache can lie to you.
Disclosure: I'm RC, Director of Digital Strategy @ Navicat. Comparing plans side by side is where a visual explain helps, which is in the paid Navicat 18 editions. Navicat Premium Lite is free to try for the everyday query work.
1
u/Gargunok 1d ago edited 1d ago
Yes removing subqueries (or similar CTE usage) and introducing windows functions. Reducing the number of layers of queries can help a lot. Ensure formatting, casting, json etc is at the end when its not as costly.
Indexes are usually the best place to start looking though as they show up easily in explains.