Derived tables overhead - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Derived tables overhead

Author

Topic

ElCapitan
Starting Member

28 Posts

Posted - 2008-12-17 : 09:09:28

Hi all,
If have a query like
SELECT * FROM myTable
INNER JOIN
(SELECT * FROM SomeOtherTable WHERE bla=somecondition) myDerivedTable
ON myTable.commonfield=myDerivedTable.commonfield

My understanding is that the inner query gets run first and the resultset is held in memory until the whole query finishes. What if the derived resultset (i.e. the inner query) returns 100,000 rows for instance, does this not soak up loads of resources? What if this query gets run many times a second because of people hitting the website. Do large resultsets in derived tables then take up too much resource?

Thanks for your time with this.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-17 : 10:12:01

You shouldn't put (select *) in outer query because your matching field returns twice unless you need it for each query. Speed depends on how table are indexed.

ElCapitan
Starting Member

28 Posts

Posted - 2008-12-17 : 10:46:04

quote:
Originally posted by sodeep

You shouldn't put (select *) in outer query because your matching field returns twice unless you need it for each query. Speed depends on how table are indexed.

Thanks for your reply. Yeh, forget I put SELECT * and imagine I just want a couple of fields from the outer query that I fully qulify with the table name. My point was regarding the amount of memory and resources is needed to execute queries with derived tables that return many rows such as 100,000. If many people hit a website per second an each request calls on this query with a heavy derived table then surely holding the resultset of 100,000 records in memory exausts resources. Or, is something else going on under the hood that makes derived tables efficient?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-17 : 10:47:14

All JOINS benefit with smaller sets of data on both inner and outer side.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources