none
Does accessing large tables from the same database at the same time affects Database performance?

    Question

  • Hi 

    I have a scenario where there is one database in the production, Lets call it "Mydb". It has a large table with millions of rows which are accessed frequently for read/write from a windows service.

    I want to update all the rows in that table. So I am planning to make a backup of existing table and work on the backup table but inside the "MyDb".

    Will it slow down the  performance  of "MyDb" even though I am using a backup table?

    Is it recommended that, I should NOT be using "MyDb" and use a temp Database?

    Hope my question is clear. Thanks in advance.



    Wednesday, February 13, 2013 7:41 PM

Answers

  • Ultimately the answer depends on many factors.  You imply that you want to update the actual table but propose using a copy (regardlesss of where the table resides).  Is the change something you need to do on a temporary basis or does the actual table ("accessed frequently ... from a windows service") need to be changed at some point? 

    The short and simplistic answer is that updating the table, a copy of the table in the same database, or even a copy of the same table resident on the same disk drive will impact performance.  In addition, read and write performance are usually diametrically opposed, so the discussion of "performance" depends on how you evaluate it at a global scale.  You will need to provide more information if you want useful suggestions.   At a minimum, this should include the version and sp level of sql server, ddl of the table involved, and query you intend to use to update the table. 

    Wednesday, February 13, 2013 9:08 PM

All replies

  • Ultimately the answer depends on many factors.  You imply that you want to update the actual table but propose using a copy (regardlesss of where the table resides).  Is the change something you need to do on a temporary basis or does the actual table ("accessed frequently ... from a windows service") need to be changed at some point? 

    The short and simplistic answer is that updating the table, a copy of the table in the same database, or even a copy of the same table resident on the same disk drive will impact performance.  In addition, read and write performance are usually diametrically opposed, so the discussion of "performance" depends on how you evaluate it at a global scale.  You will need to provide more information if you want useful suggestions.   At a minimum, this should include the version and sp level of sql server, ddl of the table involved, and query you intend to use to update the table. 

    Wednesday, February 13, 2013 9:08 PM
  • Ultimately the answer depends on many factors.  You imply that you want to update the actual table but propose using a copy (regardlesss of where the table resides).  Is the change something you need to do on a temporary basis or does the actual table ("accessed frequently ... from a windows service") need to be changed at some point? 

    The short and simplistic answer is that updating the table, a copy of the table in the same database, or even a copy of the same table resident on the same disk drive will impact performance.  In addition, read and write performance are usually diametrically opposed, so the discussion of "performance" depends on how you evaluate it at a global scale.  You will need to provide more information if you want useful suggestions.   At a minimum, this should include the version and sp level of sql server, ddl of the table involved, and query you intend to use to update the table. 

    Hi Scott

    Thanks for your answer.

    I got your point here. However, I am going to answer your questions.

    >>> Is the change something you need to do on a temporary basis or does the actual table ("accessed frequently ... from a windows service") need to be changed at some point? 

    This is going to be a permanent change and its kind of a data migration to a new format which will happen on a certain day. This migration is done into parts over the time and on one specific day, all the services will be shut down for just a few hours and actual table will be replaced by a newly migrated table. We are trying to migrate this data over the time while customer is still in the production. I wanted to make sure that this kind of background migration should not affect their production DB access performance.

    P.S. Getting a db backup to run the migration somewhere else is NOT allowed. Creating a new DBs/Tables on the same server is allowed. Data contains Varbinary column which has serialized object structure in it.

    Wednesday, February 13, 2013 11:05 PM