I have some questions on derived table. Below is my situation.

Table order, there are 40 columns and it has indexes for productID and addressID
Table product, there are 15 columns and it has index for productID
Table address, there are 20 columns and it has index for addressID

Query A
select C.address, C.area, B.productname, B.category, A.qty, A.price
from order A
join product B
on A.productID = B.productID
join address C
on A.addressID = C.addressID

Query B
select C.address, C.area, B.productname, B.category, A.qty, A.price
from (select qty, price, productID, addressID from order) A
join (select productID, productname, category from product) B
on A.productID = B.productID
join (select addressID, address, area from address) C
on A.addressID = C.addressID

Will I loss the indexing when using derived table
Why query B performance is much better than query A


Re: Questions on derived table


Try first comparing the execution plans.


Re: Questions on derived table

Arnie Rowland

Check the execution plans.

One of the reasons that Query B may be faster than Query A is that Query A pulled the data into cache where it was available to Query B.

To test the speed differences, run this command between the two queries:

DBCC DropCleanBuffers

Re: Questions on derived table

Lee Everest

You might also use DBCC FREEPROCCACHE (Transact-SQL) to make sure that any ad-hoc plans that might have been generated are cleared. Always use this and DROPCLEANBUFFERS on a test box



Re: Questions on derived table

Umachandar Jayachandran - MS

It doesn't matter which way you write the query. Both queries will and should have identical execution plans. Ideally logically equivalent forms of a query will have identical plans. The query normalization process reduces different syntaxes to same format internally and that is how you end up with the same execution plan. You can observe this from the execution plan output. It is however possible that in some cases (due to complexity of the query) that optimizer might choose different strategies or abort optimization at different phases. Under such circumstances you will end up with different execution plans.
So when you write queries, don't worry about these kind of syntax details. It is irrelevant. Pick the simple syntax that expresses the solution to your problem. The best performing queries are often the simple ones in terms of syntax. Performance problems in queries are usually the result of bad logical schemas. Here are few points to remember (logical and physical design):
1. Use only the columns that you absolutely need in the SELECT list. This can affect performance due to index choices and also send more data to client than required
2. Use more straightforward syntaxes like EXISTS/NOT EXISTS as opposed to IN/NOT IN/LEFT JOIN with NULL check
3. Declare constraints on your tables as appropriate (FK references or uniqueness of a constraint are properties that the optimizer can exploit and use to generate better plans)
4. Avoid unnecessary joins. For example, you could compute multiple aggregates with CASE expressions as opposed to writing several different queries
5. Avoid duplicate indexes (remember that statistics are maintained only for the primary key column of the index)
6. Clustering on smaller keys helps especially if you have multiple non-clustered indexes on a table
7. Don't use table variables in complex queries or with data that is more than say 100 rows. These can really affect the execution plan and you will end up with poorly performing queries
8. For complex queries, it might be worthwhile to break them into multiple ones using temporary tables. Again this an optimization technique you should resort to after you have written the single query that produces the correct results.