• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

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



0
surzycki
Asked:
surzycki
  • 2
  • 2
1 Solution
 
stevetheskiCommented:
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


0
 
surzyckiAuthor Commented:
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
0
 
stevetheskiCommented:
Stefan,

If it is going to grow to 10-50 million you most definately want to use partitioned views as i explained earlier.  1 million records is really nothing to a properly indexed table.  but 10+ is a good time to consider alternatives.  Also with large databases denormalization is "Normal" Disk space these days is cheep.  so if you are really worried, throw both tables into 1 partitioned view.  It really would depend on how many rows are going to be stored in the 2 tables.  

if it is something like
useringo and address where only 25% of the people have addresses then making one table with useringo and address is not good as you can get fragmented tables.  but if it is userinfo and address where 90% of the rows will be populated all the time then its not as much of an issue.

best advice is to look at partitioned views in "Books On Line"
and if you can provide me with an example of what data will be in each table and the % that each column is filled we can go from there


Steve
0
 
surzyckiAuthor Commented:
Cool thanks for the info Steve, it has been helpful

Stefan
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now