Trigger performance and temp tables  
Author Message
Wahou





PostPosted: Tue Nov 14 21:15:01 CST 2006 Top

SQL Server Developer >> Trigger performance and temp tables

This is a multi-part message in MIME format.

------=_NextPart_000_00A6_01C707DF.B9739650
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi.
thanks for your time you took to look at my question and any advice.

SQL 2005 server.

I have an update trigger that has certain logic built in it. It does 6 =
joins like this to detect and act on status change (sttID). Normally, =
it's one row effected but less often it can be few dozens, rarely in =
100s
FROM INSERTED i

JOIN DELETED d ON d.olnID =3D i.olnID=20

WHERE d.sttID < i.sttID or d.sttID > i.sttID

I know that it all depends (lots of things including testing), but in =
general, would it be more beneficial to create temp table/variable to =
log the key (olnID) and sttID change at the begining and use it then in =
JOINs below or just have the above JOIN repeated?

Concerns I have are:=20

1. recompiles as any temp table declaration or data change will cause =
these.

2. temp table would be scanned anyway and no index is really useful here =
as I do no where searches. Considering that SQL 2005 implements INSERTED =
and DELETED in temp db anyway, it makes no difference if SQL scans =
INSERTED and DELETED versus my own temp table. So 6 scans of INSERTED =
and DELETED versus 6 scans of #MyAcmeTemp table

What do you think, guys and girls? Should I consider #temp tables and =
will these likely help or hurt?



I also use these optimizations to reduce costs.



RETURN

SET NOCOUNT ON

IF UPDATE ( sttID )=20





------=_NextPart_000_00A6_01C707DF.B9739650
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.5730.11" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3D"Courier New" size=3D2>Hi.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>thanks&nbsp;for your time you =
took to look=20
at my question and any advice.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>SQL 2005 server.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>I have&nbsp;an =
update&nbsp;trigger that has=20
certain logic built in it. It does 6 joins like this to detect and act =
on status=20
change (sttID).&nbsp;Normally, it's one row&nbsp;effected but&nbsp;less=20
often&nbsp;it can be&nbsp;few dozens, rarely&nbsp;in 100s</FONT></DIV>
<DIV>
<P><FONT size=3D2><FONT face=3D"Courier New" =
color=3D#0000ff>FROM</FONT><FONT=20
face=3D"Courier New"> INSERTED i</FONT></FONT></P>
<P><FONT size=3D2><FONT face=3D"Courier New"><FONT =
color=3D#808080>JOIN</FONT> DELETED=20
d <FONT color=3D#0000ff>ON</FONT> d<FONT color=3D#808080>.</FONT>olnID =
<FONT=20
color=3D#808080>=3D</FONT> i<FONT color=3D#808080>.</FONT></FONT><FONT=20
face=3D"Courier New">olnID </FONT></FONT></P>
<P><FONT face=3D"Courier New"><FONT size=3D2><FONT =
color=3D#0000ff>WHERE</FONT> d<FONT=20
color=3D#808080>.</FONT>sttID <FONT color=3D#808080>&lt;</FONT> i<FONT=20
color=3D#808080>.</FONT>sttID <FONT color=3D#808080>or</FONT> d<FONT=20
color=3D#808080>.</FONT>sttID <FONT color=3D#808080>&gt;</FONT> i<FONT=20
color=3D#808080>.</FONT>sttID</FONT></FONT></P>
<P><FONT face=3D"Courier New" size=3D2>I know that it all depends (lots =
of things=20
including testing), but in general, would it be more beneficial to =
create temp=20
table/variable to log the key (olnID) and sttID change at the begining =
and use=20
it then in JOINs below or just have the above JOIN repeated?</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>Concerns I have&nbsp;are: =
</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>1. recompiles as any temp table=20
declaration&nbsp;or data change will cause these.</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>2. temp table would be scanned =
anyway and no=20
index is really useful here as I do no where searches. Considering that =
SQL 2005=20
implements INSERTED and DELETED in temp db anyway, it makes no =
difference if SQL=20
scans INSERTED and DELETED versus my own temp table. </FONT><FONT=20
face=3D"Courier New" size=3D2>So 6 scans of INSERTED and DELETED versus =
6 scans of=20
#MyAcmeTemp table</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>What do you think, guys and =
girls? Should I=20
consider #temp tables and will these likely help or hurt?</FONT></P>
<P><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</P>
<P><FONT face=3D"Courier New" size=3D2>I also use these optimizations to =
reduce=20
costs.</FONT></P><FONT size=3D2><FONT color=3D#0000ff size=3D2>
<P><FONT face=3D"Courier New">IF</FONT></FONT><FONT face=3D"Courier =
New"><FONT=20

size=3D2>=20
</FONT><FONT color=3D#808080 size=3D2>=3D</FONT><FONT size=3D2> 0 =
</FONT><FONT=20
color=3D#008000 size=3D2>-- If no rows, quit</P></FONT></FONT><FONT =
size=3D2>
<P></FONT><FONT color=3D#0000ff size=3D2><FONT face=3D"Courier =
New">RETURN</FONT></P>
<P><FONT face=3D"Courier New">SET</FONT></FONT><FONT face=3D"Courier =
New"><FONT=20
size=3D2> </FONT><FONT color=3D#0000ff size=3D2>NOCOUNT</FONT><FONT =
size=3D2>=20
</FONT></FONT><FONT color=3D#0000ff size=3D2><FONT face=3D"Courier =
New">ON</FONT></P>
<P><FONT face=3D"Courier New">IF</FONT></FONT><FONT face=3D"Courier =
New"><FONT=20
size=3D2> </FONT><FONT color=3D#0000ff size=3D2>UPDATE</FONT><FONT =
size=3D2>=20
</FONT><FONT color=3D#808080 size=3D2>(</FONT><FONT size=3D2> sttID =
</FONT><FONT=20
color=3D#808080 size=3D2>)</FONT><FONT size=3D2> </FONT></FONT></P>
<P><FONT size=3D2><FONT face=3D"Courier New" =
color=3D#808080></FONT></FONT>&nbsp;</P>
<P><FONT size=3D2><FONT=20
color=3D#808080></FONT>&nbsp;</P></FONT></FONT></DIV></BODY></HTML>

------=_NextPart_000_00A6_01C707DF.B9739650--

SQL Server211  
 
 
Tom





PostPosted: Tue Nov 14 21:15:01 CST 2006 Top

SQL Server Developer >> Trigger performance and temp tables This is a multi-part message in MIME format.

------=_NextPart_000_0089_01C7083A.5573B440
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

All of the following is IMHO.

I would not use a temp table in this case. It might be slightly more =
efficient, but it probably won't be much. After all, you hove only one =
row in the inserted/deleted tables in most cases, so the match will be =
very fast. Even in those cases where you have a few 100 rows, this =
(matching rows on a JOIN) is the kind of process that SQL Server is very =
efficient at, so it won't be terribly expensive.

If you had some expensive process that the trigger was doing 6 times, =
then it might pay to build a temp table and do the process only once. =
But in this case I think the cost of creating the temporary table plus =
the additional programming/maintaince cost caused by the increase in =
complexity of the trigger would outweigh anything you will get from the =
temp table.

BTW, if sttID does not allow nulls, then your WHERE condition is fine =
(although I would write it WHERE d.sttID <> i.sttID), but if sttID can =
be null then your WHERE condition doesn't work since if the value is =
changed from null to something or from something to null, your WHERE =
condition will be FALSE.

Tom


Hi.
thanks for your time you took to look at my question and any advice.

SQL 2005 server.

I have an update trigger that has certain logic built in it. It does 6 =
joins like this to detect and act on status change (sttID). Normally, =
it's one row effected but less often it can be few dozens, rarely in =
100s
FROM INSERTED i

JOIN DELETED d ON d.olnID =3D i.olnID=20

WHERE d.sttID < i.sttID or d.sttID > i.sttID

I know that it all depends (lots of things including testing), but in =
general, would it be more beneficial to create temp table/variable to =
log the key (olnID) and sttID change at the begining and use it then in =
JOINs below or just have the above JOIN repeated?

Concerns I have are:=20

1. recompiles as any temp table declaration or data change will cause =
these.

2. temp table would be scanned anyway and no index is really useful =
here as I do no where searches. Considering that SQL 2005 implements =
INSERTED and DELETED in temp db anyway, it makes no difference if SQL =
scans INSERTED and DELETED versus my own temp table. So 6 scans of =
INSERTED and DELETED versus 6 scans of #MyAcmeTemp table

What do you think, guys and girls? Should I consider #temp tables and =
will these likely help or hurt?



I also use these optimizations to reduce costs.



RETURN

SET NOCOUNT ON

