Hi Everyone,
Good Day!!!
Sometimes, we cannot know the importance of some simple things until we experience them. Whether it may be in life or may it be in SQL Server. In SQL Server, I realized this when I faced a severe performance problem in one of the functions I was given to enhance. I was new to the database.
I have a table valued function with the underlying query as:
SELECT T1.Col1 BaseCol
, SUM(T1.Amount1 + T3.Amount3) A1
, SUM(T2.Amount2) A2
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Col1 = T2.Col2
LEFT JOIN
(
SELECT Col3, SUM(Amount3) Amount3
FROM Table3
GROUP BY Col3
) T3 ON T2.Col2 = T3.Col3
GROUP BY T1.Col1
This simple query is giving wrong results and taking minutes of time to get executed for less than a million records.
I have tried it in several other ways but the result was same -- wrong one. Finally, I noticed that some of the Amount columns in the SELECT list were null and used ISNULL() function.
Thank God. Now I got correct results. It means when either T1.Amount or T3.Amount was NULL, the entire value has become NULL and never contributed during the summation. When ISNULLL() function is used, the NULL is converted into 0 and as a result atleast the other amount is contributed to the summation and resulted in correct amounts finally.
Now, I was getting the results correct but what about the performance.
I had to complete that quick and the timeline was small. I was annoyed and I hastily checked the execution plan. I found nested loop joins but noted some look-ups and created indexes on that particular tables. But there was no improvement in performance.
I have checked the execution plan and found that the newly created index was being used but there was no change in the performance. Having learnt a lesson from the above ISNULL() scenario. I have executed the following query
SELECT Col3, SUM(Amount3) Amount3 FROM Table3 GROUP BY Col3
To my astonishment, I have found many Amount values were NULL.
So, I included a WHERE clause with "IS NOT NULL".
It worked, it means we are bringing unnecessary data from Table3 and since its volume is huge, it created an issue which is filtered off using "IS NOT NULL".
So, my final query looked like:
SELECT T1.Col1 BaseCol
, SUM(ISNULL(T1.Amount1,0) + ISNULL(T3.Amount3,0)) A1
, SUM(T2.Amount2) A2
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Col1 = T2.Col2
LEFT JOIN
(
SELECT Col3, SUM(Amount3) Amount3
FROM Table3
WHERE Amount3 IS NOT NULL
GROUP BY Col3
) T3 ON T2.Col2 = T3.Col3
GROUP BY T1.Col1
Finally, I got the desired output in desired time.
Cool.....
Thanks,
Sunil.
