SQL Tricks #1

Hello all,

After my last few Transact-SQL training courses, one of my trainee had ask me on this scenario and I thinks it should be useful for most of people who are not concern much about SQL query performance which produce better processing time when compared to generic coding to query the result. You may not clear about what I’m writing now. Just see the following scenario.

Assume I have a table named [Book] which has the following structure.

- BookId as int – identity(1,1) – Primary key
- Title as varchar(100)
- Price as int

Then I assume that manager want a report just like this.

Title	Price	Price Range
Advanced SQL	25	Expensive
Intermediate C#	12	Cheap

You should see the column “Price Range” which do not reside in the table structure. Continue, I assume that if the price is expensive than 20 then I count as “Expensive” price range. If the price is less than 15 then I count as “Cheap”.

How should you do this report?

Most of my trainee do the following method.

1. Create an application then query for the columns [Title, Price].

2. They create a new column in report. In case of ASP.NET, they add a new template column into GridView.

3. In querying event for every record. (Such as Item data bound) They coding to check the condition whether the current record is expensive or cheap then output it.

Are there a better solution?

Certainly, If you focus on SQL query optimization. You should got a point to improve the performance of this process.

Let’s see the key.

Do you know about “CASE WHEN” statement in SQL?

Try to figure this query. Especially at the high-light.

SELECT Title, Price,
CASE
WHEN price > 20 THEN ‘Expensive’
WHEN price BETWEEN 15 AND 20 THEN ‘Medium’
ELSE ‘Cheap’
END
As ‘Price Range’
FROM [Book]

With this method, you never do some additional tasks like “adding template column” and “coding for price range”. You just binding this query to the GridView. I call this method “Column morphing” as with this method, you can create new column without structured on the table’s schema and can set the value in the column with any logical that SQL statement support.

So, does it better than the first method?

Definitely sure. Both on performance and less task effort. You can do more about “Column morphing” with the integration of sub-query. Just like the following case.

SELECT Title, Price,
CASE
WHEN Price > (SELECT AVG(Price) FROM [Book]) THEN ‘More than average’
WHEN Price = (SELECT AVG(price) FROM [Book]) THEN ‘Equal to average’
ELSE ‘Less then average’
END
As ‘Price Range’
FROM [Book]

Even more flexible when integrate with correlated sub-query. See the following more advanced case.

SELECT Title, Price,
CASE
WHEN Price > b.avgPrice THEN ‘More than average’
WHEN Price = b.avgPrice FROM [Book]) THEN ‘Equal to average’
ELSE ‘Less then average’
END
FROM [Book], (SELECT AVG(price) AS ‘avgPrice’ FROM [Book]) b

The last query will produce the same result as the previous one. (see if the current record is expensive than the average price or not) Except what each method actually process is very different.

In the 1st method, the average price calculation will be processed in every record. Think that If you have 100 records then it will calculate 100 times. Pain or not?

But the 2nd method will do the different. As you seen in the correlated sub-query. (which I’ve high-lighted as brown) You see that I put the average price calculation after FROM clause. This will force SQL parser to calculate the average price once and kept in alias table ‘b‘. This will generate much better in case of you’ve so much number of record to process.

Conclusion

It’s good to do what you can in SQL query as they’ve been optimized by database engine-self. If possible, provide as stored procedure is the best way to do as it will provide the fastest performance since the execution plan of query will be cached in memory. Got fast!