36 Chambers – The Legendary Journeys: Execution to the max!

February 28, 2014

T-SQL Anti-Patterns: Query By Agonizing Query (QBAQ)

Filed under: Database Administration — Kevin Feasel @ 10:05 pm

Row By Agonizing Row (RBAR, pronounced “re-bar”) was our last anti-pattern.  Today, I’m going to extend it to a term I’m coining, Query By Agonizing Query (QBAQ, pronounced “cue-back”).  According to a quick Google search, nobody has used that term before, so that’s my claim to fame.

What I mean by QBAQ is the following anti-pattern:

UPDATE dbo.SomeTable
SET ColumnA = @Value
WHERE KeyColumn = @ID;

UPDATE dbo.SomeTable
SET ColumnB = @SomeOtherValue
WHERE KeyColumn = @ID;

UPDATE dbo.SomeTable
SET ColumnC = ColumnA + ColumnB
WHERE KeyColumn = @ID;

In this case, we see multiple queries where just one would do nicely:

UPDATE dbo.SomeTable
SET
	ColumnA = @Value,
	ColumnB = @SomeOtherValue,
	ColumnC = ColumnA + ColumnB
WHERE KeyColumn = @ID;

The top query hits SomeTable three times. Even though this is going to be three table seeks (because we’re joining on the primary key column), it’s also two more seeks than necessary. If you run this code on a regular basis, performance will be worse than it needs to be. Also, if these are running in a single transaction, you’re going to lock pages in SomeTable for a longer period of time than necessary.

Often, QBAQ gets combined with RBAR, so that the full query might be something like:

WHILE EXISTS(SELECT * FROM #RelevantValues)
BEGIN
	SELECT TOP 1 @ID = ID
	FROM #RelevantValues;

	UPDATE dbo.SomeTable
	SET ColumnA = @Value
	WHERE KeyColumn = @ID;

	UPDATE dbo.SomeTable
	SET ColumnB = @SomeOtherValue
	WHERE KeyColumn = @ID;

	UPDATE dbo.SomeTable
	SET ColumnC = ColumnA + ColumnB
	WHERE KeyColumn = @ID;

	DELETE FROM #RelevantValues
	WHERE ID = @ID;
END

Here is a piece of code which you could optimize quite nicely, removing all kinds of table hits:

UPDATE st
SET
	ColumnA = @Value,
	ColumnB = @SomeOtherValue,
	ColumnC = ColumnA + ColumnB
FROM dbo.SomeTable st
	INNER JOIN #RelevantValues rv ON st.KeyColumn = rv.ID;

This T-SQL-specific form of the UPDATE operator eliminates a loop, scanning (or seeking) #RelevantValues N times, deleting from #RelevantValues N times, and 2/3 of our table update hits. It’s also shorter, more concise, and easier to understand.

UPDATE statements aren’t the only time I see QBAQ, but they’re typically a good start. I also sometimes see it with UNION abuse:

SELECT A, B, C FROM dbo.SomeTable WHERE SomeValue = 1
UNION ALL
SELECT A, B, C FROM dbo.SomeTable WHERE SomeValue = 2
UNION ALL
SELECT A, B, C FROM dbo.SomeTable WHERE SomeValue = 3
UNION ALL
SELECT A, B, C FROM dbo.SomeTable WHERE SomeOtherValue = 'A'

In this case, we have four seeks or scans against SomeTable, and we can get that down to one:

SELECT 
	A, 
	B, 
	C 
FROM dbo.SomeTable 
WHERE
	SomeValue IN (1, 2, 3) 
	OR SomeOtherValue = 'A';

If you’re concerned about SARGability, you can also try that as two separate queries; in any event, we cut the number of table seeks/scans at least in half, and that adds up.

Most of the time, QBAQ comes from being stuck in a procedural mindset: “I need to perform operation X, then operation Y, then operation Z.” Once you think in those terms, you’re biasing your answers toward performing discrete operations, rather than seeing if there is a good way of combining X, Y, and Z together in one query. Being able to do this is a great indicator that you truly understand and can apply set-based concepts.

About these ads

1 Comment »

  1. […] last anti-pattern had us discussing unnecessary repetition of queries, but this time, I’m going to hit the opposite problem:  trying to merge two queries which […]

    Pingback by T-SQL Anti-Patterns: Non-SARGable ORs In Joins | 36 Chambers - The Legendary Journeys: Execution to the max! — March 1, 2014 @ 6:01 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 100 other followers

%d bloggers like this: