Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Posts
    4

    Unanswered: SQLServer 1 big table or >200 smaller tables?

    I ask this because i'm interested in making the database more 'readable' it has bout 1000 tables, and from them 250 have the same structure. I have the option of putting them toghether in one single big table (more than 150.000 records). The tables are indexed and so will be the big table. How will this impact on the duration of sql queries? The database is in replication, so how will this affect replication time? If u have any suggestions, please let me know.
    Thanx

  2. #2
    Join Date
    May 2002
    Posts
    299
    If you're on sql2k, consider looking into indexed views.

    In general, the smaller the table the faster is data access. By combining into a single table, you will experience higher IO.
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Feb 2003
    Posts
    15
    150,000 records is not a big table. If it's indexed the duration of sql queries should be not be impacted much, if at all.

    The 250 tables have the same structure (exactly the same?), but do they have the same type of data, or is it completely different types of data?

    Avoiding having to join on 250 distinct tables in queries and instead joining on just one table would be reason enough for me to switch.

  4. #4
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    Definately join the 250 tables with the same structure into one. Simply add a single field which differentiates the records from the different tables. 150,000 records is tiny.

    Combining them will NOT increase 'IO' time.

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  5. #5
    Join Date
    Feb 2003
    Posts
    109

    150k records is a joke

    definitely move them into one table.

    how many columns wide are they?
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  6. #6
    Join Date
    Feb 2003
    Posts
    109

    tip

    if you make a sp that will do the append based on a input parameter (source table)

    it will simplify this greatly.

    ____________________
    Alter Procedure spAppendTable
    @tblName varchar(500)
    As

    DECLARE @sql1 VARCHAR(2000)


    --Generate the statement to be executed using the value
    SET @sql1 = 'INSERT INTO OneCenter.dbo.Audit_Agent Select
    t.[Login ID],
    t.[Split/Skill],
    t.[ACD Calls],
    t.[ACD Time],
    t.ACWTIME,
    t.HOLDTIME,
    t.TRANSFERRED,
    t.ACD,
    t.I_ACDOTHERTIME,
    t.I_ACDAUXINTIME,
    t.ACWOUTTIME,
    t.AUXOUTTIME,
    t.ACWOUTCALLS,
    t.AUXOUTCALLS,
    t.ACWINTIME,
    t.AUXINTIME,
    t.AUXINCALLS,
    t.ACWINCALLS,
    t.I_AUXINTIME,
    t.I_AUXOUTTIME,
    t.I_RINGTIME,
    t.I_ACDTIME,
    t.I_ACDAUX_OUTTIME,
    t.I_ACWTIME,
    t.TI_AUXTIME,
    t.TI_AUXTIME0,
    t.TI_AUXTIME1,
    t.TI_AUXTIME2,
    t.TI_AUXTIME3,
    t.TI_AUXTIME4,
    t.TI_AUXTIME5,
    t.TI_AUXTIME6,
    t.TI_AUXTIME7,
    t.TI_AUXTIME8,
    t.TI_AUXTIME9,
    t.I_DA_ACDTIME,
    t.I_DA_ACWTIME,
    t.DA_ACDCALLS,
    t.DA_ACWTIME,
    t.TI_AVAILTIME,
    t.TI_OTHERTIME,
    t.Date,
    t.Total_ACDTIME,
    t.Total_ACWTIME,
    t.CONFERENCE,
    t.HOLDCALLS,
    t.I_AUXTIME,
    t.UI
    FROM [' + @tblName + '] t'

    --SET @sql1 = CHAR(39) + @sql1 + CHAR(39)

    --Execute the SQL
    --PRINT @sql1

    EXEC (@sql1)


    /* set nocount on */
    return

    __________________________

    then you just have to run

    EXEC spAppendTable 'Agt_Historical_Q1_02_Archive_mdb|Agent_History_ja n02'
    EXEC spAppendTable 'Agt_Historical_Q1_02_Archive_mdb|Agent_History_fe b02'
    EXEC spAppendTable 'Agt_Historical_Q1_02_Archive_mdb|Agent_History_ma r02'
    EXEC spAppendTable 'Agt_Historical_Q2_02_Archive_mdb|Agent_History_ap r02'
    EXEC spAppendTable 'Agt_Historical_Q2_02_Archive_mdb|Agent_History_ma y02'
    EXEC spAppendTable 'Agt_Historical_Q2_02_Archive_mdb|Agent_History_ju n02'
    EXEC spAppendTable 'Agt_Historical_Q3_02_Archive_mdb|Agent_History_ju l02'
    EXEC spAppendTable 'Agt_Historical_Q3_02_Archive_mdb|Agent_History_au g02'
    EXEC spAppendTable 'Agt_Historical_Q3_02_Archive_mdb|Agent_History_se p02'
    EXEC spAppendTable 'Agt_Historical_Q4_02_Archive_mdb|Agent_History_oc t02'
    EXEC spAppendTable 'Agt_Historical_Q4_02_Archive_mdb|Agent_History_no v02'
    EXEC spAppendTable 'Agt_Historical_Q4_02_Archive_mdb|Agent_History_de c02'
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

Posting Permissions

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