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.
| Author |
Topic |
|
Aleph_0
Yak Posting Veteran
79 Posts |
 Posted - 2011-08-25 : 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
38200 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-08-26 : 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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-26 : 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 ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Correlated subqueries are ALWAYS slow for me...