By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,532 Members | 1,514 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,532 IT Pros & Developers. It's quick & easy.

Blocking and return parameters

P: n/a
Is it possible the existence of a return parameter on an SP running
insert statements would cause blocking to happen (any removing it
might help eliminate it?)
The SPs in question have three INSERT INTO statements using an
@@IDENTITY to help populate the second and third tables based on the
PK of the newly inserted record in the first INSERT INTO Statement.
I have recently stared to receive timeout errors in a multi-user
enviroment and wonder if that's part of the cause. In this
environment, users are requerying a table that is also being written
to. The writes are infrequent but the reads of that table are very
frequent.
On the read side I have added Select from TableX WITH (READPAST)...
On the write side I have elimnated the return parameters. I'm hoping
these two approaches help.
Help is greatly appreciated.
lq
Jul 20 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a

"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...
Is it possible the existence of a return parameter on an SP running
insert statements would cause blocking to happen (any removing it
might help eliminate it?)
The SPs in question have three INSERT INTO statements using an
@@IDENTITY to help populate the second and third tables based on the
PK of the newly inserted record in the first INSERT INTO Statement.
I have recently stared to receive timeout errors in a multi-user
enviroment and wonder if that's part of the cause. In this
environment, users are requerying a table that is also being written
to. The writes are infrequent but the reads of that table are very
frequent.
On the read side I have added Select from TableX WITH (READPAST)...
On the write side I have elimnated the return parameters. I'm hoping
these two approaches help.
Help is greatly appreciated.
lq


I'm not entirely sure I follow your description - stored procedures can have
return values, and output parameters, but not "return parameters" so I'm not
clear as to which one you mean here.

As a general comment, if your three INSERTs are inside a transaction, and
that transaction runs for too long, or requires a lot of locks, then it's
possible that the readers will be affected. But without some more
information, such as the piece of code containing the INSERTs, and some
background information about the tables, it's hard to say.

By the way, READPAST is potentially dangerous, because it means the client
will not see data which it should normally see, so the results returned may
be incomplete. Locking hints are not often required, so you should try other
solutions first.

Simon
Jul 20 '05 #2

P: n/a

"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...
Is it possible the existence of a return parameter on an SP running
insert statements would cause blocking to happen (any removing it
might help eliminate it?)
The SPs in question have three INSERT INTO statements using an
@@IDENTITY to help populate the second and third tables based on the
PK of the newly inserted record in the first INSERT INTO Statement.
I have recently stared to receive timeout errors in a multi-user
enviroment and wonder if that's part of the cause. In this
environment, users are requerying a table that is also being written
to. The writes are infrequent but the reads of that table are very
frequent.
On the read side I have added Select from TableX WITH (READPAST)...
On the write side I have elimnated the return parameters. I'm hoping
these two approaches help.
Help is greatly appreciated.
lq


I'm not entirely sure I follow your description - stored procedures can have
return values, and output parameters, but not "return parameters" so I'm not
clear as to which one you mean here.

As a general comment, if your three INSERTs are inside a transaction, and
that transaction runs for too long, or requires a lot of locks, then it's
possible that the readers will be affected. But without some more
information, such as the piece of code containing the INSERTs, and some
background information about the tables, it's hard to say.

By the way, READPAST is potentially dangerous, because it means the client
will not see data which it should normally see, so the results returned may
be incomplete. Locking hints are not often required, so you should try other
solutions first.

Simon
Jul 20 '05 #3

P: n/a
Simon,
Thank you for your response. I meant to write that in VBA I have
appended an Output Parameters to the prm collection.

Here is the entire SP:

*** START SP CODE:

Alter Procedure "mySPName"
@par1 nvarchar(15) = null,
@par2 int = null,
@par3 nvarchar(50) = null,
@par4 nvarchar(50) = null,
@par5 nvarchar(125) = null,
@par6 smallint = null,
@par7 ntext = null,
@par8 smalldatetime = null,
@par9 smalldatetime = null,
@par10 smalldatetime = null,
@par11 smalldatetime = null,
@par12 datetime = null,
@par13 datetime = null,
@par14 smallint = null,
@par15 smalldatetime = null,
@par16 tinyint = null,
@par17 tinyint = null,
@par18 tinyint = null,
@par19 nvarchar(100) = null,
@par20 int = null,
@par21 int = null,
@par22 int = null,
@PKID int OUTPUT

AS
set nocount on
set xact_abort on

/* Insert new event into tblAppointments */

INSERT INTO
tblAppointments (fld1, fld2, fld3, fld4, fld5, fld6, fld7, fld8,
fld9, fld10, fld11, fld12, fld13, fld14, fld15, fld16, fld17, fld18,
fld19, fld20, fld21,fld22,fld23, fld24)
VALUES
(@par1, @par2, @par3, @par4, @par5, @par6, @par7,
@par8, @par9, @par10, @par12,@par13, @par14, @par15, @par16, @par17,
@par18,
@par19, @par20, @par21,@par22, @par22,@par18, @par18)

/* Note: Some columns above receive data from the same parameters */

SELECT @PKID = @@IDENTITY

/* Create an entry in tblAppointmentHistory */

Insert Into
tblAppointmentsHistory (fldA, fldB, fldC, fldD, fldE)
Values
(@PKID, @par2, @par3,'New Entry','Appt Type = ' + @par4 + ' ;
AppTitle= ' + @par5 + ' ; AppDescription= ' + isnull(Cast(@par7 AS
nvarchar(4000)),'<none>') + ' ; Location = ' + @par19 + ' ; Start
Date = ' + CAST(@par12 AS nvarchar(25)) + ' ; End Date = ' +
IsNull(CAST(@par13 AS nvarchar(25)),'<none'))
***END SP CODE

"Simon Hayes" <sq*@hayes.ch> wrote in message news:<40**********@news.bluewin.ch>...
"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...
Is it possible the existence of a return parameter on an SP running
insert statements would cause blocking to happen (any removing it
might help eliminate it?)
The SPs in question have three INSERT INTO statements using an
@@IDENTITY to help populate the second and third tables based on the
PK of the newly inserted record in the first INSERT INTO Statement.
I have recently stared to receive timeout errors in a multi-user
enviroment and wonder if that's part of the cause. In this
environment, users are requerying a table that is also being written
to. The writes are infrequent but the reads of that table are very
frequent.
On the read side I have added Select from TableX WITH (READPAST)...
On the write side I have elimnated the return parameters. I'm hoping
these two approaches help.
Help is greatly appreciated.
lq


I'm not entirely sure I follow your description - stored procedures can have
return values, and output parameters, but not "return parameters" so I'm not
clear as to which one you mean here.

As a general comment, if your three INSERTs are inside a transaction, and
that transaction runs for too long, or requires a lot of locks, then it's
possible that the readers will be affected. But without some more
information, such as the piece of code containing the INSERTs, and some
background information about the tables, it's hard to say.

By the way, READPAST is potentially dangerous, because it means the client
will not see data which it should normally see, so the results returned may
be incomplete. Locking hints are not often required, so you should try other
solutions first.

Simon

Jul 20 '05 #4

P: n/a
Simon,
Thank you for your response. I meant to write that in VBA I have
appended an Output Parameters to the prm collection.

Here is the entire SP:

*** START SP CODE:

Alter Procedure "mySPName"
@par1 nvarchar(15) = null,
@par2 int = null,
@par3 nvarchar(50) = null,
@par4 nvarchar(50) = null,
@par5 nvarchar(125) = null,
@par6 smallint = null,
@par7 ntext = null,
@par8 smalldatetime = null,
@par9 smalldatetime = null,
@par10 smalldatetime = null,
@par11 smalldatetime = null,
@par12 datetime = null,
@par13 datetime = null,
@par14 smallint = null,
@par15 smalldatetime = null,
@par16 tinyint = null,
@par17 tinyint = null,
@par18 tinyint = null,
@par19 nvarchar(100) = null,
@par20 int = null,
@par21 int = null,
@par22 int = null,
@PKID int OUTPUT

AS
set nocount on
set xact_abort on

/* Insert new event into tblAppointments */

INSERT INTO
tblAppointments (fld1, fld2, fld3, fld4, fld5, fld6, fld7, fld8,
fld9, fld10, fld11, fld12, fld13, fld14, fld15, fld16, fld17, fld18,
fld19, fld20, fld21,fld22,fld23, fld24)
VALUES
(@par1, @par2, @par3, @par4, @par5, @par6, @par7,
@par8, @par9, @par10, @par12,@par13, @par14, @par15, @par16, @par17,
@par18,
@par19, @par20, @par21,@par22, @par22,@par18, @par18)

/* Note: Some columns above receive data from the same parameters */

SELECT @PKID = @@IDENTITY

/* Create an entry in tblAppointmentHistory */

Insert Into
tblAppointmentsHistory (fldA, fldB, fldC, fldD, fldE)
Values
(@PKID, @par2, @par3,'New Entry','Appt Type = ' + @par4 + ' ;
AppTitle= ' + @par5 + ' ; AppDescription= ' + isnull(Cast(@par7 AS
nvarchar(4000)),'<none>') + ' ; Location = ' + @par19 + ' ; Start
Date = ' + CAST(@par12 AS nvarchar(25)) + ' ; End Date = ' +
IsNull(CAST(@par13 AS nvarchar(25)),'<none'))
***END SP CODE

"Simon Hayes" <sq*@hayes.ch> wrote in message news:<40**********@news.bluewin.ch>...
"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...
Is it possible the existence of a return parameter on an SP running
insert statements would cause blocking to happen (any removing it
might help eliminate it?)
The SPs in question have three INSERT INTO statements using an
@@IDENTITY to help populate the second and third tables based on the
PK of the newly inserted record in the first INSERT INTO Statement.
I have recently stared to receive timeout errors in a multi-user
enviroment and wonder if that's part of the cause. In this
environment, users are requerying a table that is also being written
to. The writes are infrequent but the reads of that table are very
frequent.
On the read side I have added Select from TableX WITH (READPAST)...
On the write side I have elimnated the return parameters. I'm hoping
these two approaches help.
Help is greatly appreciated.
lq


I'm not entirely sure I follow your description - stored procedures can have
return values, and output parameters, but not "return parameters" so I'm not
clear as to which one you mean here.

As a general comment, if your three INSERTs are inside a transaction, and
that transaction runs for too long, or requires a lot of locks, then it's
possible that the readers will be affected. But without some more
information, such as the piece of code containing the INSERTs, and some
background information about the tables, it's hard to say.

By the way, READPAST is potentially dangerous, because it means the client
will not see data which it should normally see, so the results returned may
be incomplete. Locking hints are not often required, so you should try other
solutions first.

Simon

Jul 20 '05 #5

P: n/a

"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...
Simon,
Thank you for your response. I meant to write that in VBA I have
appended an Output Parameters to the prm collection.

Here is the entire SP:


<snip>

At first glance, there doesn't seem to be anything obviously incorrect with
your procedure, assuming that you haven't removed anything significant from
it. Small issues are that you should probably use SCOPE_IDENTITY() instead
of @@IDENTITY (see Books Online), and you may want to review your use of SET
XACT_ABORT:

http://www.sommarskog.se/error-handl...tml#XACT_ABORT

To go back to your original timeout issue, why do you think the timeouts are
connected to this specific procedure? Have you monitored what's happening on
the server when timeouts occur, especially locking? You can use Profiler to
capture what's happening on the server, and this KB article may be useful:

http://support.microsoft.com/default...&Product=sql2k

Erland also has a tool for examining locks:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Simon
Jul 20 '05 #6

P: n/a

"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...
Simon,
Thank you for your response. I meant to write that in VBA I have
appended an Output Parameters to the prm collection.

Here is the entire SP:


<snip>

At first glance, there doesn't seem to be anything obviously incorrect with
your procedure, assuming that you haven't removed anything significant from
it. Small issues are that you should probably use SCOPE_IDENTITY() instead
of @@IDENTITY (see Books Online), and you may want to review your use of SET
XACT_ABORT:

http://www.sommarskog.se/error-handl...tml#XACT_ABORT

To go back to your original timeout issue, why do you think the timeouts are
connected to this specific procedure? Have you monitored what's happening on
the server when timeouts occur, especially locking? You can use Profiler to
capture what's happening on the server, and this KB article may be useful:

http://support.microsoft.com/default...&Product=sql2k

Erland also has a tool for examining locks:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Simon
Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.