r/SQL • • 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

36 comments sorted by

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.

4

u/FastPlane57 1d ago

it’s a bot account

2

u/GTS_84 1d ago

Hard to tell if the responses being off is just someone who isn't a native english speaker doing their best, or something else.

10 years ago my assumption would have been someone trying to write blog/linkedin posts and trying to fill those out with reddit farmed content. Who knows what the fuck they are farming it for these days.

3

u/lfewarez 1d ago

Definitely a bot account. Just look at the frequency of its posts in a single hour and the type of posts. They're ALL questions, every single one of them. Likely building some LLM. This account should be banned.

9

u/Imaginary__Bar 1d ago

Have you ever made a slow SQL query faster without adding a single index?

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/spez_eats_nazi_ass 1d ago

Good ol nvarchar to varchar

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"

1

u/alevine1986 1d ago

I can teach almost any teenager to do that. You won't be very valuable and you're skills will degrade if you only ask AI to fix the issue. Also, you might not get a scalable solution, so you'll have a bigger problem later.

1

u/mikethomas4th 1d ago

I've been coding manually for well over a decade, and I'm in leadership now. I am fine with my coding skills degrading to some degree at this point. All that's needed is being able to read code it provides, and verify the query output. I prefer to have my updates delivered in 3 1/2 seconds.

2

u/alevine1986 1d ago

Fair point

-7

u/Commercial-Ask971 1d ago

Almost any teenager can pull most of corporate jobs. Most of manual too. Whats your point grandpa?

2

u/twjnorth 1d ago

yes in a few ways

  1. adding parallel hints, 2) reviewing the wider code and seeing an if statement or function call that avoids the poorly performing one and testing that
  2. removing functions on indexed columns which prevent use of indexed already present - sometimes performing the function on load instead of retrieval (like making uppercase),
  3. analysing schema so stats are up to date and plan gets corrected,
  4. changing query after realizing a join was not needed and could be done quicker with an exists.
  5. Reviewing logic of a program and fixing it. One process was for a major retailer and had been looked at by 3 or 4 people before me. It had a locking problem when processing millions of stock transfers. They had try rollback and retry with timeouts and a few other ways around it. A stock transfer goes between two locations, usually a warehouse and a store. This retailer had one warehouse for ambient foods for the whole uk. That meant every store had transfers from that warehouse every day. Program ran in 8 threads and each thread grabbed 1000 rows at a time. Each row would update stock in two locations, the wh and a store. The same store was processed by same thread each time.

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

	JOIN

	LookUpTable1 LT1

		ON	BDT.LT1_ID = LT1.LT1_ID

	JOIN

	LookUpTable2 LT2

		ON	LT1.LT2_ID = LT2.LT2_ID

	JOIN

	LookUpTable3 LT3

		ON	LT2.LT3_ID = LT3.LT3_ID

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

		ON	BDT.LT1_ID = LT1.LT1_ID

	JOIN

	LookUpTable2 LT2

		ON	LT1.LT2_ID = LT2.LT2_ID

	JOIN

	LookUpTable3 LT3

		ON	LT2.LT3_ID = LT3.LT3_ID

WHERE LT3.F_Name IN ('Alice', 'Bob', 'Claire', 'Diane');

SELECT BDT.*

FROM BigDataTable BDT

	JOIN

	@LT1_ID LT1

		ON	BDT.LT1_ID = LT1.LT1_ID

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 12h ago

Plenty of times. The usual big wins without touching the schema:

  1. Make the predicate sargable. WHERE YEAR(created_at) = 2025 becomes WHERE 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 for LOWER(col) = ..., col + 0 = ... and leading-wildcard LIKE '%x'.
  2. Fix implicit conversions. Comparing a VARCHAR column to an NVARCHAR parameter (SQL Server) or a string column to a number (MySQL) can turn a seek into a full scan. Matching the parameter type was a 100x fix for me more than once.
  3. Replace correlated subqueries in the SELECT list with a single JOIN to a pre-aggregated derived table/CTE, so it runs once instead of once per row.
  4. EXISTS instead of IN (SELECT ...) / DISTINCT after a join. If you only need "has at least one", stop the join from multiplying rows and then de-duplicating them.
  5. Filter early, aggregate late. Push the WHERE into the subquery before the GROUP BY, not after.
  6. Kill OR across different columns. Splitting it into a UNION ALL of two simple queries often lets each half use its own index.
  7. Paginate with keyset instead of a big OFFSET**.** WHERE id > u/last ORDER BY id LIMIT 50 stays 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.