Press "Enter" to skip to content

Month: August 2024

The Performance Impact of Local Variables

Published 2024-08-01 by Kevin Feasel

Jared Westover talks performance:

Often, developers use local variables when writing ad hoc queries or stored procedures for many reasons. You might hear “never repeat code” or “avoid using magic numbers.” While writing a lengthy stored procedure, I might include a few. However, did you know that local variables can hurt the performance of your queries? How can you keep local variables from negatively affecting performance? Keep reading to find those answers and more.

This is the kind of performance issue that you can easily forget about. Jared includes two methods for resolving the issue if you run into performance problems on a specific query or stored procedure.

Comments closed

Tracking Task History in Snowflake

Published 2024-08-01 by Kevin Feasel

Kevin Wilkie is interested in just one thing:

By the table function name, you’re probably wondering “Sherpa, why on Earth do I really need to look at query history based on the session? I have Query_History_By_User which gives me a broader look at my data. Who really cares?”

Great question, my friend! You’re right. This new Query_History_By_Session table function isn’t going to give you a lot of data that you can’t get easier – and more helpfully – with Query_History_By_User. Why did Snowflake provide me this “useless” function?

Read on for the value of this function and an example of querying task history.

Comments closed

A Review of Useful pg_stat_statements

Published 2024-08-01 by Kevin Feasel

Umair Shahid tracks some statements:

pg_stat_statements is an extension for PostgreSQL that tracks execution statistics of SQL statements. It is designed to provide insight into the performance characteristics of database queries by collecting data on various metrics such as execution time, number of calls, and I/O operations. This extension is immensely useful for database administrators and developers looking to optimize their SQL queries and improve overall database performance.

Click through to learn more about pg_stat_statements, including how to install and configure it, as well as some of the things you can do with it.

Comments closed

Adding tSQLt to a Database Project

Published 2024-08-01 by Kevin Feasel

Olivier Van Steenlandt provides an overview of adding tSQLt to a Visual Studio database project:

As a first step in the process, we’re going to create a new Database Project, in my case, I will be calling my Database Project AdventureWorksDW_UnitTesting and my solution AdventureWorks.

If you are not sure how to set up a Database Project in Visual Studio from scratch, don’t worry, you can follow the step-by-step data recipe I released a while ago, Getting Started with Database Projects and Version Control

Read on to learn more about how to add the tSQLt objects and eliminate cross-database reference issues.

Comments closed