Press "Enter" to skip to content

Curated SQL Posts

Finding a Scalar Function Caller

Published 2023-01-23 by Kevin Feasel

Matthew McGiffen searches for the root of the problem:

In this post we look at a method using Extended Events (XE) to identify what parent objects are calling a given SQL function and how often.

The background is that I was working with a team where we identified that a certain scalar function was being executed billions of time a day and – although lightweight for a single execution – overall it was consuming significant CPU on the server. We discussed a way of improving things but it required changing the code that called it. The problem was that the function was used in about 700 different places across the database code – both in stored procedures and views – though the views themselves would then be referenced by other stored procedures. Rather than update all the code they’d like to target the objects first that execute the function the most times.

Read on to see how Matthew did it, as well as some caveats along the way.

Comments closed

Roles and Privileges in Postgres

Published 2023-01-23 by Kevin Feasel

Ryan Booz gives us an introduction to Postgres security:

Recall that in PostgreSQL both users and groups are technically roles. These are always created at the cluster level and granted privileges to databases and other objects therein. Depending on your database background it may surprise you that roles aren’t created as a principal inside of each database. For now, just remember that roles (users and groups) are created as a cluster principal that (may) own objects in a database, and owning an object provides additional privileges, something we’ll explore later in the article.

For the purposes of this article, all example user roles will be created with password authentication. Other authentication methods are available, including GSSPI, SSPI, Kerberos, Certificate, and others. However, setting up these alternative methods is beyond what we need to discuss object ownership and privileges.

Read the whole thing if you’re doing anything with Postgres.

Comments closed

Reading the Data Lake with the Serverless Pool via OPENROWSET

Published 2023-01-23 by Kevin Feasel

Ryan Adams begins a series on reading data from the data lake:

There are two ways to read data inside Data Lake using the Synapse Serverless engine.  In this article, we’ll look at the first method which uses OPENROWSET to query a path within the lake. 

Synapse is a collection of tools with four different analytical engines (Dedicated Pool, Spark Pool, Serverless Pool, Data Explorer Pool).  This gives you a lot of options for ingesting, transforming, storing, and querying your data.  The article will focus on how you can use the Synapse Serverless Pool to query the data in your ADLS account.   

Click through for a primer on the topic, as well as a demo video.

Comments closed

Restoring Large Power BI Premium Backups

Published 2023-01-23 by Kevin Feasel

Gilbert Quevauvilliers has a hefty backup:

When using Power BI Premium or Premium Per user you get the option to backup the database, there can be occasions when you try and restore the backup and it fails.

The reason that it could fail is because when a restore happens it can consume additional memory which would take you up and over the memory limit.

Below I will explain a new option which allow this to restore successfully!

Gilbert includes a copy of the error message and one new option in the post.

Comments closed

Designing Test Code for shinytest2

Published 2023-01-20 by Kevin Feasel

Russ Hyde wraps up a series on shinytest2:

UI-driven end-to-end tests require a bit more code than unit tests. For example, starting the app and navigating around to set up some initial state will require a few lines of code. But these are things you’ll likely need to do in several tests. As you add more and more test cases and these commonalities reveal themselves, it pays to extract out some helper functions and / or classes. By doing so, your tests will look simpler, the behaviour that you are testing will be more explicit, and you’ll have less code to maintain. We’ll show some software designs that may simplify your {shinytest2} code.

This post builds upon the previous posts in the series, but is quite a bit more technical than either of them. In addition to shiny development, you’ll need to know how to define functions in R and for the last section you’ll need to know about object-oriented programming in R (specifically using R6). The ideas in that section may be of interest even if you aren’t fluent with R6 classes yet.

Click through for the series finale.

Comments closed

Emulating Window Functions in MySQL 5.7

Published 2023-01-20 by Kevin Feasel

Lukas Eder says, we have window functions at home:

One of MySQL 8’s biggest improvements is the support of window functions. As I always said in conferences, there’s SQL before window functions and SQL after window functions. Once you start using them, you’ll use them everywhere.

Some of you poor souls are unfortunate enough to be stuck on MySQL 5.7, either of your own choosing, or because you’re using a clone / fork that is still 5.7 compatible. While for most people, this blog post is just for your amusement, or nostalgia, for some of you this post will be quite useful.

If you are in a windowless world, read on to see how you can make life a little more manageable.

Comments closed

Performance Comparing DISTINCT to GROUP BY

Published 2023-01-20 by Kevin Feasel

Reitse Eskens does a performance comparison:

A few days ago, I heard someone stating that Group By was much quicker than Distinct. Less disk impact, less memory etc.
So, I thought I’d find out if it’s true or not because I found it interesting. I always thought there was no difference. I tested a single small table and found no difference in speed, reads or execution plan. But that’s no real world example. Usually the tables contain a lot of data and are joined to other tables.

Click through for the results of Reitse’s analysis.

Comments closed

An Overview of the Power BI REST API

Published 2023-01-20 by Kevin Feasel

Reza Rad takes a look at Power BI’s API:

You can interact with the Power BI objects in the Power BI Service through a set of APIs called Power BI REST API. The Power BI REST API can help automate tasks, build tools that work with Power BI, the configuration of Power BI outside of the platform, and embed Power BI into a third-party application. In this article and video, I’ll explain REST API, why it is useful, and a few examples.

Click through for the video and explanation.

Comments closed

Good Practices for Powershell Development

Published 2023-01-20 by Kevin Feasel

Jeff Hicks shares some good practices with us:

Over the course of the last several weeks, I’ve been sharing and demonstrating techniques for writing effective PowerShell functions. I know I’ve mentioned a few recommended best practices. But since they are important, I want to review and re-emphasize them. These practices will make your code easier to write, easier to debug or troubleshoot, and more secure. I’d like to think some of them are simple, common sense, but sometimes we need someone to remind us. These items are not in any particular order.

There are some Powershell specifics here but most of the advice is language-agnostic.

Comments closed