January 26, 2012 at 12:08 am
In Our company we want to log User history in our web application,for example when a user come to a page and save something (Call save stored proc) we log this action some where and ....
First solution is after call any Stored procedure(SP) ,call the SP of "Insert_Log_Table" this decrease performance,
Second is in ORM use multithreading and call "Insert_Log_Table" in another thread and ...
we cant use second solution for some problems in our application architecture,so now what should we do?
how could have concurrency or multithreading,becuase we dont want the user wait for insert into log table and ..?
January 26, 2012 at 2:20 am
This is a tricky requirement and I had to deal with this myself some years ago.
I ended up adding a couple of statements to each stored procedure I wanted to log and save the results to a log table using service broker.
The idea behind is like this:
CREATE PROCEDURE SomeProcedureName
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO LogView
SELECT db_name(), schema_name(), OBJECT_NAME(@@procid);
-- does something
PRINT 'Hello World';
END
First, you need a log table:
CREATE TABLE LogTable (
[DatabaseName] [varchar](50) NOT NULL,
[SchemaName] [varchar](50) NOT NULL,
[ProcedureName] [varchar](255) NOT NULL,
[FirstRun] [datetime] NULL,
[LastRun] [datetime] NULL,
PRIMARY KEY (DatabaseName, SchemaName, ProcedureName)
)
GO
Then you need a logView:
CREATE VIEW LogView
AS
SELECT *
FROM LogTable
GO
The "logView" has a trigger instead of insert that calls service broker:
CREATE TRIGGER TR_LogView ON LogView
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DatabaseName nvarchar(50)
DECLARE @SchemaName nvarchar(50)
DECLARE @ProcedureName nvarchar(255)
SELECT
@DatabaseName = DatabaseName,
@SchemaName = SchemaName,
@ProcedureName = ProcedureName
FROM INSERTED
-- INVOKE LOGGING ON SERVICE BROKER QUEUE
EXEC sp_Log_SB @DatabaseName, @SchemaName, @ProcedureName
END
GO
CREATE PROCEDURE sp_Log_SB
@DatabaseName sysname,
@SchemaName sysname,
@ProcedureName sysname
AS
BEGIN
-- begin dialog on the Service Broker queue
BEGIN DIALOG ...
END
On the other side of the service broker queue you need something that receives, processes the messages and inserts the data into the log table:
UPDATE LogTable
SET LastRun = GETDATE()
WHERE DatabaseName = @DatabaseName
AND SchemaName = @SchemaName
AND ProcedureName = @ProcedureName
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO LogTable (
DatabaseName,
SchemaName,
ProcedureName,
FirstRun,
LastRun
)
VALUES (
@DatabaseName,
@SchemaName,
@ProcedureName,
GETDATE(),
GETDATE()
)
END
Looks complicated, but it's not indeed. It's just a bit of work.
Hope this helps,
Gianluca
-- Gianluca Sartori
January 26, 2012 at 3:19 am
thanks for your replay.Maybe I became confused,I didnt now why trigger is on view?should not be on My Other Table that I want to log Information about it?In All Place that I want to Log Data (I meen some SP) I called a sp for "Insert_Log_Table",but now I want to parallel this work with other work that is not related
January 26, 2012 at 3:27 am
Trigger is on the view in order to allow direct inserts to the table by the service broker procedure.
Stored Procedures calls can work in parallel on the log table, because each call adds an entry in the service broker queue instead of writing the table directly.
-- Gianluca Sartori
January 26, 2012 at 4:17 am
thanks,maybe i should learn more about service broker could you tell me some link for more information to how it works.I only used it for notification from sql to application before.
January 26, 2012 at 4:26 am
These should get you started:
http://msdn.microsoft.com/en-us/library/ms345108(v=sql.90).aspx
-- Gianluca Sartori
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply