Prev: setting text result set more then 8192
Next: Best way to store vectors?
From: SqlBeginner on 8 Jul 2010 16:18
Database1:
CREATE TABLE [dbo].[tbl1]
(
[sno] [int] IDENTITY(1,1) NOT NULL primary key,
[strkeyValue] [varchar](max) NOT NULL,
[dt] [datetime] NOT NULL
)
GO

sample:
S:2221|TId:|dtstmp:2010/6/24 12:10:46|division:one|rate:10|



Database2:
CREATE TABLE [dbo].[tbldest](
[sid] [varchar](50) NULL,
[TId] [varchar](50) NULL,
[division] [varchar](50) NULL,
[dtTimeStamp] [datetime] NULL,
[strkeyValue] [varchar](max) NULL,
[dt] [datetime] NULL,
[num] [varchar](100) NULL,
[rate] [varchar](50) NULL,
[qt] [varchar](50) NULL,
[qty] [tinyint] NULL,
[Cat] [varchar](50) NULL,
[type] [tinyint] NULL,
[Code] [varchar](50) NULL,
[strId] [varchar](50) NULL
)
GO

A. on a single day we are looking at 50K to 1L entries coming into tbl1.
B. Once it reaches there that data needs to be parsed and and each string
will translate into a single record within tbldest table.
C. From tbldest based on division the records would be moved to its own
individual tables.

Since this needs to happen almost immediately! what is the best method to do
this?

Possibilities which i am able to think of:

Writing a trigger on tbl1 and once records reach there immediately call a
User defined function to parse the string and break it into multiple columns
and push it to Database2.dbo.tbldest

Not sure how to push data immediately from tbldest to 'n' number of smaller
tables based on the column division?

Regards
pradeep
From: Eric Isaacs on 8 Jul 2010 17:00
After the data has been entered into tblDest, what do you do with the
record in tbl1? If it's not needed, I would consider just writing an
INSTEAD OF INSERT TRIGGER on tbl1. You would want to write it in such
a way that it handles inserts of multiple rows in tbl1. The idea
would be to parse the values out of the strings and insert them into
the tblDest instead. You don't need a cursor or a loop to do this.
You should be able to parse out each value with standard string
functions in a set operation rather than in a loop.

You also said you need to push the data from tblDest to the other
tables. You could do this in the same trigger, so you really don't
need tblDest at all.
It might be the case that tblDest simplifies this for you, but it
could be created on the fly within the trigger.

-Eric Isaacs
From: Erland Sommarskog on 8 Jul 2010 17:25
SqlBeginner (SqlBeginner(a)discussions.microsoft.com) writes:
> Writing a trigger on tbl1 and once records reach there immediately call
> a User defined function to parse the string and break it into multiple
> columns and push it to Database2.dbo.tbldest

Makes sense. The CROSS APPLY operator will come in handy here.

> Not sure how to push data immediately from tbldest to 'n' number of
> smaller tables based on the column division?

Nor am I, since I don't know about these tables. :-)

But I guess this should be done a trigger as well:

INSERT one (...)
SELECT ...
FROM inserted
WHERE division = 'one'

Here I assume that you have the trigger on tbldest, but as Eric suggested
you may need not need that table, but all in an INSTEAD OF trigger
on tbl1. (But personally I would store the data there for debug reasons.)
--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: SqlBeginner on 8 Jul 2010 19:25
Thanks for response Eric & Erland.

This is the function i use to split the strings. This is what i call within
my AFTER INSERT trigger.

CREATE FUNCTION [dbo].[fnStringmanipulation]
(
@Source varchar(2048),
@Begin varchar(512),
@End varchar(512),
@SIndex int = 1,
@SBegin char(1) = 'N'
)
RETURNS varchar(2048)
AS
BEGIN
DECLARE @Result varchar(2048), @BIndex int, @EIndex int
SET @BIndex = CHARINDEX(@Begin, @Source, @SIndex)

IF @BIndex = 0
RETURN NULL
ELSE
SET @BIndex = @BIndex + LEN(@Begin)
SET @EIndex = CHARINDEX(@End, @Source, @BIndex)

IF @EIndex = 0
BEGIN
IF @SBegin = 'Y'
BEGIN
RETURN NULL
END
ELSE
BEGIN
SET @Result = SUBSTRING(@Source, @BIndex, 2048)
END
END
ELSE
BEGIN
SET @Result = SUBSTRING(@Source, @BIndex, @EIndex - @BIndex)
END
RETURN @Result
END

Extract from the trigger

INSERT INTO dbo.tbldest
(
...
)
SELECT
....
dbo.fnStringmanipulation(strkeyValue, 'S:','|', 1, 1) AS [strkeyValue],
....
FROM INSERTED

Please let me know if something can be improved in the code.

Erland, are you saying i can have multiple Insert stmts for each division
within one trigger on tbldest?

Overall by using trigger is it going have any hit on performance of the
server and is there anything which i need to take care while implementing it.

Regards
Pradeep

"Erland Sommarskog" wrote:

> SqlBeginner (SqlBeginner(a)discussions.microsoft.com) writes:
> > Writing a trigger on tbl1 and once records reach there immediately call
> > a User defined function to parse the string and break it into multiple
> > columns and push it to Database2.dbo.tbldest
>
> Makes sense. The CROSS APPLY operator will come in handy here.
>
> > Not sure how to push data immediately from tbldest to 'n' number of
> > smaller tables based on the column division?
>
> Nor am I, since I don't know about these tables. :-)
>
> But I guess this should be done a trigger as well:
>
> INSERT one (...)
> SELECT ...
> FROM inserted
> WHERE division = 'one'
>
> Here I assume that you have the trigger on tbldest, but as Eric suggested
> you may need not need that table, but all in an INSTEAD OF trigger
> on tbl1. (But personally I would store the data there for debug reasons.)
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
From: Eric Isaacs on 8 Jul 2010 20:31
I think that the use of the string parsing function is good, but
depending on which version of SQL Server you're using, since this
isn't touching any tables, you'll get better performance (at least in
SQL Server 2005) if you use WITH SCHEMABINDING in your function
declaration.

> CREATE FUNCTION [dbo].[fnStringmanipulation]
> (
> @Source varchar(2048),
> @Begin varchar(512),
> @End varchar(512),
> @SIndex int = 1,
> @SBegin char(1) = 'N'
> )
> RETURNS varchar(2048)
WITH SCHEMABINDING ---ADD THIS LINE
> AS
> BEGIN
....

Yes, you can have multiple insert statements in a trigger. I was
saying that you could technically do this in an instead of trigger
without actually inserting any data in any tables except for the
destination tables using an INSTEAD OF INSERT trigger. If you just
use an insert trigger, you can still execute other insert statements
besides what is going into the table.

I think having the data go into the tblDest table is a sound idea for
debugging and testing. Since there are 50K rows per day of data
though, doing it in an instead of trigger makes sense, because if
you're not testing, you really don't need those rows to go into either
of those tables.

Assuming that you know all the divisions and where the data will be
going, you can do multiple inserts in the trigger to each one of the
division tables. If you add a division, you'll need to change your
trigger, however. You might be able to work around that issue with
dynamic sql in the trigger, but that gets even more messy, but still
technically feasible.

-Eric Isaacs





 |  Next  |  Last
Pages: 1 2
Prev: setting text result set more then 8192
Next: Best way to store vectors?