IF UPDATE ( sttID )=20





------=_NextPart_000_0089_01C7083A.5573B440
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.5730.11" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>All of the following&nbsp;is =
IMHO.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I would not use a temp table in this =
case.&nbsp; It=20
might be slightly more efficient, but it probably won't be much.&nbsp; =
After=20
all, you hove only one row in the inserted/deleted tables in most cases, =
so the=20
match will be very fast.&nbsp; Even in those cases where you have a few =
100=20
rows, this (matching rows on a JOIN) is the kind of process that SQL =
Server is=20
very efficient at, so it won't be terribly expensive.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>If you had some expensive process that =
the trigger=20
was doing 6 times, then it might pay to build a temp table and do the =
process=20
only once.&nbsp; But in this case I think the cost of creating the =
temporary=20
table plus the additional programming/maintaince cost caused by the =
increase in=20
complexity of the trigger would outweigh anything you will get from the =
temp=20
table.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>BTW, if sttID does not allow nulls, =
then your WHERE=20
condition is fine (although I would write it WHERE d.sttID &lt;&gt; =
i.sttID),=20
but if sttID can be null then your WHERE condition doesn't work since if =
the=20
value is changed from null to something or from something to null, your =
WHERE=20
condition will be FALSE.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Tom</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Farmer" &lt;<A=20

wrote in=20
message <A=20
=

@TK2MSFTNGP03.phx.gbl</A>...</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Hi.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>thanks&nbsp;for your time you =
took to=20
look at my question and any advice.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>SQL 2005 server.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>I have&nbsp;an =
update&nbsp;trigger that=20
has certain logic built in it. It does 6 joins like this to detect and =
act on=20
status change (sttID).&nbsp;Normally, it's one row&nbsp;effected =
but&nbsp;less=20
often&nbsp;it can be&nbsp;few dozens, rarely&nbsp;in 100s</FONT></DIV>
<DIV>
<P><FONT size=3D2><FONT face=3D"Courier New" =
color=3D#0000ff>FROM</FONT><FONT=20
face=3D"Courier New"> INSERTED i</FONT></FONT></P>
<P><FONT size=3D2><FONT face=3D"Courier New"><FONT =
color=3D#808080>JOIN</FONT>=20
DELETED d <FONT color=3D#0000ff>ON</FONT> d<FONT =
color=3D#808080>.</FONT>olnID=20
<FONT color=3D#808080>=3D</FONT> i<FONT =
color=3D#808080>.</FONT></FONT><FONT=20
face=3D"Courier New">olnID </FONT></FONT></P>
<P><FONT face=3D"Courier New"><FONT size=3D2><FONT =
color=3D#0000ff>WHERE</FONT>=20
d<FONT color=3D#808080>.</FONT>sttID <FONT color=3D#808080>&lt;</FONT> =
i<FONT=20
color=3D#808080>.</FONT>sttID <FONT color=3D#808080>or</FONT> d<FONT=20
color=3D#808080>.</FONT>sttID <FONT color=3D#808080>&gt;</FONT> i<FONT =

color=3D#808080>.</FONT>sttID</FONT></FONT></P>
<P><FONT face=3D"Courier New" size=3D2>I know that it all depends =
(lots of things=20
including testing), but in general, would it be more beneficial to =
create temp=20
table/variable to log the key (olnID) and sttID change at the begining =
and use=20
it then in JOINs below or just have the above JOIN =
repeated?</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>Concerns I have&nbsp;are: =
</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>1. recompiles as any temp table =

declaration&nbsp;or data change will cause these.</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>2. temp table would be scanned =
anyway and=20
no index is really useful here as I do no where searches. Considering =
that SQL=20
2005 implements INSERTED and DELETED in temp db anyway, it makes no =
difference=20
if SQL scans INSERTED and DELETED versus my own temp table. =
</FONT><FONT=20
face=3D"Courier New" size=3D2>So 6 scans of INSERTED and DELETED =
versus 6 scans of=20
#MyAcmeTemp table</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>What do you think, guys and =
girls? Should I=20
consider #temp tables and will these likely help or hurt?</FONT></P>
<P><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</P>
<P><FONT face=3D"Courier New" size=3D2>I also use these optimizations =
to reduce=20
costs.</FONT></P><FONT size=3D2><FONT color=3D#0000ff size=3D2>
<P><FONT face=3D"Courier New">IF</FONT></FONT><FONT face=3D"Courier =
New"><FONT=20

