Professional Web Applications Themes

huge table and view - Microsoft SQL / MS SQL Server

Here is the situation: My application creates a record in the table A for each of the user session with a unique ID and some other user information. After a while this table is getting huge(think of billions of rows). We want to get rid of some old or unused data from the table in order to improve performance (the table is well-indexed though). We certainly don't want to try the 'delete' because that would lock the table and cause problem for our live application. Does any one know of any good way dealing with this kind of issue? I ...

  1. #1

    Default huge table and view

    Here is the situation:

    My application creates a record in the table A for each of the user session
    with a unique ID and some other user information. After a while this table
    is getting huge(think of billions of rows). We want to get rid of some old
    or unused data from the table in order to improve performance (the table is
    well-indexed though). We certainly don't want to try the 'delete' because
    that would lock the table and cause problem for our live application.

    Does any one know of any good way dealing with this kind of issue?

    I am also thinking of a redesign. For example, create a dynamic view which
    can include member tables with data we need for use and we can abandon old
    tables by altering the view from time to time. But then I ran into a problem
    which involves the above unique ID field. I can't set it to an identity
    field in the member tables because of the view's restriction. So I would end
    up to set up another table B just to have an identity field and my
    application would grap the unique ID in table B and populates it to table A.
    Then I would have a table B with billions of ID's again. Though table B is
    smaller it still concerns me.

    If you have some good solutions, please let me know.

    Thank you,
    TH


    T Guest

  2. #2

    Default Re: huge table and view

    the delete is simple.

    set rowcount 1000
    declare rowcount int
    set rowcount = 1
    while rowcount > 0
    begin
    delete mytable where .....
    set rowcount = rowcount
    waitfor delay '0:00:01' -- don't hog the server
    end
    set rowcount 0



    rolling partitions is a common approach. you can set min and max identity
    values so that do not overlap. also you can use a partitioned view, to help
    manage the partitions. this approach allows you to just truncate the
    underlying table, which is fast and uses little log space.


    -- bruce (sqlwork.com)



    "T H" <com> wrote in message
    news:phx.gbl... 
    session 
    is 
    problem 
    end 
    A. 


    bruce Guest

  3. #3

    Default Re: huge table and view

    Thank you for your comments on the 'delete'. I checked the BOL and it is
    recommending a 'delete' with a 'select top'. I think either way will work.

    For the partitioned views (the identical fields for the member tables are
    the year and the month), I am thinking to use GUID as a global unique ID
    instead of getting it from an additional table.

    Did any one here have experience with GUID (generated by NEWID()) and how is
    the performance looking?

    Thank you,
    TH

    "bruce barker" <com> wrote in message
    news:%phx.gbl...
    the delete is simple.

    set rowcount 1000
    declare rowcount int
    set rowcount = 1
    while rowcount > 0
    begin
    delete mytable where .....
    set rowcount = rowcount
    waitfor delay '0:00:01' -- don't hog the server
    end
    set rowcount 0



    rolling partitions is a common approach. you can set min and max identity
    values so that do not overlap. also you can use a partitioned view, to help
    manage the partitions. this approach allows you to just truncate the
    underlying table, which is fast and uses little log space.


    -- bruce (sqlwork.com)



    "T H" <com> wrote in message
    news:phx.gbl... 
    session 
    is 
    problem 
    end 
    A. 



    TH Guest

Similar Threads

  1. TABLE and VIEW same identifier: Please help
    By soup_or_power@yahoo.com in forum MySQL
    Replies: 3
    Last Post: June 8th, 05:56 AM
  2. loading only few rows from huge table
    By Mark in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: December 29th, 05:38 PM
  3. DBI problem : How can I load quickly one huge table with DBI ??.
    By Tim Haynes in forum PERL Miscellaneous
    Replies: 3
    Last Post: September 13th, 03:43 AM
  4. Scroll Row in Table into View?
    By Saravana in forum ASP.NET General
    Replies: 1
    Last Post: July 28th, 06:19 PM
  5. How to deal with huge table?
    By T H in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 1st, 11:50 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139