For those who don’t follow me on twitter (and why not!) you might have missed me having a whinge about a SQL database I was looking at recently from a performance perspective.

Anyway, I found this little INSERT INTO statement in one of the extremelyy slow stored procedures they were having problems with (table names changed to protect the innocent)

INSERT INTO #TEMPTable
SELECT p.PID,EH.EID, SUM(EH.Amount)
FROM ps INNER JOIN
xx ON ps.xxID = xx.xxID INNER JOIN
p ON xx.PID = p.PID INNER JOIN
EH ON ps.psID = EH.psID
WHERE p.EID = @ParamID AND (p.Processedflag = 1)
GROUP BY EH.EID, p.PID

It executed in about 25 seconds. After a look at the indexing I decided to help SQL out a little by making the join to EH a little more selective, as follows:

INSERT INTO #TEMPTable
SELECT p.PID,EH.EID, SUM(EH.Amount)
FROM ps INNER JOIN
xx ON ps.xxID = xx.xxID INNER JOIN
p ON xx.PID = p.PID INNER JOIN
E1 on E1.EID = p.EID INNER JOIN
E2 on E2.EID = E1.EID INNER JOIN
EH ON ps.psID = EH.psID
 and EH.EID = E2.EID
WHERE p.EID = @ParamID AND (p.Processedflag = 1)
GROUP BY EH.EID, p.PID

That little change reduced the query to under a second, even though I’m now joining to two extra tables.

If only all performance improvements were as simple. And if looking like a hero was always so easy! :-) And the lesson for you? Remember to keep your joins as selective as possible, even if it means taking the long way round on joins at times.