A Byte of Coding Issue 362

Howdy,

If you haven’t noticed already, I’ll occasionally include links to other interesting newsletters at the end of the newsletter (after the third article). Feel free to check those out!

Anyway, here’s the issue.

The Hunt for the Missing Data Type

Published: 2 March 2024

Tags: data types, graphs

Hillel Wayne explains why graphs aren’t more readily available as part of the core data types in most programming languages.

Some highlights:

• too many different types of graphs makes it difficult to choose which ones to include

• too many different types of implementations of graphs

• general graph solutions typically have too poor performance to be useful for specific problems

Friendly Deletes in SQL Server

Published: 1 March 2024

Tags: sql server, database

Ben Johnston describes alternative approaches approaches to set based deletes (delete based on a query) in sql server (due to performance issue with large deletes).

Some highlights:

• “Several smaller deletes can actually be faster than a delete in a single, set-based transaction”

• primary issue with “friendly deletes” is that they’re more complicated than a set delete based on a query

• offers six alternatives and their performance, including deleting in a single transaction, delete loop, dedupe with CTE, dedupe with partition, dedupe with static sized loop, and dedupe using natural increment

Buffer analysis when using EXPLAIN ANALYSE in Postgres

Published: 15 September 2023

Tags: postgres, database, optimization

Will Jessop illuminates the uses of the BUFFERS option when running EXPLAIN ANALYSE on a query in postgres.

Some highlights:

• “Cutting down buffer usage can be a great way to decrease IO resource usage for a query”

• the option shows where buffer usage is largest, allowing you to target that part of the query for optimization

• you don’t want your query storing information on disk, since that’s a lot slower than having it in memory (in the 10x magnitude slower)