Press "Enter" to skip to content

Day: December 28, 2023

What’s New in .NET 8

Published 2023-12-28 by Kevin Feasel

Thao Nguyen keeps us up to date on .NET:

Microsoft has announced .NET 8 recently. It emphasized the cloud, performance, full-stack Blazor and .NET MAUI as major highlights of the latest edition of the company’s free, cross-platform, open-source developer platform.

Of special importance to enterprises, .NET 8 is a long-term support (LTS), which means it will be supported and patched for three years as opposed to 18 months for a standard term support (STS) release.

Click through for the list, as it’s good to keep at least one eye on the developers lest they run around unopposed.

Comments closed

Creating a Time Series in R

Published 2023-12-28 by Kevin Feasel

Steven Sanderson says it’s time:

The ts() function in R is a fundamental tool for handling time series data. It takes four main arguments:

  1. data: A vector or matrix of time series values.
  2. start: The time of the first observation.
  3. end: The time of the last observation.
  4. frequency: The number of observations per unit of time.

Read on for an example of how this all works, as well as a function in the TidyDensity package to convert data into the R time series format.

Comments closed

Eager Aggregation in SQL Queries

Published 2023-12-28 by Kevin Feasel

Boris Novikov talks about an uncommon topic:

In this article we discuss one type of query transformation that most optimizers do not use. Because of this, it can be beneficial for you to rewrite a query to help the optimizer order operations in a way that can be beneficial.

An analytical query is supposed to produce some kind of summary generalizing properties of huge amounts of data but at the same time should be compact and easy for humans to understand. In terms of the SQL query language this means that any analytical query extracts and combines large number of rows and then uses aggregate functions with or even without GROUP BY clause. More specifically, we consider queries that contain many JOIN operations followed by aggregation. Usually, queries are written in this way and, surprisingly, the optimizers choose the best order of joins but leave the aggregation as the last step.

Read on for more information, including a minor lamentation that the various relational database optimizers tend not to perform this kind of operation. In SQL Server, I have an example of this pre-aggregation using the APPLY operator (with demo code here) and a simple but realistic example of how drastic the savings can be.

Comments closed

Distributed Data in Postgres

Published 2023-12-28 by Kevin Feasel

Umair Shahid explains how postgres_fdw works:

Bridging Data Silos and Accelerating Insights

In today’s data-driven world, organizations often grapple with data residing in multiple, disparate databases. This fragmentation can hinder seamless analysis and decision-making. However, PostgreSQL offers a powerful tool to address this challenge: postgres_fdw.

What is postgres_fdw?

postgres_fdw, short for PostgreSQL Foreign Data Wrapper, is a built-in extension that allows you to seamlessly access and query data stored in external PostgreSQL databases as if it were local to your current database. This means you can create views, join tables, and perform complex queries across multiple databases without the need for manual data integration or replication.

Read on for more information about the extension.

Comments closed

Options for Forcing Parallelism

Published 2023-12-28 by Kevin Feasel

Chad Callihan looks at a pair of options:

Just because something works doesn’t mean it’s the right thing to do. I had that type of challenge to my database morals recently when facing a query that refused to go parallel.

Read on to learn more. Note that neither of these relates to MAXDOP because that doesn’t determine whether a plan will go parallel (though you can use it to prevent a plan from going parallel).

Comments closed

Wrapper Stored Procedures

Published 2023-12-28 by Kevin Feasel

Erik Darling offers some advice:

Wrapper stored procedures are useful for things like:

  • Transforming declared local variables into parameters
  • Preventing code from compiling when it isn’t used
  • Generating different query plans to deal with parameter sniffing

The upside of using this over dynamic SQL is that you have a convenient object name attached to the code.

Read on for the downside to this, as well as a pair of videos on the topic.

Comments closed