size=3D2>=20
</FONT><FONT color=3D#808080 size=3D2>=3D</FONT><FONT size=3D2> 0 =
</FONT><FONT=20
color=3D#008000 size=3D2>-- If no rows, quit</P></FONT></FONT><FONT =
size=3D2>
<P></FONT><FONT color=3D#0000ff size=3D2><FONT=20
face=3D"Courier New">RETURN</FONT></P>
<P><FONT face=3D"Courier New">SET</FONT></FONT><FONT face=3D"Courier =
New"><FONT=20
size=3D2> </FONT><FONT color=3D#0000ff size=3D2>NOCOUNT</FONT><FONT =
size=3D2>=20
</FONT></FONT><FONT color=3D#0000ff size=3D2><FONT=20
face=3D"Courier New">ON</FONT></P>
<P><FONT face=3D"Courier New">IF</FONT></FONT><FONT face=3D"Courier =
New"><FONT=20
size=3D2> </FONT><FONT color=3D#0000ff size=3D2>UPDATE</FONT><FONT =
size=3D2>=20
</FONT><FONT color=3D#808080 size=3D2>(</FONT><FONT size=3D2> sttID =
</FONT><FONT=20
color=3D#808080 size=3D2>)</FONT><FONT size=3D2> </FONT></FONT></P>
<P><FONT size=3D2><FONT face=3D"Courier New"=20
color=3D#808080></FONT></FONT>&nbsp;</P>
<P><FONT size=3D2><FONT=20
color=3D#808080></FONT>&nbsp;</P></FONT></FONT></DIV></BLOCKQUOTE></BODY>=
</HTML>

------=_NextPart_000_0089_01C7083A.5573B440--

 
 
Farmer





PostPosted: Tue Jan 23 13:52:18 CST 2007 Top

SQL Server Developer >> Trigger performance and temp tables This is a multi-part message in MIME format.

------=_NextPart_000_0009_01C73EFE.147A1970
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Thank you, Tom.=20
I decided not to use temp table for similar reasons.

sttId cannot be NULL so I am OK.=20
d.sttID < i.sttID or d.sttID > i.sttID is SARGable versus d.sttID <> =
i.sttID, which is non-SARGable.
Even if it goes not make any difference, I am in habbit of using =
SARGable arguments.





All of the following is IMHO.

I would not use a temp table in this case. It might be slightly more =
efficient, but it probably won't be much. After all, you hove only one =
row in the inserted/deleted tables in most cases, so the match will be =
very fast. Even in those cases where you have a few 100 rows, this =
(matching rows on a JOIN) is the kind of process that SQL Server is very =
efficient at, so it won't be terribly expensive.

If you had some expensive process that the trigger was doing 6 times, =
then it might pay to build a temp table and do the process only once. =
But in this case I think the cost of creating the temporary table plus =
the additional programming/maintaince cost caused by the increase in =
complexity of the trigger would outweigh anything you will get from the =
temp table.

BTW, if sttID does not allow nulls, then your WHERE condition is fine =
(although I would write it WHERE d.sttID <> i.sttID), but if sttID can =
be null then your WHERE condition doesn't work since if the value is =
changed from null to something or from something to null, your WHERE =
condition will be FALSE.

Tom


Hi.
thanks for your time you took to look at my question and any advice.

SQL 2005 server.

I have an update trigger that has certain logic built in it. It does =
6 joins like this to detect and act on status change (sttID). Normally, =
it's one row effected but less often it can be few dozens, rarely in =
100s
FROM INSERTED i

JOIN DELETED d ON d.olnID =3D i.olnID=20

WHERE d.sttID < i.sttID or d.sttID > i.sttID

I know that it all depends (lots of things including testing), but =
in general, would it be more beneficial to create temp table/variable to =
log the key (olnID) and sttID change at the begining and use it then in =
JOINs below or just have the above JOIN repeated?

Concerns I have are:=20

1. recompiles as any temp table declaration or data change will =
cause these.

2. temp table would be scanned anyway and no index is really useful =
here as I do no where searches. Considering that SQL 2005 implements =
INSERTED and DELETED in temp db anyway, it makes no difference if SQL =
scans INSERTED and DELETED versus my own temp table. So 6 scans of =
INSERTED and DELETED versus 6 scans of #MyAcmeTemp table

What do you think, guys and girls? Should I consider #temp tables =
and will these likely help or hurt?



