SQL Performance JOIN's

Posted on 2005-05-10
Last Modified: 2010-03-19
Does anyone have an idea about the performance issues regarding JOIN's?

If I have two tables

UID | INFO        

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

Question by:surzycki
    LVL 4

    Expert Comment

    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



    Author Comment

    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.

    LVL 4

    Accepted Solution


    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


    Author Comment

    Cool thanks for the info Steve, it has been helpful


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now