Post reply

separate or one database ?

  • November 4, 2014 at 5:41 pm

    #312689

    what is better what do you think ? I am thinking about our database desing

    separate db according to year ? 2008-2014 We have now

    or one db with all data? I would like to create this

    one database

    advantage

    -more comprimation , so less size of data

    -easy administrating, easy optimalizating

    -easy query without search according to year for select database

    - ??? any next

    disadvantage

    -lot of operation, performance, time in special issue (example add columns - depends on requirements of customers / 1times for year or 2years )

    -rebuild index on all data if you update/insert/delete data ( on separate you insert just on last one - moving from production database, last one is still rebuild, but with less time,less operations and less data)

    - ??? any next

    thanks guys for advance

  • November 4, 2014 at 6:04 pm

    #1756641

    next advantage for separate

    - backup only last database( this year ) , where are data inserted. ( not needed backup old years )

  • November 5, 2014 at 5:22 am

    #1756715

    Or partition the tables based on date; this gives you the best of both worlds: Performance where queries only need to hit one partition and full dataset (at the expese of speed) when the entire history is required.

  • November 5, 2014 at 6:59 pm

    #1756855

    aaron.reese (11/5/2014)

    Or partition the tables based on date; this gives you the best of both worlds: Performance where queries only need to hit one partition and full dataset (at the expese of speed) when the entire history is required.

    I tried , I think that will be better result but

    --without part

    --Table 'TB_MASTER_AGVEN'. Scan count 1, logical reads 20,

    -- with part where =

    --Table 'TB_MASTER_AGVEN'. Scan count 8, logical reads 32,

    -- with part and where like

    -- Table 'TB_MASTER_AGVEN'. Scan count 1, logical reads 20

    finally like is better than = ,,, estimated subtree cost 0,339 for = vs 0,191

    do you know why is this ? it looks like scan all Filegroup , i have 8..

  • November 5, 2014 at 7:26 pm

    #1756861

    After update statistics with FULLSCAN still same ,

    with use hint OPTION ( RECOMPILE ) I get more better result, same like without or with like scan 1, log.reads 20

    I dont understand..

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply