Chances are, if you’ve ever been to a college football game, you’ve heard the drum-line. The loud crashes of cymbals, tight tap of the snares, deep tones of the bass drums, you get the idea. In order to get these specific sounds, different tools are used. Quads use special mallets, as does the pit according to whatever instrument is being played. You wouldn’t use brushes on a snare drum just as you wouldn’t use bass beaters on a xylophone.

Writing Microsoft SQL queries is the same way. Just as specified mallets and sticks are used in drum-line, different SQL techniques are used depending on the situation.

---

select refname, lskinid, round(sum(leminutes), 2) as 'Minutes' from lskin
	join hproduct_lskin on frn_lskinid = lskinid
	join dnis on add_lskinid = frn_lskinid
	join archive.dbo.xcall_2014 on cf_frn_dnisid = dnisid
		and tz_date between '2014-01-01' and '2014-03-01'
where frn_hproductid = 1
group by refname, lskinid

Above is just a basic query; nothing special, just some joins. As you can see, it took 1 minute and 58 seconds to complete.

with CTE (refname, lskinid, dnisid)
as (
select refname, lskinid, dnisid from lskin
	join hproduct_lskin on frn_lskinid = lskinid
	join dnis on add_lskinid = frn_lskinid
where frn_hproductid = 1
)
select refname, lskinid, round(sum(leminutes), 2) as 'Minutes' 
from CTE c
	join archive.dbo.xcall_2014 on cf_frn_dnisid = c.dnisid
		and tz_date between '2014-01-01' and '2014-03-01'
group by refname, lskinid

This takes the same query and separates it into 2 parts using a CTE. Changing the technique cut the time down to only 8 seconds.

declare @TempTable table (refname varchar(155), lskinid int, dnisid int)

insert into @TempTable (refname,lskinid, dnisid)
(
select refname, lskinid, dnisid from lskin
	join hproduct_lskin on frn_lskinid = lskinid
	join dnis on add_lskinid = frn_lskinid
where frn_hproductid = 1
)
select refname, lskinid, round(sum(leminutes), 2) as 'Minutes' 
from @TempTable t
	join archive.dbo.xcall_2014 on cf_frn_dnisid = t.dnisid
		and tz_date between '2014-01-01' and '2014-03-01'
group by refname, lskinid

Again, just a different technique (using a Temp Table) cut the query down to 8 seconds.

---

Those are just a few examples of query techniques in SQL. So next time you have to write a query, make sure you’re using the right ‘instrument’ for the occasion.