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 |
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
 Posted - 2010-07-31 : 23:10:11
|
I have two tablesMytable(MyTableID int primary key,val1)andMysecurity(MySecurityID PK,MytableID FK REF, SecurityTypeID)In Mysecurity there is a foreign key reference to the primary key in mytable, the issue is I currently only have to check each record in mytable for a mysecurity record.Select * fromMyTable aLeft joinMysecurity bon a.MytableID = b.MyTableIDWould it be better to insert a blank record (Only MySecurityID and MyTableID will have values the other fields will be null), this way I can avoid a left join?there will be 100,000 records in mytable and about 30,000 will have security.What's the best way to do this?
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-01 : 01:01:38
|
| Why do you want to avoid a LEFT JOIN?Seems entirely reasonable to me that rows in MySecurity MUST have a row in MyTable, but rows in MyTable do not have to have a row in MySecurity. |
 |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2010-08-01 : 01:36:23
|
Dumb reason, but there was someone on here a while back that stated how there should never be left joins in a properly modeled database. Granted Most people felt his opinion was not accurate, But It made me question this scenerio and want to investigate if maybe being able to use a inner join might be better for performance than no matching record... I guess in theory as long as it's indexed properly there's no benefit, but just wanted to make sure I wasn't missing anything.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-01 : 01:46:33
|
| I think having a "dummy" record will be slower. It will match 70% of your rows, and the index entry will probably not be very selective - which may result in Table Scan rather than Index usage; it will inflate the table size, which in turn may adversely effect performance as fewer records / page.I would liken it to the practice of storing "0" or "blank" instead of NULL. If the data is not known it should not exist - and thus it should not be a "pseudo / dummy" value. |
|
|
|
|
|
|
|
|
left join or insert null and inner join?