Ring buffer for Real-Time data

Hello
Can anybody help me with strategies for implementing a Ring Buffer for
Real-Time Data?
My application inserts real-time data (at a very heavy rate) and I
need to replace/delete old data on regular basis. If not the disk will
eventually be filled up...
brgds
EHansen
Consider using a partitioned view. When you need to add more data, create a
new table and alter the view. When you want to purge a large chunk alter
the view to exclude the table that has the chunk, then drop the table. The
BOL talks about partitioned views.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Hansen" <ehansen940@.hotmail.com> wrote in message
news:809ff900.0410220459.561bd2f9@.posting.google.c om...
Hello
Can anybody help me with strategies for implementing a Ring Buffer for
Real-Time Data?
My application inserts real-time data (at a very heavy rate) and I
need to replace/delete old data on regular basis. If not the disk will
eventually be filled up...
brgds
EHansen

Ring buffer for Real-Time data

Hello
Can anybody help me with strategies for implementing a Ring Buffer for
Real-Time Data?
My application inserts real-time data (at a very heavy rate) and I
need to replace/delete old data on regular basis. If not the disk will
eventually be filled up...
brgds
EHansenConsider using a partitioned view. When you need to add more data, create a
new table and alter the view. When you want to purge a large chunk alter
the view to exclude the table that has the chunk, then drop the table. The
BOL talks about partitioned views.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Hansen" <ehansen940@.hotmail.com> wrote in message
news:809ff900.0410220459.561bd2f9@.posting.google.com...
Hello
Can anybody help me with strategies for implementing a Ring Buffer for
Real-Time Data?
My application inserts real-time data (at a very heavy rate) and I
need to replace/delete old data on regular basis. If not the disk will
eventually be filled up...
brgds
EHansensql

Ring buffer for Real-Time data

Hello
Can anybody help me with strategies for implementing a Ring Buffer for
Real-Time Data?
My application inserts real-time data (at a very heavy rate) and I
need to replace/delete old data on regular basis. If not the disk will
eventually be filled up...
brgds
EHansenConsider using a partitioned view. When you need to add more data, create a
new table and alter the view. When you want to purge a large chunk alter
the view to exclude the table that has the chunk, then drop the table. The
BOL talks about partitioned views.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Hansen" <ehansen940@.hotmail.com> wrote in message
news:809ff900.0410220459.561bd2f9@.posting.google.com...
Hello
Can anybody help me with strategies for implementing a Ring Buffer for
Real-Time Data?
My application inserts real-time data (at a very heavy rate) and I
need to replace/delete old data on regular basis. If not the disk will
eventually be filled up...
brgds
EHansen

Rewriting Insert Statements

Hi Friends,
I have the following set of Insert Statements that calculates sums for various criteria and inserts a row at a time onto my table.
I have a row for every month starting from January with sums for 4 severity levels. So for 12 months that would be 48 Insert Statements and if I want to do this for 4 different types of [EName] that would be 48 * 4 = 192 Insert Statements. Is there a better way to write this. Thanks for your help

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 1 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 1)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 2 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 2)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 3 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 3)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 4 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 4)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 1 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 1)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 2 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 2)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 3 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 3)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 4 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 4)Maybe something along the lines of

SELECT 'OVERALL' AS [EName], trendmonth AS TrendMonth, SeverityLevel , Sum([Count])
FROM dbo.tbl_Ticket
group by EName, Trendmonth, SeverityLevel|||Works Great!! Thank you very much.

reusing a single conversation handle

Hi

I have a replicated table that has a trigger attached to the it. The trigger fires off a service broker message for inserts. Originally for every insert, I would begin a conversation, send, and end the conversation when target send an end conversation. Since replication process is only using a single spid, I would like to reuse 1 conversation. the following is what I have for the send procedure in the initiator. I check the conversation_endpoints for any open conversation, if it's null, I start a new conversation and send else just send with the existing conversation. Is there anything wrong with this code? What could cause the conversation on the initiator to be null if I never end the conversation on the initiator side? thanks

DECLARE @.dialog_handle uniqueidentifier

select @.dialog_handle = conversation_handle from sys.conversation_endpoints where state = 'CO'

IF @.dialog_handle is NULL

BEGIN DIALOG CONVERSATION @.dialog_handle

FROM SERVICE [initiator]

TO SERVICE 'target'

ON CONTRACT [portcontract];

SEND ON CONVERSATION @.dialog_handle

MESSAGE TYPE [Port] (@.msg)

Hi

Reusing conversations is generally a good idea and one good way to achieve this is to store a mapping of spid to conversations handle in a lookup table. http://blogs.msdn.com/remusrusanu/archive/2007/05/02/recycling-conversations.aspx goes into the details and helps answer your questions.

Thanks

Ketan