Re: How to limited number of rows in a table?
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 09/12/04
- Next message: Louis Davidson: "Re: Insert.... Select From... question"
- Previous message: Adam Machanic: "Re: Date Conversion Question"
- In reply to: Bob Castleman: "Re: How to limited number of rows in a table?"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 11 Sep 2004 21:14:21 -0500
If this is a log table (basically a queue, right), database blocking should
never be a problem (hardware might be a different story of course!) The
reason is that if indexes are done right, you basically pop old stuff off
the queue on the one end of the index (must be unique!) and push stuff on
the other end. You could run the background task all of the time with
little impact to the active users (again, this gets messy from a hardware
point of view, but if you are doing heavy data insertion 24/7 you had best
be getting paid well for it).
Specs? What is that? All anyone I ever work with wants to do is prototype
and code. Seriously though, you are exactly correct. Lets be clear, if at
all possible, what I might do is have multiple tables that OLTP work is
being entered into periodically, or via a hash or modulo function or
something. Then filter off rows as often as possible to a data
warehouse/operational data store for reading so you never have reading
contention.
-- ---------------------------------------------------------------------------- Louis Davidson (drsql@hotmail.com) Compass Technology Management Pro SQL Server 2000 Database Design http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies will be ignored :) "Bob Castleman" <nomail@here> wrote in message news:uKvIFJ1lEHA.2680@TK2MSFTNGP15.phx.gbl... > I'll bend on the trigger, but I think a nightly job may be too infrequent > from the way he explains the problem. I suppose it matters most why the > number of rows needs to be limited. Also, isn't blocking an issue even on a > nightly job if the data insertion is 24/7? Lets say the highest data input > is 10 entries per second or 36,000 entries per hour. If the threshold is at > 110% of a million and it gets purge down to 90%, then even at 24/7 of > constant maximum input, the purge part of a trigger would be invoked only > every 5.5 hours (200,000/36,000). Conversely, unless a nightly job always > purged to 90% (may or may not be desirable), if the 110% threshold were not > reached at the time the job was invoked, 24 hours of max data throughput > could see as much as 864,000 - 1 rows too many before the next time the job > fires. > > I guess it really depends on the precise details of the task at hand. > SPECIFICATIONS!!!! > > :) > > Bob > > > "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message > news:%23EqMb10lEHA.324@TK2MSFTNGP11.phx.gbl... > >I would not suggest a trigger for this. You will have trouble with > > blocking, and running the cleaning process multiple times, etc. I would > > think just having a job that runs through the system every night would be > > best. Nothing is built in to do this, but it would be pretty easy to > > implment. > > > > If you want help implementing, just ask (but you have to share your > > solution, as this would be pretty useful for anyone who uses SQL Server to > > log operations :) > > > > > > -- > > -------------------------------------------------------------------------- -- > > Louis Davidson (drsql@hotmail.com) > > Compass Technology Management > > > > Pro SQL Server 2000 Database Design > > http://www.apress.com/book/bookDisplay.html?bID=266 > > > > Note: Please reply to the newsgroups only unless you are > > interested in consulting services. All other replies will be ignored :) > > > > "Bob Castleman" <nomail@here> wrote in message > > news:ugw5bj0lEHA.3612@TK2MSFTNGP12.phx.gbl... > >> You could create a job that periodically cleans out the old stuff, but it > >> sounds like a job for a trigger. But intead of the trigger doing a > > COUNT(*) > >> on the table at every insert (possibly the expensive part), use a counter > >> stored in the database until the counter reaches a certain threshold then > >> have it invoke the cleaning process. > >> > >> Bob Castleman > >> SuccessWare Software > >> > >> > >> "John" <johnfofawn@hotmail.com> wrote in message > >> news:d89364bd.0409100635.7d66bcb8@posting.google.com... > >> > Hi, > >> > > >> > I have a process that collects data from sensors and stores the data > >> > in a table in SQL Server. The data can arrive multiple times per > >> > second for several hours at a time or I can only get one or two data > >> > entries per hour. > >> > > >> > I want to limit the number of rows that can exist in this table. I'd > >> > like to age off the old rows so that only the last 100,000 or > >> > 1,000,000 rows are kept in the table. > >> > > >> > I don't want to check the number of rows each time I do an insert > >> > because I think this might be too expensive. Am I wrong? > >> > > >> > I'm thinking I need to create a "watchdog" process that will > >> > periodically check the number of rows in the table and if it is over > >> > 110% of the desired capacity then it will trim it back to 90% of the > >> > desired capacity by dropping the oldest rows. > >> > > >> > Is this the best way to solve this problem or is there some built-in > >> > SQL Server way to do this? > >> > > >> > Thanks! > >> > John > >> > >> > > > > > >
- Next message: Louis Davidson: "Re: Insert.... Select From... question"
- Previous message: Adam Machanic: "Re: Date Conversion Question"
- In reply to: Bob Castleman: "Re: How to limited number of rows in a table?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