I also use these optimizations to reduce costs.



RETURN

SET NOCOUNT ON

IF UPDATE ( sttID )=20





------=_NextPart_000_0009_01C73EFE.147A1970
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.5730.11" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3D"Courier New" size=3D2>Thank you, Tom. </FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>I decided not to use temp table =
for similar=20
reasons.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>sttId cannot be NULL so I am =
OK.=20
</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>d<FONT =
color=3D#808080>.</FONT>sttID <FONT=20
color=3D#808080>&lt;</FONT> i<FONT color=3D#808080>.</FONT>sttID <FONT=20
color=3D#808080>or</FONT> d<FONT color=3D#808080>.</FONT>sttID <FONT=20
color=3D#808080>&gt;</FONT> i<FONT color=3D#808080>.</FONT>sttID is =
SARGable versus=20
d.sttID &lt;&gt; i.sttID, which is non-SARGable.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Even if it goes not make any =
difference, I=20
am in habbit of using SARGable arguments.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Tom Cooper" &lt;<A=20
=

.spam.please.net</A>&gt;=20

=


<DIV><FONT face=3DArial size=3D2>All of the following&nbsp;is =
IMHO.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I would not use a temp table in this =
case.&nbsp;=20
It might be slightly more efficient, but it probably won't be =
much.&nbsp;=20
After all, you hove only one row in the inserted/deleted tables in =
most cases,=20
so the match will be very fast.&nbsp; Even in those cases where you =
have a few=20
100 rows, this (matching rows on a JOIN) is the kind of process that =
SQL=20
Server is very efficient at, so it won't be terribly =
expensive.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>If you had some expensive process =
that the=20
trigger was doing 6 times, then it might pay to build a temp table and =
do the=20
process only once.&nbsp; But in this case I think the cost of creating =
the=20
temporary table plus the additional programming/maintaince cost caused =
by the=20
increase in complexity of the trigger would outweigh anything you will =
get=20
from the temp table.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>BTW, if sttID does not allow nulls, =
then your=20
WHERE condition is fine (although I would write it WHERE d.sttID =
&lt;&gt;=20
i.sttID), but if sttID can be null then your WHERE condition doesn't =
work=20
since if the value is changed from null to something or from something =
to=20
null, your WHERE condition will be FALSE.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Tom</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Farmer" &lt;<A=20

wrote in=20
message <A=20
=

@TK2MSFTNGP03.phx.gbl</A>...</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Hi.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>thanks&nbsp;for your time =
you took to=20
look at my question and any advice.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>SQL 2005 =
server.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>I have&nbsp;an =
update&nbsp;trigger that=20
has certain logic built in it. It does 6 joins like this to detect =
and act=20
on status change (sttID).&nbsp;Normally, it's one row&nbsp;effected=20
but&nbsp;less often&nbsp;it can be&nbsp;few dozens, rarely&nbsp;in=20
100s</FONT></DIV>
<DIV>
<P><FONT size=3D2><FONT face=3D"Courier New" =
color=3D#0000ff>FROM</FONT><FONT=20
face=3D"Courier New"> INSERTED i</FONT></FONT></P>
<P><FONT size=3D2><FONT face=3D"Courier New"><FONT =
color=3D#808080>JOIN</FONT>=20
DELETED d <FONT color=3D#0000ff>ON</FONT> d<FONT =
color=3D#808080>.</FONT>olnID=20
<FONT color=3D#808080>=3D</FONT> i<FONT =
color=3D#808080>.</FONT></FONT><FONT=20
face=3D"Courier New">olnID </FONT></FONT></P>
<P><FONT face=3D"Courier New"><FONT size=3D2><FONT =
color=3D#0000ff>WHERE</FONT>=20
d<FONT color=3D#808080>.</FONT>sttID <FONT =
color=3D#808080>&lt;</FONT> i<FONT=20
color=3D#808080>.</FONT>sttID <FONT color=3D#808080>or</FONT> d<FONT =

