Nesting queries: best practices

Question

I'm in the process of trying to speed up my queries. A lot of them are quite complicated, and currently I mostly achieve this through creating multiple queries and linking them through joins.

Is this best practice, or should I be using SELECTs within SELECTs and trying to achieve the same thing within one query?

Will either option have an effect on speed, or is complicated just complicated?

EDIT: Example query below

SELECT HoldingCoNos.ID
FROM (
  SELECT [SearchByName - No Filter].ID
  FROM [SearchByName - No Filter]
  INNER JOIN [SearchByName - Level 1 Subsidiaries]
  ON [SearchByName - No Filter].ID = [SearchByName - Level 1 Subsidiaries].[Holding Company]
) AS HoldingCoNos
GROUP BY HoldingCoNos.ID;

This is with it all in one query - the other option is to create the contents of the FROM section of the query as two seperate queries, save them and use an inner join.

"Best practices" are a matter of opinion. While they often represent sage advice, I have found that on occasion, "worst practices" are more appropriate for the situation at hand. Regarding your question about which approach performs better, what happened when you tested each method? — Dan Bracuk, Sep 26 '13 at 12:20
Be aware that you're asking this question for a DB which provides no profiling or analyzing mechanisms, which means any answer is sort of like a shot in the dark. I work with MS Access a lot (although I do use SQL Server) and I basically never use linked query objects or subqueries. I suspect my applications have different, maybe simpler requirements that yours. — HK1, Sep 26 '13 at 12:51
"multiple queries and linking them through joins" vs "selects within selects", what is the difference? Usually, one only has a single query with a single `SELECT` statement within. — Will Palmer, Sep 26 '13 at 13:11
@DanBracuk It varies so much it's hard to tell, but that's a very senisble suggestion. — Sinister Beard, Sep 26 '13 at 13:22
@WillPalmer I mean the different between a query constucted of two previosuly constructed and saved queries via a join, or acheiving the same via a "virtual" query in SQL, which means fewer more complicated queries. — Sinister Beard, Sep 26 '13 at 13:25

score 3 · Accepted Answer · edited May 23 '17 at 12:28

Generally speaking: Sub queries get executed for each row in the parent query for example

Select * from employees where name IN (select Manager_name from Team_project where project_id=1)

It would execute select manager_name for every row in the employees table to compare the names granted the query is cached which would make it faster, but it is still more work.

However, it all depends have a look at this discussion for more detail: Subquery v/s inner join in sql server

score 2 · Answer 2 · answered Sep 27 '13 at 00:24

Looking at your example query, I can point out a couple of things:

You don't really need a subquery here. You're selecting a single field from a subquery and grouping by that field. Instead you can get rid of the outer query and just select distinct [SearchByName - No Filter].ID.
Perception is nine-tenths of reality. Your queries look complicated partly because you're using full table names everywhere instead of short table aliases. Notice the difference with aliases (and a little careful formatting). This will be magnified several times over when your queries get bigger:

SQL:

select distinct sbnnf.ID as HoldingCoID
from [SearchByName - No Filter] as sbnnf
inner join [SearchByName - Level 1 Subsidiaries] as sbnl1s
on sbnnf.ID = sbnl1s.[Holding Company];

Nesting queries: best practices

2 Answers2