none
SQL Response Time?

    Question

  • Simple question from an inexperienced SQL programmer.

     

    Lets say I have 2 tables in a SQL database.  One table is relatively small, and one table is very large.  Will the amount of processing time to query the small table be greater because of the existance of the very large table?  Or.... should I seperate the 2 tables into 2 seperate database files?

     

    Are there any rules to follow when designing a multple table SQL database?

    Monday, June 23, 2008 4:04 AM

Answers

  •  Stuck on Code wrote:

    Simple question from an inexperienced SQL programmer.

     

    Lets say I have 2 tables in a SQL database.  One table is relatively small, and one table is very large.  Will the amount of processing time to query the small table be greater because of the existance of the very large table?  Or.... should I seperate the 2 tables into 2 seperate database files?

     

    Are there any rules to follow when designing a multple table SQL database?

     

    Processing time (or Performance) to a small table will not be affected because you have a huge table in the same database. Processing time is more or less depends upon the object being queried. If the query is not refering Large object, it will not affect the performance. There is no need to seperate db in this case.

     

    Madhu

    SQL Server Blog

    SQL Server 2008 Blog

     

    Tuesday, June 24, 2008 11:06 AM
    Moderator
  • There are lots of variables that affect response time - number of simultaneous queries, speed of your disk i/o system, amount of memory, location of tempdb, etc. But I agree with Madhu, it doesn't sound like there is a need in your instance to separate out the table to a new data file - not based on what you've said in your post.

    If you are concerned about performance, you can download the Performance Dashboard Reports from Microsoft and benchmark/monitor the performance. If you anticipate that performance will become an issue based on your benchmarking metrics, you can take steps to head it off - create new indexes, move indexes to another set of spindles, or even move the table to another set of spindles as you've suggested.

    HTH...

    Joe

    Tuesday, June 24, 2008 11:47 AM
    Moderator

All replies

  • As with life, there is no simple answer. Database design is case driven. You first have to understand how it will be used before you can design how it will be implemented. This covers everything from your entity designs down to how to implement the database on the physical hardware.

     

    If you have a scenario you would like to post, then this question can be answered. Otherwise, there are no general rules for multiple tables. Feel free to post your scenario and we can walk through it.

    Monday, June 23, 2008 9:12 PM
  •  Stuck on Code wrote:

    Simple question from an inexperienced SQL programmer.

     

    Lets say I have 2 tables in a SQL database.  One table is relatively small, and one table is very large.  Will the amount of processing time to query the small table be greater because of the existance of the very large table?  Or.... should I seperate the 2 tables into 2 seperate database files?

     

    Are there any rules to follow when designing a multple table SQL database?

     

    Processing time (or Performance) to a small table will not be affected because you have a huge table in the same database. Processing time is more or less depends upon the object being queried. If the query is not refering Large object, it will not affect the performance. There is no need to seperate db in this case.

     

    Madhu

    SQL Server Blog

    SQL Server 2008 Blog

     

    Tuesday, June 24, 2008 11:06 AM
    Moderator
  • There are lots of variables that affect response time - number of simultaneous queries, speed of your disk i/o system, amount of memory, location of tempdb, etc. But I agree with Madhu, it doesn't sound like there is a need in your instance to separate out the table to a new data file - not based on what you've said in your post.

    If you are concerned about performance, you can download the Performance Dashboard Reports from Microsoft and benchmark/monitor the performance. If you anticipate that performance will become an issue based on your benchmarking metrics, you can take steps to head it off - create new indexes, move indexes to another set of spindles, or even move the table to another set of spindles as you've suggested.

    HTH...

    Joe

    Tuesday, June 24, 2008 11:47 AM
    Moderator
  • There is a free tool that allows you to measure response time 
    Thursday, April 29, 2010 8:48 PM