Press "Enter" to skip to content

Category: Performance Tuning

Ordered Insert Optimization in OrioleDB

Alexander Korotkov deals with hot page issues:

When many sessions try to insert into the same B-tree leaf page, classic exclusive page locking serializes progress and wastes time on sleep/wake cycles. We’re introducing a batch page insertion path that lets the session holding the page lock insert for itself and its neighbors. The result: dramatically reduced lock waits, and big gains at high client counts (2X throughput boost starting from 64 clients in our benchmark).

Click through to see how it differs from normal PostgreSQL behavior on what is one of the more common performance problems on busy OLTP systems.

Leave a Comment

Troubleshooting Power BI Report Performance

Ben Richardson speeds up a report:

Slow Power BI reports waste time and frustrate users.

The slowdown often comes from hidden issues in the data model, DAX, visuals, or refresh settings.

This guide shows you how to find the cause, fix it, and keep reports running smoothly.

Read on to see some of the built-in tooling for performance optimization, as well as more information on these four common issues.

Leave a Comment

An Introduction to Query Folding in Power BI

Alex Powers takes us through one major performance optimization technique in Power BI:

One of the most powerful capabilities of Power Query and the M Language is Query Folding (also referred to as query delegation, and predicate push-down). Query Folding allows the Power Query Mashup Engine to push the transformations expressed in an M (mashup) query to the data source, in the data source’s query language, resulting in more efficient data processing.

For inexperienced database technology users this ability to leverage the graphical user interface of Power Query to dynamically generate a query written in the data source’s query language unlocks enormous opportunities to find insights with any data, at any scale.

Click through to see how you can know if query folding is enabled, as well as some hints around when and to what extent query folding will work.

Leave a Comment

Tips for Solving SSIS Package Bottlenecks

Andy Brownsword has some advice:

Last time out we started to look at optimising SSIS packages by showing how to identify bottlenecks with a handy script. This time we’re turning insights into action to solve those pain points.

The solutions are grouped into 3 areas: Data Flows, as they do a lot of heavy lifting; the Execute SQL task, which can also be used for transformation and calculations; and finally everything else (because the first two are usually the issue).

Andy has some good advice and plenty of links to prior content around optimizing SSIS performance. One small thing I’d add is architectural: think about whether you can solve the slow part inside SQL Server. If you’re grabbing a huge amount of data from a SQL Server instance and then narrowing it down with filters, it might be a lot faster to transform that into a SQL query with a stronger WHERE clause. But let’s say there’s some small file you’re using to filter, so you need to pull all of the data out of SQL Server to compare against the small file so that you know what you need. Instead of pulling all of the data out of SQL Server or setting up a Lookup component to hit the SQL Server instance for each row in the file, how about loading that file into SQL Server and then writing a query to do the work?

In short, the database engine is typically going to be a much better at performance than an integration layer would be.

Leave a Comment

Tips for Troubleshooting PostgreSQL Performance Slowdowns

Umair Shahid shares a few tips with us:

If you are a technology leader overseeing a team of developers who manage PostgreSQL as part of a broader application stack, or you are responsible for uptime and customer satisfaction at scale, knowing where to look first can make all the difference.

Let us walk through a focused checklist of patterns and places that commonly hold the key to unlocking better PostgreSQL performance.

This is a very high-level set of reminders regarding where you should look, rather than a detailed troubleshooting guide. But sometimes, it’s good to have that reminder.

Leave a Comment

Sundry Causes of Slow Disk Performance

Kevin Hill thinks about I/O:

“SQL Server is slow.”

We’ve all heard it. But that doesn’t always mean SQL Server is the problem. And “slow” means nothing without context and ability to verify.

More often than you’d think, poor performance is rooted in the one thing most sysadmins don’t touch until it’s on fire: the disk subsystem.

There are other potential causes as well, such as choosing the wrong RAID array format (like, say, RAID 6 for your extremely busy log files) and limited bandwidth to a SAN. Note that Kevin’s listings for what constitutes acceptable disk focuses primarily on on-premises solutions, maybe biased toward direct attached storage versus a SAN. For cloud databases, spikes of 30-60 seconds are perfectly fine, of course.

Leave a Comment

Making PostgreSQL Slower

Jacob Jackson takes on a unique challenge:

Everyone is always wondering how to make Postgres fastermore efficient, etc, but nobody ever thinks about how to make Postgres slower. Now, of course, most of those people are being paid to focus on speed, but I am not (although, if you wanted to change that, let me know). As I was writing a slightly more useful guide, I decided someone needed to try to create a Postgres configuration optimized to process queries as slowly as possible. Why? I am not sure, but this is what came of that thought.

I spent a few moments thinking about an equivalent sort of torture test on SQL Server, doing things like forcing CPU affinity through one core, monkeying with cost threshold for parallelism, and using trace flags to turn off different join optimizations (like, say, hash matches and merge joins, forcing everything to be nested loops). It’s a fun thought experiment.

Comments closed

SSIS Slowdowns in Paging to Disk

Andy Brownsword notes a major performance risk in Integration Services:

One particular performance issue with SSIS data flows can fly under the radar – spilling to disk. This isn’t clearly visible through regular debugging or execution so can go unnoticed. And it hurts.

Paging to disk is bad for performance. Disks are much slower to access than memory, so we want to keep our data away when possible.

Andy calls out two reasons why we might find spilling to disk, as well as how to track if this is happening.

Comments closed

Fast-Path Search in OrioleDB

Alexander Korotkov describes a new feature coming to OrioleDB:

When you optimize the CPU time of a transactional database management system, it comes down to one question: how fast can you read a page without breaking consistency? In this post, we explore how OrioleDB avoids locks, trims memory copies, and — starting with beta12 — even bypasses both copying and tuple deforming altogether for fixed-length types during intra-page search. This means that not only are memory copies skipped, but the overhead of reconstructing tuples is also eliminated. The result: an even faster read path, with no manual tuning required.

Read on to see what’s new and how it works.

Comments closed