More tips for SQL efficiency: boolean values in SQL
I'm always looking for ways to speed up my SQL Server queries. Here's a tip that I used just today to speed up a search query, and I thought maybe it would be useful for all of you.
First up, make sure to include an execution plan by right-clicking on your query window and selecting "Include Actual Execution Plan"
Let's take a look at the following query:
FROM authors a
INNER JOIN books b ON a.author_id = b.author_id
WHERE a.author_id = 27
AND b.book_published = 1
AND b.best_seller = 1
As you can see, we have two tables, books and authors. Each of these tables are joined to show the author names for each book, and we filter by books that are published and are best sellers. This is where we'll be concentrating.
This query is a common example of a mistake that can slow down large datasets. Though you can't see it in the query itself, let's assume the "book_published" and "best_seller" fields are the boolean field bit. These fields are implicitly convert to bit values when you execute the query. This conversion can take time, and these conversions can be seen in the query execution plan.
We can remove do the conversion manually and take the load off the server very easily as you can see by a simple update below:
FROM authors a
INNER JOIN books b ON a.author_id = b.author_id
WHERE a.author_id = 27
AND b.book_published = '1'
AND b.best_seller = '1'
That's it! Adding apostrophes tells the query tool allows the fields to query without needing to convert the data at all!
There are no comments for this entry.
[Add Comment]