Think in Sets: Writing High-Performance SQL the Declarative Way

[Lomanu4]

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

Think in Sets: Writing High-Performance SQL the Declarative Way

“SQL is not about loops—it's about logic. The sooner you let go of procedural thinking, the faster your queries fly.”

If you’ve come from a programming background, chances are your instinct is to iterate. But SQL wasn’t designed for that. SQL speaks the language of sets, and when you think in sets, your queries become:

• Faster
• More readable
• Easier to maintain

In this article, we’ll compare procedural vs set-based logic with clear examples and best practices.

The Problem: Updating Order Statuses


Scenario: Mark all Pending orders as Shipped.

Procedural Style with Cursors (SQL Server)


DECLARE order_cursor CURSOR FOR
SELECT id FROM Orders WHERE status = 'Pending';

DECLARE @id INT;
OPEN order_cursor;
FETCH NEXT FROM order_cursor INTO @id;

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Orders SET status = 'Shipped' WHERE id = @id;
FETCH NEXT FROM order_cursor INTO @id;
END

CLOSE order_cursor;
DEALLOCATE order_cursor;

? This runs row-by-row. It’s slow, error-prone, and hard to maintain.

Set-Based Solution


UPDATE Orders
SET status = 'Shipped'
WHERE status = 'Pending';

? One line. Thousands of rows. Zero loops.

Looping vs Thinking in Sets: A Comparison

Task	Procedural	Set-Based
Update rows	Loop through one-by-one	Single UPDATE with WHERE
Aggregate totals	Initialize, add in loop	Use SUM(), AVG(), GROUP BY
Join data	Multiple selects	Use JOIN
Conditional logic	IF, WHILE, FOR blocks	CASE, COALESCE, NULLIF
Ranking or row numbering	Temp tables, loops	ROW_NUMBER(), RANK(), DENSE_RANK()

Set-Based Aggregation Example


Goal: Get total revenue per customer.

Procedural Pseudo-code


DECLARE @customer_id INT, @revenue DECIMAL;
-- Loop over customers, sum revenues manually
Set-Based


SELECT customer_id, SUM(amount) AS total_revenue
FROM Orders
GROUP BY customer_id;

Much faster. Easier to extend. Compatible with indexes.

Common Procedural Traps in SQL

1. Row-by-row subqueries

SELECT name, (SELECT COUNT(*) FROM Orders o WHERE o.customer_id = c.id)
FROM Customers c;

Replace with JOIN + GROUP BY.

1. 
   Cursor-based ETL workflows
   Use MERGE, INSERT ... SELECT, or window functions.
   
   
2. 
   Using temp tables for simple filters
   Use WHERE, IN, EXISTS, or INTERSECT.
   
   
3. 
   Running aggregates in loops
   Use analytic functions (SUM() OVER, etc.).
   

Set-Based Power Patterns

Pattern	Example
Multi-row update	UPDATE ... FROM ... JOIN
Merge/Upsert	MERGE or INSERT ... ON CONFLICT
Ranking	ROW_NUMBER() OVER (PARTITION BY ...)
Conditional projection	SELECT CASE WHEN ... THEN ... END
Aggregates	SUM(), MAX(), COUNT()
Window aggregates	SUM(amount) OVER (PARTITION BY customer)

Final Thoughts: SQL is Declarative, Not Procedural


To truly master SQL:

• Think in sets, not steps
• Use the language of logic, not loops
• Trust the query planner, not imperatives

“Good SQL doesn’t loop. It reasons.”

If you're still writing SQL like it’s JavaScript, now’s the time to shift. The result? Cleaner, faster, scalable queries.

#SQL #Performance #BestPractices #Optimization #SetBasedSQL #NoLoops #DataEngineering #DeclarativeSQL

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.