locked
Put information into multiple tables RRS feed

  • Question

  • Hi,

    I have one table with around 10 million records.every second entry in this table.

    Table like this.

    Table :tFileDetails

    Columns

    Id int identity

    FileName varchar(1000)

    FilePath--Path in local drive

    FileSize--size in kbs

    ReportTime--from one server to another server copy.

    FileTime--original file copy to folder.

    DeviceId--Device where file come to us

    IsProcessed--bit this successfully read by system then 1 else 0

    Shall I divide this table into multiple columns?.

    All Analysis based on this table.means insertion every second and every minute reports check by people.


    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

    Thursday, February 7, 2013 9:39 AM

Answers

  • The question I would ask is two fold.

    1. Does all of the attributes in this table really represent one thing. The name FileDetails tends to make me think that you might have > 1 row per file, but the columns say that you are really modeling a file. There seems to be a bit of mixing of filename, and file processing, but that would be more up to you to decide.

    The fact is, when you model > 1 thing into a single table, you create a potential performance bottleneck because the more often all of the columns in a table are the focus of your queries, the more likely that the locks you are waiting for are truly there because the other user is using the same data you need, and not just data physically stored in the same location.

    2. Building on the first question, do you use all of the data at the same time? Like I can see a table for files having the file information, and then a table for processing the files. The processing table to me might even be one row per operation, but that is really up to what you need.

    Your queries also bely a few things you could fix to make performance better.

    The first two queries suggest you oughto add a "fileType" column, and load it on insert to get the type so it can be indexed. That could save you a lot of blocking

    (select count(*) from tFileDetails where FileName like '.jpg')

    (select count(*) from tFileDetails where FileName like '.txt')

    Your queries seem to me that it probably wouldn't be all that useful to make structural changes, depending on the whole processing thing. You may want to build a side table to handle the counts if that is the big deal, and just cache the answer to the query every N minutes, depending on how perfect the answer needs to be, but usually, the correct answer a few minutes ago is good enough for everything except the most sensitive situations.


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Fanny Liu Friday, February 8, 2013 9:57 AM
    • Marked as answer by Fanny Liu Friday, February 15, 2013 8:26 AM
    Thursday, February 7, 2013 11:08 PM

All replies

  • ????????????????????

    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

    Thursday, February 7, 2013 12:02 PM
  • Can you post the top 3 most important queries on this table?

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Thursday, February 7, 2013 12:08 PM
  • (select count(*) from tFileDetails where FileName like '.jpg')

    (select count(*) from tFileDetails where FileName like '.txt')

    Select count(*) from tdevice wheer IsActive=1)

    Select Top 1 FileName from tFileDetails

    where DeviceId in (select Id from tDevice where IsActive=1)

    order by FileTime

    Select count(*) as blankfile from tFileDetails where FileSize <11(Kb)

    Select count(DeviceId) ActiveDevice from tFileDetails join tDevice(Acrtived) where ReportITime=Getdate()--aslo date as input in sp.


    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

    Thursday, February 7, 2013 12:15 PM
  • The question I would ask is two fold.

    1. Does all of the attributes in this table really represent one thing. The name FileDetails tends to make me think that you might have > 1 row per file, but the columns say that you are really modeling a file. There seems to be a bit of mixing of filename, and file processing, but that would be more up to you to decide.

    The fact is, when you model > 1 thing into a single table, you create a potential performance bottleneck because the more often all of the columns in a table are the focus of your queries, the more likely that the locks you are waiting for are truly there because the other user is using the same data you need, and not just data physically stored in the same location.

    2. Building on the first question, do you use all of the data at the same time? Like I can see a table for files having the file information, and then a table for processing the files. The processing table to me might even be one row per operation, but that is really up to what you need.

    Your queries also bely a few things you could fix to make performance better.

    The first two queries suggest you oughto add a "fileType" column, and load it on insert to get the type so it can be indexed. That could save you a lot of blocking

    (select count(*) from tFileDetails where FileName like '.jpg')

    (select count(*) from tFileDetails where FileName like '.txt')

    Your queries seem to me that it probably wouldn't be all that useful to make structural changes, depending on the whole processing thing. You may want to build a side table to handle the counts if that is the big deal, and just cache the answer to the query every N minutes, depending on how perfect the answer needs to be, but usually, the correct answer a few minutes ago is good enough for everything except the most sensitive situations.


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Fanny Liu Friday, February 8, 2013 9:57 AM
    • Marked as answer by Fanny Liu Friday, February 15, 2013 8:26 AM
    Thursday, February 7, 2013 11:08 PM