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"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404110746.4de8d6d7@.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|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404110746.4de8d6d7@.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|||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" <sql@.hayes.ch> wrote in message news:<407af642$1_2@.news.bluewin.ch>...
> "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:47e5bd72.0404110746.4de8d6d7@.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|||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" <sql@.hayes.ch> wrote in message news:<407af642$1_2@.news.bluewin.ch>...
> "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:47e5bd72.0404110746.4de8d6d7@.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|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404121731.52fe5214@.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-hand...html#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/defaul...9&Product=sql2k
Erland also has a tool for examining locks:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
Simon|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404121731.52fe5214@.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-hand...html#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/defaul...9&Product=sql2k
Erland also has a tool for examining locks:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
Simon
No comments:
Post a Comment