Press "Enter" to skip to content

Day: March 13, 2024

Pulling Samples in R with sample()

Steven Sanderson takes a sample:

The sample() function in R is a powerful tool that allows you to generate random samples from a given dataset or vector. It’s an essential function for tasks such as data analysis, Monte Carlo simulations, and randomized experiments. In this blog post, we’ll explore the sample() function in detail and provide examples to help you understand how to use it effectively.

Read on to see what options are available with sample() and the different ways in which you can use the function.

Comments closed

Issues and Projects in GitHub

I have a new video:

In this video, we take a look at what GitHub has for project management, reviewing GitHub Projects and Issues.

The upshot is that GitHub has a fair amount of capability for project management. Its notion of Issues definitely feels fairly well fleshed out, which makes sense considering GitHub’s original purpose as a storehouse for open-source code repositories. By contrast, Projects are a relatively new feature and there’s still some room to grow there, especially if you’re used to project management tools like Jira or Trello.

Comments closed

Hash Match and Stream Aggregate Operators in SQL Server

Andy Brownsword rounds up the usual suspects:

In the last post we looked at how TOP and MAX operators compared. We saw the execution plan for a MAX function used a Stream Aggregate operator which is one of two which we can use for aggregation

I wanted to look at the two operators and how they perform the same tasks in different ways. The way they function is key to understanding why the engine may choose to use one over the other and the impact this can have on the performance of a query.

The two operators in question: the Hash Match (Aggregate) and Stream Aggregate

Read on for a discussion of how each operator works and when each makes sense for the optimizer to use.

Comments closed

Tips on Using Subqueries in the SELECT Clause

Erik Darling covers a “sometimes” topic:

I think subqueries in select lists are very neat things. You can use them to skip a lot of additional join logic, which can have all sorts of strange repercussions on query optimization, particularly if you use have to use left joins to avoid eliminating results.

Subqueries do have their limitations:

  • They can only return one row
  • They can only return one column

But used in the right way, they can be an excellent method to retrieve results without worrying about what kind of join you’re doing, and how the optimizer might try to rearrange it into the mix.

Read on for a dive into this topic and a scenario in which subqueries in the SELECT clause can be faster than alternatives. My personal preference is, unless there’s a major performance difference, I’d rather have the SELECT clause be as simple as possible. But sometimes, the difference is stark enough to matter.

Comments closed

Setting up Replication with dbatools

Jess Pomfret continues a series on replication in dbatools:

This post is focusing on how to setup replication with dbatools. We support all three flavours – snapshottransactional and even merge replication!

In this article I’ll be creating a transactional publication, but the steps for setup are very similar no matter which flavour you’re implementing.

I’ll walk through and demonstrate all the steps to setup replication in this article as dbatools allows us to complete them all. However, I won’t go into a lot of details on why or how replication works, or provide guidance on best practices. If you need more information on replication as a technology then I recommend visiting the Microsoft Docs.

Read on for a demonstration of how the cmdlets work for adding a publication, articles, subscriptions, and more.

Comments closed

Migrating Always Encrypted to a Secure Enclave

Pieter Vanhove has an enclave, which is sort of like a Bat-cave:

Always Encrypted is a feature of Azure SQL and SQL Server that allows you to encrypt sensitive data in your database. The data is never exposed in plaintext to the database engine, or anyone who has access to it.

However, Always Encrypted has some limitations. For example, you cannot perform any computations or operations on the encrypted data, such as sorting, filtering, or indexing. Secondly, an initial encryption must be done on the application side which can be time consuming on a large set of data.

That’s where Always Encrypted with secure enclaves comes in.

Read on to see what secure enclaves give you, as well as how you can enable it and what changes your application might require.

Comments closed

Enforcing Join Order in Postgres

Hans-Juergen Schoenig demands order:

What is the important observation here? Let us take a look at planning time. PostgreSQL needs 0.297 milliseconds to find the best execution plan (= execution strategy) to run the query. The question arising is: Where does the planner need the time to plan the query? The thing is: Even when using explicit joins as shown above PostgreSQL will join those tables implicitly and decide on the best join order. What does that mean in real life?

Read on to see what this means in practice and how you can control join order in Postgres. With SQL Server, there are various join hints that will force a specific join order. As for the why, there are specific circumstances in which you might have more information than the optimizer and can come up with a superior way of joining tables together, especially as queries get more complicated. One of my favorite query tuning books is Dan Tow’s SQL Tuning, which is 20 years old at this point but still lays out a great way of thinking about how to attack the process of running a query. In that book, Dan uses several criteria to determine the table from which you want to drive a particular query, using factors like filters, the existence of foreign key constraints, etc. From there, you have a somewhat-deterministic way of defining the most efficient path for connecting the rest of the tables together. For most queries, especially in OLTP systems, this doesn’t matter very much in practice. But for warehouses, it can make a world of difference.

Comments closed