Solved

Machanics of derived table

Posted on 2011-02-22
12
794 Views
Last Modified: 2012-05-11
I would like to understand the machanics of the sql derived table.

Here is an example:


Select * from Products P
(select ID, Name where country = 'UK' From shops) S
ON P.ID = S.ID

Does the derived table return every record from the Shops table and then match it to products?

OR

Does the dervie table corrolate with products using the ID column?
0
Comment
Question by:Mr_Shaw
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 20 total points
ID: 34951411
It scan full table and after fetch matching rows.
0
 
LVL 10

Assisted Solution

by:John Claes
John Claes earned 240 total points
ID: 34951577
Mr Shawn

In very basic terms, a derived table is a virtual table that’s calculated on the fly from a select statement.

I can give you some good references where you can find all you need ;-)

When to use Virtual tables and when derived
http://techahead.wordpress.com/2007/10/01/sql-derived-tables/

A big difference is :
A SQL derived table differs from a temporary table in that a SQL derived table exists only for the duration of the query, while a temporary table exists until the server is brought down.

Some extra useages ;-)
http://www.sqlteam.com/article/using-derived-tables-to-calculate-aggregate-values


regards

poor beggar
0
 

Author Comment

by:Mr_Shaw
ID: 34952663
Hi PatelAlpesh,

I tested the following:

Select * from Products P
(select ID, Name where country = 'UK' From shops) S
ON P.ID = S.ID
Where P.ID IN (1,2,4,55,66)

What happens is for each ID in the where clause the derived table is executed. Therefore in this example it is run 5 times.

I was thinking that the derived table is run once and then the required rows pulled from the results.



0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 240 total points
ID: 34952999
hi

while using a derived table the folowing happens.

1, the derived table is generated
2. the join process kicks in and it can use three diferrent option.
   a. nested loop : for each row in table A is scans all rows in table B for a match
       Table A : driving Table , Table B driven Table (the optimizer will choose the smaller table as the   driving so the execution will result with less loops. )

   b. Hash join : The hash join first scans or computes the entire build input and then builds a hash table in memory. Each row is inserted into a hash bucket depending on the hash value computed for the hash key. This build phase is followed by the probe phase. The entire probe input is scanned or computed one row at a time, and for each probe row, the hash key's value is computed, the corresponding hash bucket is scanned, and the matches are produced.

c. The merge join requires that both inputs be sorted on the merge columns, which are defined by the equality (WHERE) clauses of the join predicate

BTW : a goos filter in the query will usually generate a nested loop.
0
 

Author Comment

by:Mr_Shaw
ID: 34953594
>>a good filter in the query will usually generate a nested loop.

Do you mean put the filter in derived table or main table?
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34953701
no i dont
i just mean that adding filters to a join process will result most of the time in a nested loop join.

But.
it will be much better performance if you add filters to your inner query (derived table)
couse the join process will have less rows to ivaluate
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Mr_Shaw
ID: 34953789
I assume the SQL Optimizer decides on what approach it takes to process derived tables?
0
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 240 total points
ID: 34954345
yes ofcourse
but you can help it reach the right decission by tuning your query :-)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34957395
>>I tested the following:

Select * from Products P
(select ID, Name where country = 'UK' From shops) S
ON P.ID = S.ID
Where P.ID IN (1,2,4,55,66)
<<
You did?  

Since it does not compile and you do not state whether you want an INNER or OUTER JOIN, I can only guess this is what you meant:
SELECT  *
FROM    Products P
        INNER JOIN (SELECT  ID, Name FROM shops WHERE   country = 'UK') S ON P.ID = S.ID
WHERE   P.ID IN (1, 2, 4, 55, 66)

Open in new window

0
 
LVL 10

Assisted Solution

by:John Claes
John Claes earned 240 total points
ID: 34958866
acperkins::
if not ginving the Join statement SQL changes the , between 2 tables into a Inner join ;-)

ashilo and Mr_Shaw:
The query you're using folows my prevoius post :

The derived tabel is created from the inner select-query
SELECT  ID, Name FROM shops WHERE   country = 'UK'

This table is then joined with Products P
using the INNER JOIN (, is replaced by INNER JOIN )

Then this result will be checked for the given ID's


To optimize this Query By 1 step
loose the filter upon the Joined result and make the join result smaler
SELECT  *
FROM    Products P
INNER JOIN ( SELECT  ID, Name 
             FROM shops 
             WHERE   country = 'UK') S 
ON P.ID = S.ID AND P.ID in (1, 2, 4, 55, 66)

Open in new window


To Optimze this query By 2 steps
Making the derived Table Smaler and so also the join
SELECT  *
FROM    Products P
INNER JOIN ( SELECT  ID, Name 
             FROM shops 
             WHERE   country = 'UK' AND S.ID in (1, 2, 4, 55, 66)) S ON P.ID = S.ID

Open in new window




0
 

Author Closing Comment

by:Mr_Shaw
ID: 34959923
thanks.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34960917
poor_beggar,
>>if not ginving the Join statement SQL changes the , between 2 tables into a Inner join ;-) <<
You had better double check that.  While SQL Server is very smart, what it cannot do is fix a comple error.  Thre is no way in Hell that SQL Server would convert this to an INNER JOIN:
Select * from Products P
(select ID, Name where country = 'UK' From shops) S
ON P.ID = S.ID
Where P.ID IN (1,2,4,55,66)
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now