Press "Enter" to skip to content

Month: December 2023

Set-Based vs Row-Based Code Considerations

Published 2023-12-04 by Kevin Feasel

Kevin Hill explains a concept:

In SQL Server, the terms “set-based” and “row-based” refer to different approaches or styles of writing SQL code to manipulate data. These styles have implications for performance, readability, and the way queries are processed. Let’s explore the differences between set-based and row-based code:

Click through for Kevin’s thoughts. One thing I’d re-emphasize (because Kevin did make this point), especially for people coming to SQL Server from Oracle, is that set-based operations are going to be more efficient about 95-99% of the time than their row-based equivalents. Oracle has a large number of optimizations to make cursor-style code efficient and T-SQL has very few of those, as set-based is the more natural expression of SQL.

One quick example of this is, prior to SQL Server 2012 and its extended support of window functions, the fastest officially supported way to calculate a running total was to build a cursor. The other alternatives, including self-joins, were much less efficient. There was an unsupported but much faster technique that relied on a peculiarity of how SQL Server sorts clustered indexes (the “quirky update” method), but because it relied on internals that could change with any patch, it was a risky maneuver.

Comments closed

Using Extended Events in Azure Data Studio

Published 2023-12-04 by Kevin Feasel

Josephine Bush tries it out:

I know I can use extended events (xevents) in Azure SQL DB when in SSMS, but I wanted to learn how to use them in Azure Data Studio (ADS).

Click through to see the normal workflow in SQL Server Management Studio, followed by the workflow in Azure Data Studio. I’d also recommend, at some point, finding good extended events sessions and saving the T-SQL to create them.

Comments closed

Generating Reports in Azure ML with Copilot

Published 2023-12-04 by Kevin Feasel

Soheil Bakhshi automates report creation:

In Nov 2023, Microsoft announced Microsoft Fabric’s general availability and Public Preview of Copilot in Microsoft Fabric. In a previous post, I explained what Copilot means to Power BI developers, which is valid for other Fabric developers such as data engineers and data scientists as Copilot for Fabric helps with those experiences as well. But the main focus of this blog post is to discuss the requirements, how to enable Copilot, and how to use it from a Power BI development point of view. So, this blog will not discuss other aspects of Copilot in Microsoft Fabric. With that, let’s begin.

I haven’t been particularly impressed with the reports it generates, but I suppose this is like the proverbial bear riding a unicycle: it’s not a question of how well it does the task that makes it interesting, but rather that it does it at all.

Comments closed

Grouping By Column Alias

Published 2023-12-04 by Kevin Feasel

Aaron Bertrand wants a feature:

GROUP BY queries can become overly convoluted if your grouping column is a complex expression. Because of the logical processing order of a query, you’re often forced to repeat such an expression since its alias can’t be used in the GROUP BY clause.

Oracle recently solved this in their 23c release by adding the ability to GROUP BY column_alias. This is such simple but powerful syntax, and I’m hoping we can get SQL Server to follow Oracle’s lead.

This would be a pretty nice feature. Admittedly, the workarounds aren’t that difficult, but this would be a nice quality of life update.

Comments closed

What Is Microsoft Fabric?

Published 2023-12-04 by Kevin Feasel

Tomaz Kastrun starts a new series:

Microsoft Fabric is a next-gen platform, that brings all-in-one data and analytics solution for end users, small, medium and large enterprises. Services offer the complete data cycle movement (data ingestion, data engineering, data integration, data storing with warehouse using one lake), delivering data insights and building predictive models.

Read on for the overview and stay tuned for plenty more where that came from.

Comments closed

Benchmarking Memory Usage in Shiny Apps

Published 2023-12-01 by Kevin Feasel

Ryszard Szymanski tracks memory utilization:

R/Shiny allows you to prototype a working web application quickly and easily. However, with increasing amounts of data, your app may become slow and, in extreme cases, crash due to insufficient memory.

When the worst-case scenario happens, we need to figure out a way to lower the memory usage of our app to avoid those crashes.

A crucial part of optimization efforts is benchmarking how much memory our app is consuming. This allows us to check if the changes we made to the app are indeed moving us in the right direction.

In this step-by-step guide, we will describe how to do that based on an example application.

Read on for the process and how you can diagnose memory problems. H/T R-Bloggers.

Comments closed

Quantile Regression in R

Published 2023-12-01 by Kevin Feasel

Steven Sanderson performs quantile regression:

Quantile regression is a robust statistical method that goes beyond traditional linear regression by allowing us to model the relationship between variables at different quantiles of the response distribution. In this blog post, we’ll explore how to perform quantile regression in R using the quantreg library.

If you need to hone up on your quantile regression knowledge, Wikipedia is usually good for statistics and here’s an academic paper from Roger Koenker and Kevin Hallock on the topic.

Comments closed

Replacing the Workspace Summary with Azure Workbooks

Published 2023-12-01 by Kevin Feasel

Josephine Bush works around a deprecation notice:

I’m saddened that the Workspace Summary is being deprecated in Log Analytics Workspace. I am trying to reproduce it in workbooks. While it isn’t an exact match, workbooks provide me with enough information to use and share with others.

Click through to see what you could get from the Workspace Summary and how to re-implement a fair amount of that in Azure Workbooks.

Comments closed

Knowledge Management via Azure OpenAI

Published 2023-12-01 by Kevin Feasel

Paul Hernandez builds a system:

In this post, I would like to show you how I implemented a simple use case to exemplify how you can query your data by implementing a chat application using Azure Open AI. Of course, we cannot only answer questions, LLMs are also capable of summarizing texts, or extracting entities. I decided to call it “Knowledge Management Assistant”, since I would like to use the application to assist me with some tedious tasks, which consumes some of my limited time.

Click through for the process. I would have recommended checking the box for vector search, though I imagine that would have blown past the limitations of the Basic tier of Azure AI Search (nee Azure Cognitive Search).

Comments closed