color=3D#808080>.</FONT>sttID <FONT color=3D#808080>&gt;</FONT> =
i<FONT=20
color=3D#808080>.</FONT>sttID</FONT></FONT></P>
<P><FONT face=3D"Courier New" size=3D2>I know that it all depends =
(lots of=20
things including testing), but in general, would it be more =
beneficial to=20
create temp table/variable to log the key (olnID) and sttID change =
at the=20
begining and use it then in JOINs below or just have the above JOIN=20
repeated?</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>Concerns I have&nbsp;are: =
</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>1. recompiles as any temp =
table=20
declaration&nbsp;or data change will cause these.</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>2. temp table would be =
scanned anyway and=20
no index is really useful here as I do no where searches. =
Considering that=20
SQL 2005 implements INSERTED and DELETED in temp db anyway, it makes =
no=20
difference if SQL scans INSERTED and DELETED versus my own temp =
table.=20
</FONT><FONT face=3D"Courier New" size=3D2>So 6 scans of INSERTED =
and DELETED=20
versus 6 scans of #MyAcmeTemp table</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>What do you think, guys and =
girls? Should=20
I consider #temp tables and will these likely help or =
hurt?</FONT></P>
<P><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</P>
<P><FONT face=3D"Courier New" size=3D2>I also use these =
optimizations to reduce=20
costs.</FONT></P><FONT size=3D2><FONT color=3D#0000ff size=3D2>
<P><FONT face=3D"Courier New">IF</FONT></FONT><FONT face=3D"Courier =
New"><FONT=20
size=3D2> </FONT><FONT color=3D#ff00ff =

</FONT><FONT color=3D#808080 size=3D2>=3D</FONT><FONT size=3D2> 0 =
</FONT><FONT=20
color=3D#008000 size=3D2>-- If no rows, quit</P></FONT></FONT><FONT =
size=3D2>
<P></FONT><FONT color=3D#0000ff size=3D2><FONT=20
face=3D"Courier New">RETURN</FONT></P>
<P><FONT face=3D"Courier New">SET</FONT></FONT><FONT face=3D"Courier =
New"><FONT=20
size=3D2> </FONT><FONT color=3D#0000ff size=3D2>NOCOUNT</FONT><FONT =
size=3D2>=20
</FONT></FONT><FONT color=3D#0000ff size=3D2><FONT=20
face=3D"Courier New">ON</FONT></P>
<P><FONT face=3D"Courier New">IF</FONT></FONT><FONT face=3D"Courier =
New"><FONT=20
size=3D2> </FONT><FONT color=3D#0000ff size=3D2>UPDATE</FONT><FONT =
size=3D2>=20
</FONT><FONT color=3D#808080 size=3D2>(</FONT><FONT size=3D2> sttID =
</FONT><FONT=20
color=3D#808080 size=3D2>)</FONT><FONT size=3D2> </FONT></FONT></P>
<P><FONT size=3D2><FONT face=3D"Courier New"=20
color=3D#808080></FONT></FONT>&nbsp;</P>
<P><FONT size=3D2><FONT=20
=
color=3D#808080></FONT>&nbsp;</P></FONT></FONT></DIV></BLOCKQUOTE></BLOCK=
QUOTE></BODY></HTML>

------=_NextPart_000_0009_01C73EFE.147A1970--

 
 
Tom





PostPosted: Tue Jan 23 22:10:58 CST 2007 Top

SQL Server Developer >> Trigger performance and temp tables This is a multi-part message in MIME format.

------=_NextPart_000_0167_01C73F43.BE646E00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Actually, both methods are SARGable. But you are right to always use =
SARGable queries whenever possible.

Tom=20


Thank you, Tom.=20
I decided not to use temp table for similar reasons.

sttId cannot be NULL so I am OK.=20
d.sttID < i.sttID or d.sttID > i.sttID is SARGable versus d.sttID <> =
i.sttID, which is non-SARGable.
Even if it goes not make any difference, I am in habbit of using =
SARGable arguments.





All of the following is IMHO.

I would not use a temp table in this case. It might be slightly =
more efficient, but it probably won't be much. After all, you hove only =
one row in the inserted/deleted tables in most cases, so the match will =
be very fast. Even in those cases where you have a few 100 rows, this =
(matching rows on a JOIN) is the kind of process that SQL Server is very =
efficient at, so it won't be terribly expensive.

If you had some expensive process that the trigger was doing 6 =
times, then it might pay to build a temp table and do the process only =
once. But in this case I think the cost of creating the temporary table =
plus the additional programming/maintaince cost caused by the increase =
in complexity of the trigger would outweigh anything you will get from =
the temp table.

BTW, if sttID does not allow nulls, then your WHERE condition is =
fine (although I would write it WHERE d.sttID <> i.sttID), but if sttID =
can be null then your WHERE condition doesn't work since if the value is =
changed from null to something or from something to null, your WHERE =
condition will be FALSE.

