Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Posts
    9

    Unanswered: Whatís the fastest way to make a search?

    Hello

    I'm making an MVC-application and don't know what way to go in my database design.

    Letís say I have a table of Resources and have 1 million records in that table compared to having 10 or more tables, one for each type of resource, is it faster to have fewer tables with more records or is it faster to have lots of tables with a smaller amount of records in them?

    Iím thinking there are two searches anyway; one for the table and one for the record, but are having lots of tables slowing things down?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Fastest most flexible by far is a single table with properly designed column data types and appropriate indexes)
    One table per 'resource' is a seemingly attractive approach but requires mre development time. More maintenance. Is more limiting
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Your design error has a name

    Letís say I have a table of Resources and have 1 million records {rows are not anything like records} in that table compared to having 10 or more tables, one for each type of resource, is it faster to have fewer tables with more records {sic} or is it faster to have lots of tables with a smaller amount of records {sic} in them?
    You have re-invented the design flaw called "attribute splitting" !! Would you really prefer to have a "Personnel" table , or "Female_Personnel" and "Male_Personnel" instead? How abotu splitting them by eye colors?

    Want to try to re-discover EAV ?

    Million rows is not that big today. You can index it, you can partition it. Performance will be fine.

  4. #4
    Join Date
    Feb 2008
    Location
    Columbus, OH
    Posts
    26
    Personally, I wouldn't worry about partitioning the index at a million records, but you may find it useful or fun. Using covered indexes should satisfy the speed requirements.

    But please be mindful that indexes aren't free. They cost space.

    Code:
    DECLARE @TableName VARCHAR (200)
    
    SET @TableName = 'dbo.YourTableNameHere'
    
    SELECT    
    i.name                  AS IndexName,    
    s.used_page_count * 8   AS IndexSizeKB
    FROM sys.dm_db_partition_stats  AS s 
    JOIN sys.indexes                AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
    WHERE s.[object_id] = object_id(@TableName)
    ORDER BY i.name
    Again, at a million records it shouldn't be an issue.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •