Question about sproc performance?

whats the difference in performance between these two scenarioes...

Suppose if i build a sproc to insert or update into one table and then take its primary key and insert and update subsequent tables may one or 2 or more take more time in exceuting...

or

writing indivdual sproc for insert and update for particular table in different sproc and then calling all the sprocs in a master proc using exec sproc command...

Which scenario would better if they are around 230 or 300 or more than records.

Hope some would shed some light into this..

Any help is appreciated...

Regards

Karen

The best advise I can give is to build both of them. You can then look at the individual execution plans of them both and determine which is the better approach for your database. It should also help to highlight if any indexes need to be added to your tables.

|||

I dont think it will be too much of a noticeable difference.. unless you have some very complex logic involving several IF loops which radically changes the WHERE conditions in the query.

|||

Thanks Dinakar...

I just plain inserts and updates...to different tables... and in the above scenario which is best approach to take?

Regards

Karen

|||

If its plain INSERTs/UPDATEs its more of a convenience and easier maintenance that you should decide based on rather than performance. I would create one proc for each table that takes care of INSERT and UPDATE (Depending on whether record exists or not) so you can re-use the procs. If you put multiple tables related INSERTs into one proc and later if you had to do an INSERTt/UPDATE into one of those 2 tables, you'd be re-writing the same logic into another proc again. If you created a separate proc for the table now, you could just call the proc later.

question about process order

Hi all,
I have a trigger for update on a table. It does a whole bunch of things,
then calls a SP. I want to then check something afet the SP is completed. To
check this out I put a print statement after the line that called the SP
exec calcdeductables @.claimid, 1
print 'Check SIP'
The SP, by nature update multiple rows in child tables, as A result I would
expect the following:
(1 row(s) affected)
(1 row(s) affected)
(4 row(s) affected)
But with the print statement I am gettingh
(1 row(s) affected)
(1 row(s) affected)
Check SIP
(4 row(s) affected)
Should'nt the Check sip statement come last
Thanks
RobertRobert Bravery (me@.u.com) writes:
> I have a trigger for update on a table. It does a whole bunch of things,
> then calls a SP. I want to then check something afet the SP is
> completed. To check this out I put a print statement after the line that
> called the SP
> exec calcdeductables @.claimid, 1
> print 'Check SIP'
> The SP, by nature update multiple rows in child tables, as A result I
> would expect the following:
> (1 row(s) affected)
> (1 row(s) affected)
> (4 row(s) affected)
> But with the print statement I am gettingh
> (1 row(s) affected)
> (1 row(s) affected)
> Check SIP
> (4 row(s) affected)
> Should'nt the Check sip statement come last
A more interesting question is from where you got that @.claimid. If you
do
SELECT @.claimid = claimid FROM inserted
then you need to rewrite your trigger. A trigger fires once per statement,
and thus "inserted" can include more than more than one row.
If you believe that you know that your table will ever be subject to
single-row inserts and updates, add this to your trigger:
IF @.@.rowcount > 1
BEGIN
RAISERROR('Multi-row operations not supported!, 16, 1)
ROLLBACK TRANSACTION
RETURN
END
If you need to support multi-row operations, you can run a cursor over
"inserted" (and maybe this is what you do already). However, since a
trigger runs in the context of a transaction defined by the statement
that defined it, it is not a good idea to start looping in a trigger, as
this could cause contention issues.
The remedy would be to perform the updates directly the in trigger, in a
set-based fashion rather than running one by one from your procedure. If
you do that your question about output may be become a moot point.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Does the procedure end with that PRINT statement?
ML
http://milambda.blogspot.com/|||HI Erland
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97729620C5F6DYazorman@.127.0.0.1...
> Robert Bravery (me@.u.com) writes:
> A more interesting question is from where you got that @.claimid. If you
> do
> SELECT @.claimid = claimid FROM inserted
> then you need to rewrite your trigger. A trigger fires once per statement,
> and thus "inserted" can include more than more than one row.
> If you believe that you know that your table will ever be subject to
> single-row inserts and updates, add this to your trigger:
Yes I do get @.claimid from the inserted table. But it will alwaus be only
one record that is update. Just the nature of the operation.
Ok so then I need to qualify some things in my mind.
If a update trigger is ona table, and that table will always only have one
row updated at a time, the update trigger would fire, there is now an
inserted table, with the values on the update row in it. Now that trigger
then updates another table.
Do you no get a different inserted table, or is the same inserted table the
one created by the original insert, used.
Thanks for the single-row inserts little guidence you gave there
What I basically whant to do is that the update trigger on claims, updates
another table, which has say 4 rowse to update. Those four rows are related
to claim table. After that related table and its four rows are updates, I
want to perform and agregate check. All this will do is to notify the user
of an above or below threshold.
Thanks
Robert|||Robert Bravery (me@.u.com) writes:
> Yes I do get @.claimid from the inserted table. But it will alwaus be only
> one record that is update. Just the nature of the operation.
That's alright. But add an assertion to state that this is the case, to
prevent accidents.

> Ok so then I need to qualify some things in my mind.
> If a update trigger is ona table, and that table will always only have one
> row updated at a time, the update trigger would fire, there is now an
> inserted table, with the values on the update row in it. Now that trigger
> then updates another table.
> Do you no get a different inserted table, or is the same inserted table
> the one created by the original insert, used.
That's a different one. "inserted" and "deleted" are virtual tables, and
they are only visible within the trigger, nowhere else. So there is no
risk for confusion.

> What I basically whant to do is that the update trigger on claims,
> updates another table, which has say 4 rowse to update. Those four rows
> are related to claim table. After that related table and its four rows
> are updates, I want to perform and agregate check. All this will do is
> to notify the user of an above or below threshold.
I'm not sure that I understand exactly. I assumed that the PRINT was part
of some debug output. Are you now saying that the output is intended for
user consumption?
Generally, triggers are not supposed to generate any output (save for
error messages from integrity checks). It's perfectly legal to so, but
generally client code usually see INSERT, UPDATE, DELETE as operations
that do not delete data. Of course, if you code your application
accordingly, you can have output from the trigger. Still, I would consider
a different solution, if this is the case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||HI Erland,
> That's a different one. "inserted" and "deleted" are virtual tables, and
> they are only visible within the trigger, nowhere else. So there is no
> risk for confusion.
Ok thanks, that makes it easier to understand

> I'm not sure that I understand exactly. I assumed that the PRINT was part
> of some debug output. Are you now saying that the output is intended for
> user consumption?
I did put it there for degug purposes, because I could not get what I wanted
correctly

> Generally, triggers are not supposed to generate any output (save for
> error messages from integrity checks). It's perfectly legal to so, but
> generally client code usually see INSERT, UPDATE, DELETE as operations
> that do not delete data. Of course, if you code your application
> accordingly, you can have output from the trigger. Still, I would consider
> a different solution, if this is the case.
Basically the output would only be a type of informational message
ok in psudo code
the SP would update a table called liability, based on amounts entered into
the claim table.
After the SP has update the needed rows, I would like, to perhaps call
another SP, so
declare @.liab numeric(12,2), @.ded numeric(12,2)
select @.liab=sum(ammount) from claimliability where claimid = 10
select @.ded = ammount from ClaimDeductables where claimid = 10 and clmdedid=
70
if @.liab<@.ded
raiserror('Claim does not partake in SIP', 10,1)
Is this feasable, or am I going about it the wrong way
Thanks
Robert|||HI,
No the Print statement, debug for the time being, is in the trigger and is
after the exec of the SO
Thanks
RObert
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0E2F2CE4-C6DD-496C-8200-73F3BDDFC489@.microsoft.com...
> Does the procedure end with that PRINT statement?
>
> ML
> --
> http://milambda.blogspot.com/|||Since I can't see the entire code used in the process you're debugging, try
putting a PRINT statement at the end of each procedure - e.g. "print
'<procedure name> done."
Are there more triggers?
ML
http://milambda.blogspot.com/|||>The SP, by nature update multiple rows in child tables, as A result I would
>expect the following:
>(1 row(s) affected)
>(1 row(s) affected)
>(4 row(s) affected)
>But with the print statement I am gettingh
>(1 row(s) affected)
>(1 row(s) affected)
>Check SIP
>(4 row(s) affected)
>Should'nt the Check sip statement come last
I have the ouput of PRINT appear out of order relative to the retults
from SELECT (or UPDATE or INSERT) all the time. They apparently are
buffered independently. This is annoying, and I have never seen it
documented, but it is expected and nothing to worry about.
Roy|||HI Roy,
Thanks. THats reassuring
Robert
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:sirpv1duj8up6ssb9as694g5fpntjhq9li@.
4ax.com...
would
> I have the ouput of PRINT appear out of order relative to the retults
> from SELECT (or UPDATE or INSERT) all the time. They apparently are
> buffered independently. This is annoying, and I have never seen it
> documented, but it is expected and nothing to worry about.
> Roy

Question about performance

I'm using a tableadapter for my update operations.

The application i'm using typically creates 2500 dirty rows for a single table and then updates using the tableadapter.Update(). The tableadapter.update() method takes about 50 seconds to complete, which is way too long.

This is the only query running agaist the sql server 2000 database ( as this is a test server only), so i'm not sure if it is a problem with sql server or a problem with the tableadapter. Can anyone recomment how to improve performance for updating with the tableadapter and sql 2000?

Thanks in advance.

How big is each row? Was the CPU fully utilized? Is the client remote or local? For performance related issue, if you can describe your configuration and share out the code, it will be helpful.|||

The cpu isn't fully being utilzed on the sql server machine.

Here is the exact process:

There are 3 tables for this process. I basically create the information inside the dataset, and then postback to the server with all changes. Some of those changes are creating new rows and retrieving identities as well.

Example schema:

Table1 ( ID1 (identity, pk), 3 misc rows)

Table2 ( ID2 (Identity, pk,), ID1 (ForiegnKey)

Table 3 ( ID2 (ForigneKey) )

So, Table1 is parent to Table2 and Table3 is parent to table 2.

-

The user will create a row in Table1. The ID of Table1 is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. The user will then create on average 300 rows in table 2. Table2's ID is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. Next, the user creates 6 rows (on average) for every ID in Table2 ( 6 * 300).

So, then when you update this dataset, you're basically udpating Table1, getting the ID's from the database and cascading them down to Table2. Table2 will update and recieve it's true identities and then pass it down to table 3. Or something like that. So, it takes about a minute for this to update... Is my methodology fuzzy?

Please let me know if you need anymore detail!

|||

I still haven't solved this problem and maybe there is no solution. But I'll try one more time to clarify.

http://www.condoresorts.com/sample.jpg

Ok, so here is the process:

1. A user will create a time span that will be entered into the RoomInfo table. The start date is stored in Arrival column and end date stored in Departure column.

2. The time span which is stored in RoomInfo (Departure date - Arrival Date) will get expanded for each day and will be stored in Room_days.

3. Then the individual days for a timespan can then be assigned one to many people in the RoomDay_Customer table.

* RoomInfo and Room_Days primary key is an identity.

The problem is if you enter a timespan that spans more then 6 months, the update is really slow using the tableadapter.Update method.

A six month time span creates the following data:

1 entry in RoomInfo

180 entries in Room_Days ( on average)

540 entries in RoomDay_Customer ( for 3 people for example)

The only bottleneck I can think of is the identities. Is there a more efficenet way to update a typed dataset aside from calling its update method?

Question about performance

I'm using a tableadapter for my update operations.

The application i'm using typically creates 2500 dirty rows for a single table and then updates using the tableadapter.Update(). The tableadapter.update() method takes about 50 seconds to complete, which is way too long.

This is the only query running agaist the sql server 2000 database ( as this is a test server only), so i'm not sure if it is a problem with sql server or a problem with the tableadapter. Can anyone recomment how to improve performance for updating with the tableadapter and sql 2000?

Thanks in advance.

How big is each row? Was the CPU fully utilized? Is the client remote or local? For performance related issue, if you can describe your configuration and share out the code, it will be helpful.|||

The cpu isn't fully being utilzed on the sql server machine.

Here is the exact process:

There are 3 tables for this process. I basically create the information inside the dataset, and then postback to the server with all changes. Some of those changes are creating new rows and retrieving identities as well.

Example schema:

Table1 ( ID1 (identity, pk), 3 misc rows)

Table2 ( ID2 (Identity, pk,), ID1 (ForiegnKey)

Table 3 ( ID2 (ForigneKey) )

So, Table1 is parent to Table2 and Table3 is parent to table 2.

-

The user will create a row in Table1. The ID of Table1 is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. The user will then create on average 300 rows in table 2. Table2's ID is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. Next, the user creates 6 rows (on average) for every ID in Table2 ( 6 * 300).

So, then when you update this dataset, you're basically udpating Table1, getting the ID's from the database and cascading them down to Table2. Table2 will update and recieve it's true identities and then pass it down to table 3. Or something like that. So, it takes about a minute for this to update... Is my methodology fuzzy?

Please let me know if you need anymore detail!

|||

I still haven't solved this problem and maybe there is no solution. But I'll try one more time to clarify.

http://www.condoresorts.com/sample.jpg

Ok, so here is the process:

1. A user will create a time span that will be entered into the RoomInfo table. The start date is stored in Arrival column and end date stored in Departure column.

2. The time span which is stored in RoomInfo (Departure date - Arrival Date) will get expanded for each day and will be stored in Room_days.

3. Then the individual days for a timespan can then be assigned one to many people in the RoomDay_Customer table.

* RoomInfo and Room_Days primary key is an identity.

The problem is if you enter a timespan that spans more then 6 months, the update is really slow using the tableadapter.Update method.

A six month time span creates the following data:

1 entry in RoomInfo

180 entries in Room_Days ( on average)

540 entries in RoomDay_Customer ( for 3 people for example)

The only bottleneck I can think of is the identities. Is there a more efficenet way to update a typed dataset aside from calling its update method?

Question about Insert/Update/Delete to return fail/success

I want the stored procedure of insert/update/delete
can return fail/success to asp.net , how can i do thatJoe
CREATE PROCEDURE Proc1 AS
BEGIN TRANSACTION
--UPDATE Table1
--INSERT INTO Table1
--DELETE Table1
SET @.Err = @.@.ERROR
IF @.Err <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN (@.Err)
END
COMMIT TRANSACTION
--usage
EXEC @.Ret = Proc1
SELECT @.Ret = coalesce(nullif(@.Ret, 0),@.@.error,1001)
IF @.Ret <> 0
BEGIN
blabalabalabala --RAISERROR ('An error occured ........,10,1)
END
"joe" <joe@.discussions.microsoft.com> wrote in message
news:522DBA35-DFFF-4505-AAB5-10290E059279@.microsoft.com...
>I want the stored procedure of insert/update/delete
> can return fail/success to asp.net , how can i do that

Question about GridView update query

I try to calculate a field value and update other. It almost works

UPDATE t_Shopping_cart SET [Product_code] = @.Product_code, [Quantity] = @.Quantity, [Total] = Quantity * Price WHERE ([Product_code] = @.Product_code)

I try to update t_Shopping_cart table with quantity values from grid view and calculate sum of products (quantity*Price). Best I can get is that quantity values are old. Some comments about my query:Price is also a field in table and control. [Product_code] = @.Product_code part is probably not needed.

Any ideas?

Leif

So there is a column in t_Shopping_cart with the name Price?
Then maybe you can use this:

UPDATE t_Shopping_cart SET [Quantity] = @.Quantity, [Total] = @.Quantity * Price WHERE ([Product_code] = @.Product_code)

|||

"So there is a column in t_Shopping_cart with the name Price?" Yes. I'll try that "UPDATE t_Shopping_cart SET [Quantity] = @.Quantity, [Total] = @.Quantity * Price WHERE ([Product_code] = @.Product_code)" tomorrow.

Thanks

Leif

|||

Np, feel free to get back to me if it doesn't work!

|||

Hi. A short question. Is this a good way to this. The more I read about ways to do this the more ways there seems to be calculating sub totals and probably checking duplicates too.

Regards

Leif

Question about best practices..

Given the need to update data, is it better to have 500 procs that update a
table as needed for a specific situation or given a table like:
create table dbo.People
(
ID int identity(1,1) not null,
LastAccessed datetime null,
FirstName varchar(20) not null,
LastName varchar(30) not null,
Address1 varchar(50) not null,
Address2 varchar(50) not null,
City varchar(50) not null,
State char(2) not null
)
to have a proc like this that can be used for any update to this table:
create procedure UpdatePeople
@.ID int,
@.LastAccessed datetime=null,
@.FirstName varchar(20)=null,
@.LastName varchar(30)=null,
@.Address1 varchar(50)=null,
@.Address2 varchar(50)=nul,
@.City varchar(50)=null,
@.State char(2)=null
as
set nocount on;
update dbo.People
set LastAccessed=isnull(@.LastAccessed,LastAccessed),
FirstName=isnull(@.FirstName, FirstName),
LastName=isnull(@.LastName, LastName),
Address1=isnull(@.Address1, Address1),
Address2=isnull(@.Address2, Address2),
City=isnull(@.City, City),
State=isnull(@.State,State)
where ID=@.ID
return @.@.error
GO
Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
developers throwing hundreds of procs at me when it seems unecessary!Personally, I would prefer to have a CRUD interface that takes all the
values out, and then updates all of the values when saving.
The problem with trying to preserve a few bytes like you are doing, is that
now you can't overwrite an existing value with NULL. Yes, you can pass
Address1 = '' but is that really the same thing as NULL? I don't think so,
but it really depends on overall requirements.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23NHluITgHHA.5052@.TK2MSFTNGP05.phx.gbl...
> Given the need to update data, is it better to have 500 procs that update
> a table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
>
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAccessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
>
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
>
>|||Also, typically if you are updating a person's info, you're not updating a
single value. e.g. if someone moves, you need to update address1, address2,
city, state, zip, etc.
Do you really want to manage this set of procedures, and call them all
individually? I wouldn't:
dbo.Person_UpdateAddress1
dbo.Person_UpdateAddress2
dbo.Person_UpdateCity
dbo.Person_UpdateState
dbo.Person_UpdateZip
dbo.Person_UpdatePhone
dbo.Person_UpdateFax
...
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||On 17 Apr, 21:51, "Tim Greenwood" <tim_greenwood AT yahoo DOT com>
wrote:
> Given the need to update data, is it better to have 500 procs that update a
> table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAccessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
If you regularly need to update some small subset of columns then it
may be worth creating a separate proc for such a case. On grounds of
maintainability I would question the value of creating 500 such procs
unless it's essential to squeeze every last ounce of performance from
the database.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||That's exactly my point...no I wouldn't want to but that is exactly what I
keep getting from developers. I'd prefer CRUD interface as well but I'm
lacking in support right now. Unfortunately all developers have to pass
their code through me for verification as dba before it goes into final QA
environment.
So other than the idea of supporting only modified values the basic idea
here seems right on? IOW get rid of the whole isnull() on the updates?
The only problem we run into with that is if they've used a custom view or
proc that only queries a subset of data from multiple tables then they don't
have all the values to supply to a CRUD proc for updating...I know these
are extremely basic issues here, I'm just playing devils advocate. I have
some authority to leverage in the enforcement of these things but want to be
sure I'm coming from a best (or at least accepted) practices.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23RO3DPTgHHA.1220@.TK2MSFTNGP03.phx.gbl...
> Also, typically if you are updating a person's info, you're not updating a
> single value. e.g. if someone moves, you need to update address1,
> address2, city, state, zip, etc.
> Do you really want to manage this set of procedures, and call them all
> individually? I wouldn't:
> dbo.Person_UpdateAddress1
> dbo.Person_UpdateAddress2
> dbo.Person_UpdateCity
> dbo.Person_UpdateState
> dbo.Person_UpdateZip
> dbo.Person_UpdatePhone
> dbo.Person_UpdateFax
> ...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>|||> So other than the idea of supporting only modified values the basic idea
> here seems right on? IOW get rid of the whole isnull() on the updates?
> The only problem we run into with that is if they've used a custom view or
> proc that only queries a subset of data from multiple tables then they
> don't have all the values to supply to a CRUD proc for updating...I know
> these are extremely basic issues here, I'm just playing devils advocate.
> I have some authority to leverage in the enforcement of these things but
> want to be sure I'm coming from a best (or at least accepted) practices.
We enforce that they get all the details from a generic _GetDetails
procedure if their intention is to update even only one of the values.
If they are just getting the data for display, then yes I could see why they
might argue that they only want a subset of the data.
But with the memory on servers these days, there is no reason why the app
can't store the whole row in memory. Or, in cases like a two-column report,
that segment of the code can just ignore the other columns.
The trade-off here is performance vs. maintenance. You need to make that
decision... we can't tell you what's best because we don't know what it will
take to convince your developers to do it your way, and we don't know what
the performance threshold is (e.g. when does pulling/updating a subset
really change the way the app behaves).
A|||I have developed a WhichFieldsUsed mechanism that enables a single sproc to
only update the field or fields in a table that the caller wishes to have
updated, and then only if they are different from the existing values. Drop
me an email if you are interested. kgboles a t earth link d o t net.
--
TheSQLGuru
President
Indicium Resources, Inc.|||I already know *how* to do that. My question wasn't so much how to do it as
what is the most accepted practice.
Thanks!
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23eMdKFfgHHA.5044@.TK2MSFTNGP05.phx.gbl...
>I have developed a WhichFieldsUsed mechanism that enables a single sproc to
>only update the field or fields in a table that the caller wishes to have
>updated, and then only if they are different from the existing values.
>Drop me an email if you are interested. kgboles a t earth link d o t net.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>

Question about best practices..

Given the need to update data, is it better to have 500 procs that update a
table as needed for a specific situation or given a table like:
create table dbo.People
(
ID int identity(1,1) not null,
LastAccessed datetime null,
FirstName varchar(20) not null,
LastName varchar(30) not null,
Address1 varchar(50) not null,
Address2 varchar(50) not null,
City varchar(50) not null,
State char(2) not null
)
to have a proc like this that can be used for any update to this table:
create procedure UpdatePeople
@.ID int,
@.LastAccessed datetime=null,
@.FirstName varchar(20)=null,
@.LastName varchar(30)=null,
@.Address1 varchar(50)=null,
@.Address2 varchar(50)=nul,
@.City varchar(50)=null,
@.State char(2)=null
as
set nocount on;
update dbo.People
set LastAccessed=isnull(@.LastAccessed,LastAc
cessed),
FirstName=isnull(@.FirstName, FirstName),
LastName=isnull(@.LastName, LastName),
Address1=isnull(@.Address1, Address1),
Address2=isnull(@.Address2, Address2),
City=isnull(@.City, City),
State=isnull(@.State,State)
where ID=@.ID
return @.@.error
GO
Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
developers throwing hundreds of procs at me when it seems unecessary!Personally, I would prefer to have a CRUD interface that takes all the
values out, and then updates all of the values when saving.
The problem with trying to preserve a few bytes like you are doing, is that
now you can't overwrite an existing value with NULL. Yes, you can pass
Address1 = '' but is that really the same thing as NULL? I don't think so,
but it really depends on overall requirements.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23NHluITgHHA.5052@.TK2MSFTNGP05.phx.gbl...
> Given the need to update data, is it better to have 500 procs that update
> a table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
>
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAc
cessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
>
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
>
>|||Also, typically if you are updating a person's info, you're not updating a
single value. e.g. if someone moves, you need to update address1, address2,
city, state, zip, etc.
Do you really want to manage this set of procedures, and call them all
individually? I wouldn't:
dbo.Person_UpdateAddress1
dbo.Person_UpdateAddress2
dbo.Person_UpdateCity
dbo.Person_UpdateState
dbo.Person_UpdateZip
dbo.Person_UpdatePhone
dbo.Person_UpdateFax
...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||On 17 Apr, 21:51, "Tim Greenwood" <tim_greenwood AT yahoo DOT com>
wrote:
> Given the need to update data, is it better to have 500 procs that update
a
> table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAc
cessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
If you regularly need to update some small subset of columns then it
may be worth creating a separate proc for such a case. On grounds of
maintainability I would question the value of creating 500 such procs
unless it's essential to squeeze every last ounce of performance from
the database.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||That's exactly my point...no I wouldn't want to but that is exactly what I
keep getting from developers. I'd prefer CRUD interface as well but I'm
lacking in support right now. Unfortunately all developers have to pass
their code through me for verification as dba before it goes into final QA
environment.
So other than the idea of supporting only modified values the basic idea
here seems right on? IOW get rid of the whole isnull() on the updates?
The only problem we run into with that is if they've used a custom view or
proc that only queries a subset of data from multiple tables then they don't
have all the values to supply to a CRUD proc for updating...I know these
are extremely basic issues here, I'm just playing devils advocate. I have
some authority to leverage in the enforcement of these things but want to be
sure I'm coming from a best (or at least accepted) practices.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23RO3DPTgHHA.1220@.TK2MSFTNGP03.phx.gbl...
> Also, typically if you are updating a person's info, you're not updating a
> single value. e.g. if someone moves, you need to update address1,
> address2, city, state, zip, etc.
> Do you really want to manage this set of procedures, and call them all
> individually? I wouldn't:
> dbo.Person_UpdateAddress1
> dbo.Person_UpdateAddress2
> dbo.Person_UpdateCity
> dbo.Person_UpdateState
> dbo.Person_UpdateZip
> dbo.Person_UpdatePhone
> dbo.Person_UpdateFax
> ...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>|||> So other than the idea of supporting only modified values the basic idea
> here seems right on? IOW get rid of the whole isnull() on the updates?
> The only problem we run into with that is if they've used a custom view or
> proc that only queries a subset of data from multiple tables then they
> don't have all the values to supply to a CRUD proc for updating...I know
> these are extremely basic issues here, I'm just playing devils advocate.
> I have some authority to leverage in the enforcement of these things but
> want to be sure I'm coming from a best (or at least accepted) practices.
We enforce that they get all the details from a generic _GetDetails
procedure if their intention is to update even only one of the values.
If they are just getting the data for display, then yes I could see why they
might argue that they only want a subset of the data.
But with the memory on servers these days, there is no reason why the app
can't store the whole row in memory. Or, in cases like a two-column report,
that segment of the code can just ignore the other columns.
The trade-off here is performance vs. maintenance. You need to make that
decision... we can't tell you what's best because we don't know what it will
take to convince your developers to do it your way, and we don't know what
the performance threshold is (e.g. when does pulling/updating a subset
really change the way the app behaves).
A|||I have developed a WhichFieldsUsed mechanism that enables a single sproc to
only update the field or fields in a table that the caller wishes to have
updated, and then only if they are different from the existing values. Drop
me an email if you are interested. kgboles a t earth link d o t net.
TheSQLGuru
President
Indicium Resources, Inc.|||I already know *how* to do that. My question wasn't so much how to do it as
what is the most accepted practice.
Thanks!
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23eMdKFfgHHA.5044@.TK2MSFTNGP05.phx.gbl...
>I have developed a WhichFieldsUsed mechanism that enables a single sproc to
>only update the field or fields in a table that the caller wishes to have
>updated, and then only if they are different from the existing values.
>Drop me an email if you are interested. kgboles a t earth link d o t net.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>

Question about best practices..

Given the need to update data, is it better to have 500 procs that update a
table as needed for a specific situation or given a table like:
create table dbo.People
(
ID int identity(1,1) not null,
LastAccessed datetime null,
FirstName varchar(20) not null,
LastName varchar(30) not null,
Address1 varchar(50) not null,
Address2 varchar(50) not null,
City varchar(50) not null,
State char(2) not null
)
to have a proc like this that can be used for any update to this table:
create procedure UpdatePeople
@.ID int,
@.LastAccessed datetime=null,
@.FirstName varchar(20)=null,
@.LastName varchar(30)=null,
@.Address1 varchar(50)=null,
@.Address2 varchar(50)=nul,
@.City varchar(50)=null,
@.State char(2)=null
as
set nocount on;
update dbo.People
set LastAccessed=isnull(@.LastAccessed,LastAccessed),
FirstName=isnull(@.FirstName, FirstName),
LastName=isnull(@.LastName, LastName),
Address1=isnull(@.Address1, Address1),
Address2=isnull(@.Address2, Address2),
City=isnull(@.City, City),
State=isnull(@.State,State)
where ID=@.ID
return @.@.error
GO
Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
developers throwing hundreds of procs at me when it seems unecessary!
Personally, I would prefer to have a CRUD interface that takes all the
values out, and then updates all of the values when saving.
The problem with trying to preserve a few bytes like you are doing, is that
now you can't overwrite an existing value with NULL. Yes, you can pass
Address1 = '' but is that really the same thing as NULL? I don't think so,
but it really depends on overall requirements.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23NHluITgHHA.5052@.TK2MSFTNGP05.phx.gbl...
> Given the need to update data, is it better to have 500 procs that update
> a table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
>
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAccessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
>
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
>
>
|||Also, typically if you are updating a person's info, you're not updating a
single value. e.g. if someone moves, you need to update address1, address2,
city, state, zip, etc.
Do you really want to manage this set of procedures, and call them all
individually? I wouldn't:
dbo.Person_UpdateAddress1
dbo.Person_UpdateAddress2
dbo.Person_UpdateCity
dbo.Person_UpdateState
dbo.Person_UpdateZip
dbo.Person_UpdatePhone
dbo.Person_UpdateFax
...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||On 17 Apr, 21:51, "Tim Greenwood" <tim_greenwood AT yahoo DOT com>
wrote:
> Given the need to update data, is it better to have 500 procs that update a
> table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAccessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
If you regularly need to update some small subset of columns then it
may be worth creating a separate proc for such a case. On grounds of
maintainability I would question the value of creating 500 such procs
unless it's essential to squeeze every last ounce of performance from
the database.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||That's exactly my point...no I wouldn't want to but that is exactly what I
keep getting from developers. I'd prefer CRUD interface as well but I'm
lacking in support right now. Unfortunately all developers have to pass
their code through me for verification as dba before it goes into final QA
environment.
So other than the idea of supporting only modified values the basic idea
here seems right on? IOW get rid of the whole isnull() on the updates?
The only problem we run into with that is if they've used a custom view or
proc that only queries a subset of data from multiple tables then they don't
have all the values to supply to a CRUD proc for updating...I know these
are extremely basic issues here, I'm just playing devils advocate. I have
some authority to leverage in the enforcement of these things but want to be
sure I'm coming from a best (or at least accepted) practices.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23RO3DPTgHHA.1220@.TK2MSFTNGP03.phx.gbl...
> Also, typically if you are updating a person's info, you're not updating a
> single value. e.g. if someone moves, you need to update address1,
> address2, city, state, zip, etc.
> Do you really want to manage this set of procedures, and call them all
> individually? I wouldn't:
> dbo.Person_UpdateAddress1
> dbo.Person_UpdateAddress2
> dbo.Person_UpdateCity
> dbo.Person_UpdateState
> dbo.Person_UpdateZip
> dbo.Person_UpdatePhone
> dbo.Person_UpdateFax
> ...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
|||> So other than the idea of supporting only modified values the basic idea
> here seems right on? IOW get rid of the whole isnull() on the updates?
> The only problem we run into with that is if they've used a custom view or
> proc that only queries a subset of data from multiple tables then they
> don't have all the values to supply to a CRUD proc for updating...I know
> these are extremely basic issues here, I'm just playing devils advocate.
> I have some authority to leverage in the enforcement of these things but
> want to be sure I'm coming from a best (or at least accepted) practices.
We enforce that they get all the details from a generic _GetDetails
procedure if their intention is to update even only one of the values.
If they are just getting the data for display, then yes I could see why they
might argue that they only want a subset of the data.
But with the memory on servers these days, there is no reason why the app
can't store the whole row in memory. Or, in cases like a two-column report,
that segment of the code can just ignore the other columns.
The trade-off here is performance vs. maintenance. You need to make that
decision... we can't tell you what's best because we don't know what it will
take to convince your developers to do it your way, and we don't know what
the performance threshold is (e.g. when does pulling/updating a subset
really change the way the app behaves).
A
|||I have developed a WhichFieldsUsed mechanism that enables a single sproc to
only update the field or fields in a table that the caller wishes to have
updated, and then only if they are different from the existing values. Drop
me an email if you are interested. kgboles a t earth link d o t net.
TheSQLGuru
President
Indicium Resources, Inc.
|||I already know *how* to do that. My question wasn't so much how to do it as
what is the most accepted practice.
Thanks!
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23eMdKFfgHHA.5044@.TK2MSFTNGP05.phx.gbl...
>I have developed a WhichFieldsUsed mechanism that enables a single sproc to
>only update the field or fields in a table that the caller wishes to have
>updated, and then only if they are different from the existing values.
>Drop me an email if you are interested. kgboles a t earth link d o t net.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>

Question about auto update statistics and stored proc recompilation

I am not clear on the relationship between statistics being updated and
stored procedures being recomipled. I beleive that when when auto stats
is on then a stored procedure will be recompiled when statistics are
updated on a table that the stored procedure accesses.
However I have auto stats turned off for a number of tables and instead
update the statistics on a regular bases depending on certain data
characteristics of the table.
So I am not clear on whether the recompilation is triggered by the
updating of the statistics or by the data modification in the table. In
other words will a stored prodedure still be recompiled when are
certain number of modifications are made to the table even if I have
auto stats turned off or will it only be recompiled when I manually
update the statistics?
Thanks!There is a very good white paper that covers both SQL 2000 and SQL 2005
compilation/recompilation at
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Hope this helps.
Dan Guzman
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1158954729.540099.201400@.i3g2000cwc.googlegroups.com...
>I am not clear on the relationship between statistics being updated and
> stored procedures being recomipled. I beleive that when when auto stats
> is on then a stored procedure will be recompiled when statistics are
> updated on a table that the stored procedure accesses.
> However I have auto stats turned off for a number of tables and instead
> update the statistics on a regular bases depending on certain data
> characteristics of the table.
> So I am not clear on whether the recompilation is triggered by the
> updating of the statistics or by the data modification in the table. In
> other words will a stored prodedure still be recompiled when are
> certain number of modifications are made to the table even if I have
> auto stats turned off or will it only be recompiled when I manually
> update the statistics?
> Thanks!
>

Question about auto update statistics and stored proc recompilation

I am not clear on the relationship between statistics being updated and
stored procedures being recomipled. I beleive that when when auto stats
is on then a stored procedure will be recompiled when statistics are
updated on a table that the stored procedure accesses.
However I have auto stats turned off for a number of tables and instead
update the statistics on a regular bases depending on certain data
characteristics of the table.
So I am not clear on whether the recompilation is triggered by the
updating of the statistics or by the data modification in the table. In
other words will a stored prodedure still be recompiled when are
certain number of modifications are made to the table even if I have
auto stats turned off or will it only be recompiled when I manually
update the statistics?
Thanks!There is a very good white paper that covers both SQL 2000 and SQL 2005
compilation/recompilation at
http://www.microsoft.com/technet/pr...005/recomp.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1158954729.540099.201400@.i3g2000cwc.googlegroups.com...
>I am not clear on the relationship between statistics being updated and
> stored procedures being recomipled. I beleive that when when auto stats
> is on then a stored procedure will be recompiled when statistics are
> updated on a table that the stored procedure accesses.
> However I have auto stats turned off for a number of tables and instead
> update the statistics on a regular bases depending on certain data
> characteristics of the table.
> So I am not clear on whether the recompilation is triggered by the
> updating of the statistics or by the data modification in the table. In
> other words will a stored prodedure still be recompiled when are
> certain number of modifications are made to the table even if I have
> auto stats turned off or will it only be recompiled when I manually
> update the statistics?
> Thanks!
>

Question about auto update statistics and stored proc recompilation

I am not clear on the relationship between statistics being updated and
stored procedures being recomipled. I beleive that when when auto stats
is on then a stored procedure will be recompiled when statistics are
updated on a table that the stored procedure accesses.
However I have auto stats turned off for a number of tables and instead
update the statistics on a regular bases depending on certain data
characteristics of the table.
So I am not clear on whether the recompilation is triggered by the
updating of the statistics or by the data modification in the table. In
other words will a stored prodedure still be recompiled when are
certain number of modifications are made to the table even if I have
auto stats turned off or will it only be recompiled when I manually
update the statistics?
Thanks!
There is a very good white paper that covers both SQL 2000 and SQL 2005
compilation/recompilation at
http://www.microsoft.com/technet/pro...05/recomp.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1158954729.540099.201400@.i3g2000cwc.googlegro ups.com...
>I am not clear on the relationship between statistics being updated and
> stored procedures being recomipled. I beleive that when when auto stats
> is on then a stored procedure will be recompiled when statistics are
> updated on a table that the stored procedure accesses.
> However I have auto stats turned off for a number of tables and instead
> update the statistics on a regular bases depending on certain data
> characteristics of the table.
> So I am not clear on whether the recompilation is triggered by the
> updating of the statistics or by the data modification in the table. In
> other words will a stored prodedure still be recompiled when are
> certain number of modifications are made to the table even if I have
> auto stats turned off or will it only be recompiled when I manually
> update the statistics?
> Thanks!
>

Question about "WITH (ROWLOCK)" hint.

(SQL Server 2000, SP3a)
Hello all!
I've got a trigger on a table that I want to cause a column in another table to be
updated:
update t
set t.[DateLastUpdated] = getdate()
from [dbo].[MyOtherTable] as t with (rowlock)
inner join inserted as i with (nolock) on i.[PK] = t.[PK]
Is the WITH (ROWLOCK) hint any different if I express it this way:
update t with (rowlock)
set t.[DateLastUpdated] = getdate()
from [dbo].[MyOtherTable] as t
inner join inserted as i with (nolock) on i.[PK] = t.[PK]
My guess is that it isn't (since the compiler will warn you if there are incompatible lock
types between the "alias" and the table) -- so I just thought it'd be the same. But, I'm
not really sure and was hoping for clarification.
Thanks for any help you can provide!
John PetersonJohn,
I don't believe so but your example is a bad one<g>. First off you don't
need to issue a NOLOCK on the Inserted table since the session doing the
updating is the only one that has access to it. I don't know if it even
does anything to tell you the truth. And the ROWLOCK should not be needed
either. Your joining on a PK and unless your updating most of the table it
should use rowlocks anyway. Hints should only be used where absolutely
necessary. You need to prove to yourself they are needed before using them
otherwise you risk getting poor performance overall.
--
Andrew J. Kelly
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:utDiYXb8DHA.488@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I've got a trigger on a table that I want to cause a column in another
table to be
> updated:
> update t
> set t.[DateLastUpdated] = getdate()
> from [dbo].[MyOtherTable] as t with (rowlock)
> inner join inserted as i with (nolock) on i.[PK] = t.[PK]
> Is the WITH (ROWLOCK) hint any different if I express it this way:
> update t with (rowlock)
> set t.[DateLastUpdated] = getdate()
> from [dbo].[MyOtherTable] as t
> inner join inserted as i with (nolock) on i.[PK] = t.[PK]
> My guess is that it isn't (since the compiler will warn you if there are
incompatible lock
> types between the "alias" and the table) -- so I just thought it'd be the
same. But, I'm
> not really sure and was hoping for clarification.
> Thanks for any help you can provide!
> John Peterson
>|||> from [dbo].[MyOtherTable] as t with (rowlock)
What do you think you're gaining here?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hello Andrew!
Thanks -- yeah, I figured my NOLOCK hint was likely superfluous. The table that being
updated is a fairly high-use table, and explicitly providing a ROWLOCK hint has been
helpful in the past. But, your words of caution are appreciated!
Thanks for your help!
John Peterson
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OYCc4xb8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> John,
> I don't believe so but your example is a bad one<g>. First off you don't
> need to issue a NOLOCK on the Inserted table since the session doing the
> updating is the only one that has access to it. I don't know if it even
> does anything to tell you the truth. And the ROWLOCK should not be needed
> either. Your joining on a PK and unless your updating most of the table it
> should use rowlocks anyway. Hints should only be used where absolutely
> necessary. You need to prove to yourself they are needed before using them
> otherwise you risk getting poor performance overall.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:utDiYXb8DHA.488@.TK2MSFTNGP12.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I've got a trigger on a table that I want to cause a column in another
> table to be
> > updated:
> >
> > update t
> > set t.[DateLastUpdated] = getdate()
> > from [dbo].[MyOtherTable] as t with (rowlock)
> > inner join inserted as i with (nolock) on i.[PK] = t.[PK]
> >
> > Is the WITH (ROWLOCK) hint any different if I express it this way:
> >
> > update t with (rowlock)
> > set t.[DateLastUpdated] = getdate()
> > from [dbo].[MyOtherTable] as t
> > inner join inserted as i with (nolock) on i.[PK] = t.[PK]
> >
> > My guess is that it isn't (since the compiler will warn you if there are
> incompatible lock
> > types between the "alias" and the table) -- so I just thought it'd be the
> same. But, I'm
> > not really sure and was hoping for clarification.
> >
> > Thanks for any help you can provide!
> >
> > John Peterson
> >
> >
>|||"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23wEOE0b8DHA.452@.TK2MSFTNGP11.phx.gbl...
> > from [dbo].[MyOtherTable] as t with (rowlock)
> What do you think you're gaining here?
I had hoped to mitigate the possibility of conversion deadlocks with this approach
(deadlocks that are caused due to row-->page lock escalation). I was just curious to
learn whether specifying the hint at one or the other locations might have any difference.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>