| Author |
 Topic  |
|
|
Aleph_0
Yak Posting Veteran
79 Posts |
 Posted - 08/25/2011 : 14:52:14
|
Maybe the tables I'm working with are too large, but any time I write a concise query (that uses correlated subqueries) that would make any purist happy, it takes forever. I always have to resort to using temp tables.
That got me thinking, what would I do if I were trying to make a view? I couldn't use temp tables, and doing multiple joins on those large tables wouldn't make things faster. Is there some other option when trying to make a view-like object? I suppose you could always just create a new table instead of a view, but then you'd have to do that every time the original table is updated.
This isn't a specific problem I'm working on - just trying to get a better understanding of SQL in general. Of course, now that I've asked, I bet I'll run into this very problem soon!
Thanks! |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3425 Posts |
Posted - 08/26/2011 : 05:30:21
|
I don't like correlated sub queries. You can end up running that query for every row in the outer query.
Generally rewriting as a derived table (if possible) and joining is a much better approach.
Ya -- can you post an example. Sounds like an interesting challenge.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 08/26/2011 : 05:47:14
|
In general, the only time a correlated subquery runs once per row is when there's a TOP 1 .. ORDER BY in it, or a triangular join. Otherwise they're pretty much equivalent to joins.
--
Gail Shaw
SQL Server MVP |
|
|
| |
Topic |
|
Correlated subqueries are ALWAYS slow for me...
