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 |
|
sdiwi
Starting Member
27 Posts |
 Posted - 2005-09-02 : 08:52:19
|
| hi there.i ran into this problem for several times now, and until now i always found an acceptable workaround or didnt care for the speed loss...let's assume following query:SELECT a.id, a.name, b.id FROM tablea aLEFT JOIN tableb b ON(......)got it? ok. let's go on...now i don't need most of the rows of tablea. because tablea really has a huge amount of data stored the query is kinda costly and slow.however limiting after the left join does not really improve things.what i need to do, is to limit the results of tablea BEFORE joining left.something like this would be fine for me:SELECT a.id, a.name, b.id FROM tablea a WHERE a.id NOT IN (....) LEFT JOIN tableb b ON(......)Of course this does not work. As this is anyway code inside of an udf, i could use a tablevar or a temp table and initialize it with the rows i need from tablea.but this is clumsy. ain't there an easier way?thanks for your help,peace,sdiwi. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-02 : 08:58:38
|
| Not sure whether this helps youSELECT a.id, a.name, b.id FROM (Select top 100 * from tableA) a LEFT JOIN tableb b ON(......)Otherwise post some sample data and the result you wantMadhivananFailing to plan is Planning to fail |
 |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2005-09-02 : 09:08:24
|
| Put condition in a where clause that limits rows in table a and have usefull index in plays. Also condition should be:<table a column> <operator> <expression>where <operator> is in (<, <=, >, >=, =)...Have statistics updated and sometimes force execution plan. |
|
|
|
sdiwi
Starting Member
27 Posts |
Posted - 2005-09-02 : 09:17:37
|
| @madhivananomg. ya. this does help me...ouch....dam. why didn't i try that one?thank you. i think that was too obvious. i didn't really search for that simple solutions :P... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-02 : 09:27:57
|
| But make sure you get proper result (most desired result) at endYou may use tableA Order by to get relevent dataMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
limiting the results of the first table - TRIVIAL?