Get SQL speed 1000 times faster by simple change in SELECT statement

Posted on Posted in Technology

Well don’t go with numbers but in SQL, using small SELECT statements and then clubbing them into one is much faster than using nested SELECT statements or even simply using JOIN. Below is an example for your understanding (Syntax for MS SQL Server):
Nested SELECT statement:
SELECT columnA, columnB, columnC
FROM TABLE1
WHERE columnA in (
SELECT columnA
FROM TABLE2
WHERE columnA = “Alfa”
)
Using TEMP table in SQL:
WITH TEMP1 AS
{
columnA, columnB, columnC
FROM TABLE1
} ,
TEMP2 AS
{
SELECT columnA
FROM TABLE2
WHERE columnA = “Alfa”
}
SELECT TEMP1.*
FROM TEMP1
JOIN TEMP2
ON (TEMP1.columnA = TEMP2.columnA);

Do not confuse, TEMP with temporary tables. Temporary tables start with @ or #.

TEMP is just a name given to a SELECT query.
Well if you know enough SQL, you may say why don’t we just use JOIN instead of nested SELECT statement? Why TEMP on top of JOIN?
You’ll realize the pain of not using TEMP if your relevant tables have a million record with over hundred columns and multiple conditions for getting final output. In Banking sector where I work, this scenario is common.
Few days back I used just JOIN for a query and it took 23 minutes to execute, when I broke SELECT into many and clubbed them back, the same logic executed in 15 seconds! If I would have used nested SELECT, execution would have taken many hours!
So, I thought to share this experience with my blog readers.
Thank you for reading this blog. In my next blog, I’ll walk you through NOLOCK and UNION concepts.
If you like this blog please hit the LIKE button and feel free to share with anyone. I would love to see your comments, I read all comments religiously.
Let me know what do you want to know more about and I’ll see how may I help you! Thanks again and have a successful day!

Leave a Reply

Your email address will not be published. Required fields are marked *