While answering some questions in the forums, I notice that there are a lot questions that have queries that could be quickly modified to make them more efficient. Queries like:
SELECT (SELECT SUM(Column1) FROM MyTable WHERE Column2 = 'Some Value') as FieldNeeded1,
(SELECT SUM(Column1) FROM MyTable WHERE Column2 = 'Some Other Value') as FieldNeeded2,
(SELECT SUM(Column1) FROM MyTable WHERE Column2 = 'Some Other Value 3') as FieldNeeded3, ETC
Can be easily rewritten as :
SELECT
SUM(CASE WHEN Column2 = 'Some Value' THEN Column1 ELSE 0 END) as FieldNeeded1,
SUM(CASE WHEN Column2 = 'Some Other Value' THEN Column1 ELSE 0 END) as FieldNeeded2,
SUM(CASE WHEN Column2 = 'Some Other Value 3' THEN Column1 ELSE 0 END) as FieldNeeded3
FROM
MyTable
While it might not save much on a small table, think when the there are a million records. Running a select 3 times to pull the same fields would be very inefficient. However, this method is very efficient because the select only has to happen once, and the data is then just summarized as it is needed.
Anybody else have some very simple tips to increase peoples query performance?