Press "Enter" to skip to content

Curated SQL Posts

Stored Procedure IF Branching and Performance

Published 2019-02-04 by Kevin Feasel

Erik Darling explains that the IF block in a stored procedure won’t help you with performance:

Making plan choices with IF branches like this plain doesn’t work.
The optimizer compiles a plan for both branches based on the initial compile value.
What you end up with is a stored proc that doesn’t do what it’s supposed to do, and parameter sniffing times two.

Read on to see an example of this. If you really, really want to use an IF block, you could separate the components out into individual stored procedures and call those stored procedures independently.

Comments closed

Power BI Palette In Charticulator

Published 2019-02-04 by Kevin Feasel

David Eldersveld has submitted a pull request for Charticulator:

This morning, I submitted a pull request on GitHub to bring the default Power BI color palette to Charticulator. While Charticulator can export a Power BI custom visual .pbiviz file, there are no color-related Format options in Power BI. Any color choices must occur in Charticulator before exporting (you currently cannot change them later in Power BI). As a result, having the Power BI default palette easily accessible in Charticulator could help bring more consistency between the two tools.

I think this is a good idea. Consistency between products allows people to combine them more effectively.

Comments closed

Using AWS Lambda To Get Into Nice Restaurants

Published 2019-02-01 by Kevin Feasel

Stephane Maarek gives us the best use of AWS Lambda I’ve seen yet:

One attentive eye would have noticed that the booking platform is not hosted on the restaurant website at http://www.septime-charonne.fr/en/ but instead on https://module.lafourchette.com.

Upon using the Chrome Web Developer Tools to analyze the network calls being made between my browser and the booking service, I stumbled upon an easy to use and completely unprotected REST API:

I love the bonus hack at the end.

Comments closed

Enabling Cloudera Manager Debug Mode

Published 2019-02-01 by Kevin Feasel

Guy Shilo has a quick tip around debugging in Cloudera Manager:

This is a short post but it can save you some wandering and searching.

Sometimes when you try to find and fix issues with Cloudera Manager you will want to increase the log level to debug so you can see what’s wrong.

The procedure cannot be found in the documentation (or at least cannot be found easily), so here how it’s done:

As you’d expect, going into debug mode generates a lot of data on a real cluster, so use sparingly.

Comments closed

Things To Know About Databricks UAP

Published 2019-02-01 by Kevin Feasel

Kara Annanie has five things you should know about the Databricks Unified Analytics Platform:

4.     A Spark Dataframe is not the same as a Pandas/R Dataframe
Spark Dataframes are specifically designed to use distributed memory to perform operations across a cluster whereas Pandas/R Dataframes can only run on one computer. This means that you need to use a Spark Dataframe to realize the benefits of the cluster when coding in Python or R within Databricks.

This is a nice set of tips for people getting started with Spark and Databricks.

Comments closed

When A Procedure Has Multiple Plan Cache Entries

Published 2019-02-01 by Kevin Feasel

Arthur Daniels shows that multi-statement stored procedures can have multiple entries in the plan cache:

So we have two entries for this stored procedure. I included the statement sql handle to show that each statement handle has its own text. Let’s parse that text to see each statement. I copied the parsing SQL from this Plan Cache article.

This is a good thing to keep in mind if you’re trying to figure out how often a procedure gets called: SUM on the execution counts grouped only by text might not give you the results you expect.

Comments closed

Azure Data Studio Code Snippets

Published 2019-02-01 by Kevin Feasel

Rich Brenner shows us how we can create code snippets in Azure Data Studio:

A great thing about these snippets is that you can add your own and they can be exactly how you want them.

To get started with this open the Command Pallet with Ctrl+Shift+P and type in ‘snippets’.

Scroll down and find the SQL option. Open it and it will bring you to the SQL.json file in which we’ll be storing our SQL Snippets.

I had to migrate a bunch of SSMS snippets to Azure Data Studio and was not that happy with the experience, especially for some of the more complicated snippets.

Comments closed

Digging Into DBCC CHECKIDENT

Published 2019-02-01 by Kevin Feasel

Solomon Rutzky covers the four primary scenarios when running DBCC CHECKIDENT and specifying a new reseed value:

So, when specifying a “new_reseed_value“, the possible scenarios covered are:
1. Rows exist
2. No rows due to none inserted since the table was created
3. No rows due to TRUNCATE TABLE operation


What’s missing? The following scenario:
No rows due to DELETE operation!!

Click through to see how DBCC CHECKIDENT behaves differently depending upon the scenario.

Comments closed

Database Integration Testing With Pester

Published 2019-02-01 by Kevin Feasel

Ust Oldfield shows us an example of how we can perform database integration testing using Pester:

In a previous post, I gave an overview to integration tests and documenting integration points. In this post, I will give a practical example of developing and performing integration tests with the Pester framework for PowerShell. With a data platform, especially one hosted in Azure, it’s important to test that the Azure resources in your environment have been deployed and configured correctly. After we’ve done this, we can test the integration points on the platform, confident that all the components have been deployed.

The code for performing integration tests is written in PowerShell using the Pester Framework. The tests are run through Azure DevOps pipelines and are designed to test documented integration points. The PowerShell scripts, which contain the mechanism for executing tests, rely upon receiving the actual test definitions from a metadata database.

Click through for the script.

Comments closed