Tom


Hi.
thanks for your time you took to look at my question and any =
advice.

SQL 2005 server.

I have an update trigger that has certain logic built in it. It =
does 6 joins like this to detect and act on status change (sttID). =
Normally, it's one row effected but less often it can be few dozens, =
rarely in 100s
FROM INSERTED i

JOIN DELETED d ON d.olnID =3D i.olnID=20

WHERE d.sttID < i.sttID or d.sttID > i.sttID

I know that it all depends (lots of things including testing), but =
in general, would it be more beneficial to create temp table/variable to =
log the key (olnID) and sttID change at the begining and use it then in =
JOINs below or just have the above JOIN repeated?

Concerns I have are:=20

1. recompiles as any temp table declaration or data change will =
cause these.

2. temp table would be scanned anyway and no index is really =
useful here as I do no where searches. Considering that SQL 2005 =
implements INSERTED and DELETED in temp db anyway, it makes no =
difference if SQL scans INSERTED and DELETED versus my own temp table. =
So 6 scans of INSERTED and DELETED versus 6 scans of #MyAcmeTemp table

What do you think, guys and girls? Should I consider #temp tables =
and will these likely help or hurt?



I also use these optimizations to reduce costs.



RETURN

SET NOCOUNT ON

IF UPDATE ( sttID )=20





------=_NextPart_000_0167_01C73F43.BE646E00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.5730.11" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Actually, both&nbsp;methods&nbsp;are=20
SARGable.&nbsp; But you are right to always use SARGable&nbsp;queries =
whenever=20
possible.</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Tom</FONT>&nbsp;</DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Farmer" &lt;<A=20

wrote in=20
message <A=20
=


<DIV><FONT face=3D"Courier New" size=3D2>Thank you, Tom. </FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>I decided not to use temp =
table for=20
similar reasons.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>sttId cannot be NULL so I am =
OK.=20
</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>d<FONT =
color=3D#808080>.</FONT>sttID <FONT=20
color=3D#808080>&lt;</FONT> i<FONT color=3D#808080>.</FONT>sttID <FONT =

color=3D#808080>or</FONT> d<FONT color=3D#808080>.</FONT>sttID <FONT=20
color=3D#808080>&gt;</FONT> i<FONT color=3D#808080>.</FONT>sttID is =
SARGable=20
versus d.sttID &lt;&gt; i.sttID, which is non-SARGable.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Even if it goes not make any =
difference,=20
I am in habbit of using SARGable arguments.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Tom Cooper" &lt;<A=20
=

.spam.please.net</A>&gt;=20

=


<DIV><FONT face=3DArial size=3D2>All of the following&nbsp;is =
IMHO.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I would not use a temp table in =
this=20
case.&nbsp; It might be slightly more efficient, but it probably =
won't be=20
much.&nbsp; After all, you hove only one row in the inserted/deleted =
tables=20
in most cases, so the match will be very fast.&nbsp; Even in those =
cases=20
where you have a few 100 rows, this (matching rows on a JOIN) is the =
kind of=20
process that SQL Server is very efficient at, so it won't be =
terribly=20
expensive.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>If you had some expensive process =
that the=20
trigger was doing 6 times, then it might pay to build a temp table =
and do=20
the process only once.&nbsp; But in this case I think the cost of =
creating=20
the temporary table plus the additional programming/maintaince cost =
caused=20
by the increase in complexity of the trigger would outweigh anything =
you=20
will get from the temp table.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>BTW, if sttID does not allow nulls, =
then your=20
WHERE condition is fine (although I would write it WHERE d.sttID =
&lt;&gt;=20
i.sttID), but if sttID can be null then your WHERE condition doesn't =
work=20
since if the value is changed from null to something or from =
something to=20
null, your WHERE condition will be FALSE.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Tom</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Farmer" &lt;<A=20
=

wrote in=20
message <A=20
=

