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 |
|
dbuser123
Starting Member
20 Posts |
 Posted - 2006-01-04 : 14:50:57
|
| I have a DB structure something similiar to thisT1 a col T11. T11 is the pk(identity)T2 cols T11, T21, T22. T11(FK), T21 is the primary. T2 is related to T1.T3 cols T11, T21, T31. T11, T21 is the primary(FK). T3 is related to T2.My query needs to capture T11, T21, T22 & T31 where T21 = 0. so T21 can go from 0..n for each of T11.Right now i have a left join on those 3 tables. The problem is it does a table scan for T2 and T3 to get the T21 = 0 !!Select TOP 1000 T11, T2.T21, T2.T22, T31 FROM T1 LEFT JOIN T2 ON T1.T11 = T2.T11LEFT JOIN T3 ON T2.T11=T3.T11 AND T2.T21=T3.T21 WHERE T2.T21=0 ORDER BY T22There are 200k records in T1, 2 milion in both T2 & T3 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-04 : 14:54:32
|
| SQL Server does not automatically add indexes to the foreign key as it does on the primary key. So did you manually add the index after setting up the foreign key?BTW, you have over generalized your problem so much, that it is impossible to figure otu what you are doing. If the index on the foreign key doesn't help, please provide a better explanation.Tara Kizeraka tduggan |
 |
|
|
dbuser123
Starting Member
20 Posts |
Posted - 2006-01-04 : 15:16:09
|
| but on T2 and T3 there are clustered indexs for T11, T21.is that not enough.Im not sure i understand. Please let me know what details you need. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-04 : 15:17:32
|
| Please post the DDL for your tables, including all indexes and constraints.Tara Kizeraka tduggan |
|
|
|
dbuser123
Starting Member
20 Posts |
Posted - 2006-01-04 : 15:30:59
|
| Im sorry that would be impossible. I work for a security firm. There is no way i can do that without getting caught !! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-04 : 15:34:32
|
| It doesn't have to be the exact DDL. You can change it, just don't over generalize it so much that it's impossible for us to read. Script the exact DDL using EM making sure to select the options for indexes and constraints on the third tab. Then modify the output by changing the column names, index names, and constraint names. Any column not involved in the query, constraints, or indexes can be removed from the output.Tara Kizeraka tduggan |
|
|
|
|
|
|
|
|
Query Help !!