Press "Enter" to skip to content

Day: March 25, 2024

Analyzing Aircraft Routes

Published 2024-03-25 by Kevin Feasel

Mark Litwintschik performs a deep dive into aircraft telemetry:

In November, I wrote a post on analysing aircraft position telemetry with adsb.lol. At the time, I didn’t have a clear way to turn a series of potentially thousands of position points for any one aircraft into a list of flight path trajectories and airport stop-offs.

Since then, I’ve been downloading adsb.lol’s daily feed and in this post, I’ll examine the flight routes taken by AirBaltic’s YL-AAX Airbus A220-300 aircraft throughout February. I flew on this aircraft on February 24th between Dubai and Riga. I used my memory and notes of the five-hour take-off delay to help validate the enriched dataset in this post.

Click through for Mark’s analysis using DuckDB and Python.

Comments closed

Refreshing a View in SQL Server

Published 2024-03-25 by Kevin Feasel

Chad Callihan hits the reset button:

I recently encountered a question related to views: what happens when you make a change to the table that a view is based on? For example, if you change a column from VARCHAR(8) to VARCHAR(20), how does the view handle that change? You might expect the view to update, but it won’t do it on its own. You have to refresh the view.

Let’s look at a mocked up example.

Click through for that example. You’d think it could do so on its own, but nope. I suppose the reason you can’t is probably related to linked server and external object references in views, where that remote resource can change schema and SQL Server wouldn’t know about it. Perhaps also the idea that a person may be authorized to change a table in one database or schema but shouldn’t be authorized to modify (even to refresh?) a particular view that references the table.

Comments closed

Regex Support in Azure SQL DB

Published 2024-03-25 by Kevin Feasel

Abhiman Tiwari has a big announcement:

We are pleased to announce the private preview of regular expressions (regex) support in Azure SQL Database. Regex is a powerful tool that allows you to search, manipulate, and validate text data in flexible ways. With regex support, you can enhance your SQL queries with pattern matching, extraction, replacement, and more. You can also combine them with other SQL functions and operators to create complex expressions and logic.

This is something I’ve wanted to see in SQL Server for years, and I’m excited that there’s official support now. Prior to that, you could use SQL# to perform some regular expression operations using the CLR, but as long as performance is reasonable on these, it’s a huge feature to include.

Comments closed

Sorting by Large Columns in SQL Server

Published 2024-03-25 by Kevin Feasel

Aaron Bertrand has a clever trick:

In the most basic cases, we can implement this functionality in SQL Server by using OFFSET/FETCH. The problem is that anything that uses TOP or OFFSET will potentially have to scan everything in the index up until the page requested, which means that queries become slower and slower for higher page numbers. To achieve anything close to linear performance, you need to have a narrow, covering index for each sort option, or use columnstore as Erik Darling recommends here, or concede that some searches are just going to be slow. Throw in additional filtering, pulling data from other tables, and letting users dictate any sort order they want, and it becomes that much harder to tune for all cases.

I have a lot that I want to say about paging, and I will follow up with more content soon. Several years ago, I wrote about some ways to reduce the pain here, and it is long overdue for a refresh. For today’s post, though, I wanted to talk specifically about pagination when you have to order by large values. By “large” I mean any data type that can’t fit in an index key, like nvarchar(4000) or, really, anything that can’t lead in an index and/or would push the key past 1,700 bytes.

Read on for the scenario and how it all works.

Comments closed

An Overview of Postgres’s Planner

Published 2024-03-25 by Kevin Feasel

Cary Huang digs into one phase of Postgres query processing:

When you send a query to PostgreSQL, it normally would go through stages of query processing and return you the results at the end. These stages are known as:

  • Parse
  • Analyze
  • Rewrite
  • Plan
  • Execute

I wrote another blog to briefly explain the responsibility of each query processing stage. You can find it here. In this blog, we will only focus on the “plan” stage or the “planner” module as this is perhaps the most interesting or complex stage if you will. I will share my understanding of the planner module as I investigate its internal workings to handle a simple sequential scan. This will be based on PostgreSQL 16.

Read on to learn what the planner does and how it works, at a high level.

Comments closed