I am always amazed at how SQL server provides a lot of calculations and logic built right into a select statement. Most of the time (if you are like me) you spend 85% of your time writing basic Select statements with a variety of where clauses. However last week I came across a need to get the percent of total for 1000’s of records. In other words assume you have this data.

Using this data I generated a sample table with data to perform an optimization exercise. Here are the details.

[code language=”sql”]
CREATE TABLE TempTable([Name] varchar(20),Age Int);

INSERT INTO TempTable ([Name], Age) VALUES (‘David’,34)
INSERT INTO TempTable ([Name], Age) VALUES (‘Jeff’,19)
INSERT INTO TempTable ([Name], Age) VALUES (‘Steve’,18)
INSERT INTO TempTable ([Name], Age) VALUES (‘Bill’,19)
INSERT INTO TempTable ([Name], Age) VALUES (‘Lin’,40)
INSERT INTO TempTable ([Name], Age) VALUES (‘Heather’,41)
INSERT INTO TempTable ([Name], Age) VALUES (‘Sarah’,37)
INSERT INTO TempTable ([Name], Age) VALUES (‘Tom’,37)
INSERT INTO TempTable ([Name], Age) VALUES (‘Linda’,37)
INSERT INTO TempTable ([Name], Age) VALUES (‘Jennifer’,35)
INSERT INTO TempTable ([Name], Age) VALUES (‘Susan’,20)
INSERT INTO TempTable ([Name], Age) VALUES (‘Laura’,19)
INSERT INTO TempTable ([Name], Age) VALUES (‘Jake’,18)

–Method #1
Declare @totalcount integer
Set @totalcount = (select count(*) as totalrec from TempTable)

Select Age, num, Total, (cast(num as float)/Total)*100 as PctTotal from (
Select Age,count(*) as num, @totalcount as total from TempTable tb1 Group by Age) ee
————————————————

Drop Table TempTable
[/code]

This will give you the right answer and is a pretty common way I have seen a select statement like this written. But using Over(Partition By …) you can achieve some pretty amazing performance gains. By changing the Select to this instead….

[code language=”sql”]
–Method #2
select distinct
Age,
count(Age) over() AS TOTALValue,
count(Age) over(partition by age),
–Calculate Percentage
cast((count(Age) over(partition by age)/(count(age) over()*1.000)) as float)*100 as Percentage
from TempTable
————————————————-
[/code]

There is a major improvement in performance as well as it is easier to understand.

I used about 1,500 records and with the help of Toad for SQL here are the results.

Scenario #1 : 2.662 seconds to run
Scenario #2 : 0.017 seconds to run

You can see by the results that taking time to better understand your options and altering your select statement you can not only get percent of total using Selects but do it in a very concise and optimized way.

Looking for quality web hosting? Look no further than Arvixe Web Hosting!