Optimizing Lookups on a trigger's INSERTED virtual table

Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution time
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!
Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
David Portas
SQL Server MVP
|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:

> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-based
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger because
> multiple procedural statements in triggers will create a bottleneck. For the
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>

Optimizing Lookups on a trigger's INSERTED virtual table

Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution time
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
--
David Portas
SQL Server MVP
--|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:
> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-based
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger because
> multiple procedural statements in triggers will create a bottleneck. For the
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>sql

Optimizing Lookups on a trigger's INSERTED virtual table

Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution tim
e
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
David Portas
SQL Server MVP
--|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:

> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-base
d
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger becaus
e
> multiple procedural statements in triggers will create a bottleneck. For t
he
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues
or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>

OPENXML Question

How do I return the number of rows inserted/updated using OPENXML?
I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
Generic update example trying to return the # of rows updated:
declare @.i int
exec sp_xml_preparedocument @.i output,
'<mydata>
<test xmlID="3" xmlData="blah blah blah"/>
<test xmlID="1" xmlData="blah"/>
</mydata>'
update test
set test.xmlData = ox.xmlData
from OpenXml(@.i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) ox
where test.xmlID = ox.xmlID
RETURN @.@.ROWCOUNT --Returns a 0
exec sp_xml_removedocument @.i
Thanks,It will return the rowcount.
Can you check if the data was really updated.
I think its the data problem.
Or try using print @.@.rowcount as see.
"Robert" wrote:

> How do I return the number of rows inserted/updated using OPENXML?
> I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
> Generic update example trying to return the # of rows updated:
> declare @.i int
> exec sp_xml_preparedocument @.i output,
> '<mydata>
> <test xmlID="3" xmlData="blah blah blah"/>
> <test xmlID="1" xmlData="blah"/>
> </mydata>'
> update test
> set test.xmlData = ox.xmlData
> from OpenXml(@.i, 'mydata/test')
> with (xmlID int, xmlData nvarchar(30)) ox
> where test.xmlID = ox.xmlID
> RETURN @.@.ROWCOUNT --Returns a 0
> exec sp_xml_removedocument @.i
>
> Thanks,
>|||Check my procedure here:
It works for me (rowcount stuff that is)
if exists (select * from sysobjects
where id = object_id('uspTitleUpdate') and sysstat & 0xf = 4)
drop procedure uspTitleUpdate
GO
CREATE PROCEDURE dbo.uspTitleUpdate (
@.xml_doc TEXT ,
@.numberRowsAffected int output --return
)
AS
SET NOCOUNT ON
DECLARE @.hdoc INT -- handle to XML doc
DECLARE @.errorTracker int -- used to "remember" the @.@.ERROR
DECLARE @.updateRowCount int
DECLARE @.insertRowCount int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XML_Doc
-- build a table (variable table) to store the xml-based result set
DECLARE @.titleupdate TABLE (
identityid int IDENTITY (1,1) ,
title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate datetime ,
--used to differeniate between existing (update) and new ones (insert)
alreadyExists bit DEFAULT 0
)
--the next call will take the info IN the @.hdoc(with is the holder for
@.xml_doc), and put it IN a variableTable
INSERT @.titleupdate
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate ,
alreadyExists
)
SELECT
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
dbo.udf_convert_xml_date_to_datetime (pubdate) ,
0
FROM
-- use the correct XPath .. the second arg ("2" here) distinquishes
-- between textnode or an attribute, most times with
--.NET typed datasets, its a "2"
--This xpath MUST match the syntax of the DataSet
OPENXML (@.hdoc, '/TitlesDS/Titles', 2) WITH (
title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate varchar(32) ,
alreadyExists bit
)
--select * from @.titleupdate
--lets differeniate between existing (update) and new ones (insert)
Update @.titleupdate
SET
alreadyExists = 1
FROM
@.titleupdate tu , titles
WHERE
--this where clause is a little weird, usually you'll must match
--primary key (int or global identifiers)
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
SET NOCOUNT OFF
Update
titles
set
title = tu.title ,
type = tu.type ,
pub_id = tu.pub_id ,
price = tu.price ,
advance = tu.advance ,
royalty = tu.royalty ,
ytd_sales = tu.ytd_sales ,
notes = tu.notes ,
pubdate = tu.pubdate
FROM
@.titleupdate tu , titles
WHERE
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
AND
tu.alreadyExists <> 0
Select @.updateRowCount = @.@.ROWCOUNT
INSERT INTO titles
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
)
Select
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
FROM
@.titleupdate
WHERE
alreadyExists = 0
Select @.insertRowCount = @.@.ROWCOUNT
select @.numberRowsAffected = @.insertRowCount + @.updateRowCount
--select * from titles
SET NOCOUNT OFF
GO
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:3C2C4124-DEE9-4A0E-82CE-FE91CCFDC5AF@.microsoft.com...
> How do I return the number of rows inserted/updated using OPENXML?
> I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
> Generic update example trying to return the # of rows updated:
> declare @.i int
> exec sp_xml_preparedocument @.i output,
> '<mydata>
> <test xmlID="3" xmlData="blah blah blah"/>
> <test xmlID="1" xmlData="blah"/>
> </mydata>'
> update test
> set test.xmlData = ox.xmlData
> from OpenXml(@.i, 'mydata/test')
> with (xmlID int, xmlData nvarchar(30)) ox
> where test.xmlID = ox.xmlID
> RETURN @.@.ROWCOUNT --Returns a 0
> exec sp_xml_removedocument @.i
>
> Thanks,
>