Link to home
Start Free TrialLog in
Avatar of surzycki
surzycki

asked on

SQL Performance JOIN's

Does anyone have an idea about the performance issues regarding JOIN's?

If I have two tables

UID | INFO        
UID | INFO2

and the two of these are over a million records, being hit regularly but 10's of thousands of web users

What kind of impact would I have seleting INFO1 and INFO2 using a JOIN?

Thanks in advance
Stefan



Avatar of stevetheski
stevetheski
Flag of United States of America image

only a million records?  
that should be nothing.

make sure that the tables are indexed properly and have the relations set up properly

if there is still a problem consider partitioning your tables i.e.
info_0 -> check constraint make sure only 0's are inserted
info_0-> check constraint make sure only 1's are inserted
all the way to 9
info1_0 -> check constraint make sure only 0's are inserted
info1_0-> check constraint make sure only 1's are inserted
all the way to 9

then make a view nmed info that unions all the info_x tables
then make a view nmed info1 that unions all the info1_x tables
now your data is segmented and it will only hit the tables required by the where clause thus reducing reads

Steve


Avatar of surzycki
surzycki

ASKER

Well actaully to start there will be 1 million but should go to 10-50 million. Does this change anything? Or am I just being paranoid. I have never seen a large DB.

Stefan
ASKER CERTIFIED SOLUTION
Avatar of stevetheski
stevetheski
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Cool thanks for the info Steve, it has been helpful

Stefan