Performance Tuning - will I loss the indexing when using derived table?

This was the question asked by a Developer when we are investigating a performance loss issues on a database. To the point a derived table means a virtual table that's calculated on the fly from a select statement. By default using derived tables can be tremendously useful in certain situations.

In any case if you have 2 queries in hand say one with [select C.address, C.area, B.productname, B.category, A.qty, A.price  from order A join product B   on A.productID = B.productID.....] and another with [select C.address, C.area, B.productname, B.category, A.qty, A.price from (select qty, price, productID, addressID from order) A join.....], it is better to check execution plan for both the queries. Theoritically both should get optimum results, but in the real time I have seen the second query will get faster results by pulling data from cache because of derived table usage. For the discussion sake let us run it through afresh by executing DBCC FREEPROCCACHE to clear the plan buffer, but make sure to perform this in development box  but not on live SQL instance as it will lead to another performance issue in re-generating all the plans again.

As referred the Estimated Execution Plan should get you what it looks like for performance, overall it doesn't matter what way you want to write the query. By default the SQL optimizer  query normalization process reduces different syntaxes to same format internally and that is how you end up with the same execution plan. I have seen few cases where it returns different execution plan when it goes deep down to complex usage of query conditions.

As usual for better understanding to resolve the issue it is suggested to write the simple syntax to get expressed rsults, when you think about Performance loss it is nothing but  problems (complexity) in queries leads to result of bad logical schemas. So for the newbie knowledge sake I would like to list few things that would help you in case you are performing similar performance analysis:

• If the query fashion is persisting then better to use it as a Stored Procedure and compile it for a better cache returns.
• Unless required do not use IN or NOT IN conditions, also carefully consider usage of JOINs with NULL checks.SELECT needed columns only, do not use SELECT *. Using relevant columns will keep better index choices and also reduces the network contention too, if that query is accessed heavily.
• For multiple table joins better to declare constraints with appropriate Foreign Key references.
• For conditional based queries better to go with CASE expressions first than mixing with JOINS usage.
• Avoid COMPOSITE indexes whereever possible, as this leads to duplicate indexes and better to maintain the primary key column of the index.
• Avoid duplication of data by using unique constraints, to generate optimum plans for performance.
• Have CLUSTERED indexes with less columns (avoid COMPOSITE) when you have more NON-CLUSTERED indexes.
• If using INDEXED views then better to drop and re-create it specifying WITH SCHEMABINDING.
• Consider usage of TABLE variables within the complexed queries, better to use TEMP tables.
• Again (if possible) divide the complex queries by using TEMP tables for better optimization.