@TK2MSFTNGP03.phx.gbl</A>...</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Hi.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>thanks&nbsp;for your time =
you took to=20
look at my question and any advice.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>SQL 2005 =
server.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>I have&nbsp;an =
update&nbsp;trigger=20
that has certain logic built in it. It does 6 joins like this to =
detect=20
and act on status change (sttID).&nbsp;Normally, it's one=20
row&nbsp;effected but&nbsp;less often&nbsp;it can be&nbsp;few =
dozens,=20
rarely&nbsp;in 100s</FONT></DIV>
<DIV>
<P><FONT size=3D2><FONT face=3D"Courier New" =
color=3D#0000ff>FROM</FONT><FONT=20
face=3D"Courier New"> INSERTED i</FONT></FONT></P>
<P><FONT size=3D2><FONT face=3D"Courier New"><FONT =
color=3D#808080>JOIN</FONT>=20
DELETED d <FONT color=3D#0000ff>ON</FONT> d<FONT =
color=3D#808080>.</FONT>olnID=20
<FONT color=3D#808080>=3D</FONT> i<FONT =
color=3D#808080>.</FONT></FONT><FONT=20
face=3D"Courier New">olnID </FONT></FONT></P>
<P><FONT face=3D"Courier New"><FONT size=3D2><FONT =
color=3D#0000ff>WHERE</FONT>=20
d<FONT color=3D#808080>.</FONT>sttID <FONT =
color=3D#808080>&lt;</FONT> i<FONT=20
color=3D#808080>.</FONT>sttID <FONT color=3D#808080>or</FONT> =
d<FONT=20
color=3D#808080>.</FONT>sttID <FONT color=3D#808080>&gt;</FONT> =
i<FONT=20
color=3D#808080>.</FONT>sttID</FONT></FONT></P>
<P><FONT face=3D"Courier New" size=3D2>I know that it all depends =
(lots of=20
things including testing), but in general, would it be more =
beneficial to=20
create temp table/variable to log the key (olnID) and sttID change =
at the=20
begining and use it then in JOINs below or just have the above =
JOIN=20
repeated?</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>Concerns I have&nbsp;are: =
</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>1. recompiles as any temp =
table=20
declaration&nbsp;or data change will cause these.</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>2. temp table would be =
scanned anyway=20
and no index is really useful here as I do no where searches. =
Considering=20
that SQL 2005 implements INSERTED and DELETED in temp db anyway, =
it makes=20
no difference if SQL scans INSERTED and DELETED versus my own temp =
table.=20
</FONT><FONT face=3D"Courier New" size=3D2>So 6 scans of INSERTED =
and DELETED=20
versus 6 scans of #MyAcmeTemp table</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>What do you think, guys and =
girls?=20
Should I consider #temp tables and will these likely help or=20
hurt?</FONT></P>
<P><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</P>
<P><FONT face=3D"Courier New" size=3D2>I also use these =
optimizations to=20
reduce costs.</FONT></P><FONT size=3D2><FONT color=3D#0000ff =
size=3D2>
<P><FONT face=3D"Courier New">IF</FONT></FONT><FONT =
face=3D"Courier New"><FONT=20
size=3D2> </FONT><FONT color=3D#ff00ff =

</FONT><FONT color=3D#808080 size=3D2>=3D</FONT><FONT size=3D2> 0 =
</FONT><FONT=20
color=3D#008000 size=3D2>-- If no rows, =
quit</P></FONT></FONT><FONT size=3D2>
<P></FONT><FONT color=3D#0000ff size=3D2><FONT=20
face=3D"Courier New">RETURN</FONT></P>
<P><FONT face=3D"Courier New">SET</FONT></FONT><FONT=20
face=3D"Courier New"><FONT size=3D2> </FONT><FONT color=3D#0000ff=20
size=3D2>NOCOUNT</FONT><FONT size=3D2> </FONT></FONT><FONT =
color=3D#0000ff=20
size=3D2><FONT face=3D"Courier New">ON</FONT></P>
<P><FONT face=3D"Courier New">IF</FONT></FONT><FONT =
face=3D"Courier New"><FONT=20
size=3D2> </FONT><FONT color=3D#0000ff size=3D2>UPDATE</FONT><FONT =
size=3D2>=20
</FONT><FONT color=3D#808080 size=3D2>(</FONT><FONT size=3D2> =
sttID </FONT><FONT=20
color=3D#808080 size=3D2>)</FONT><FONT size=3D2> =
</FONT></FONT></P>
<P><FONT size=3D2><FONT face=3D"Courier New"=20
color=3D#808080></FONT></FONT>&nbsp;</P>
<P><FONT size=3D2><FONT=20
=
color=3D#808080></FONT>&nbsp;</P></FONT></FONT></DIV></BLOCKQUOTE></BLOCK=
QUOTE></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_0167_01C73F43.BE646E00--