473,320 Members | 1,867 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Blocking and return parameters

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
6 2571

"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

"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
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
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

"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

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: David Sworder | last post by:
This message was already cross-posted to C# and ADO.NET, but I forgot to post to this "general" group... sorry about that. It just occured to me after my first post that the "general" group readers...
3
by: Mario | last post by:
Hello, I couldn't find a solution to the following problem (tried google and dejanews), maybe I'm using the wrong keywords? Is there a way to open a file (a linux fifo pipe actually) in...
23
by: David McCulloch | last post by:
QUESTION-1: How can I detect if Norton Internet Security is blocking pop-ups? QUESTION-2a: How could I know if a particular JavaScript function has been declared? QUESTION-2b: How could I...
2
by: Rene | last post by:
Hi, In my VB6 application I'm using a class/object that is using full-async ADO. I can start multiple queries, the class stores the ADODB.Recordset object in an array and waits for the...
20
by: Charles Law | last post by:
Consider the following scenario: A data packet is sent out of a serial port and a return packet is expected a short time later. The application sending the packet needs to send another packet...
3
by: loosecannon_1 | last post by:
I get a 90-120 second blocking when send 15 or so simultaneous queries to SQL Server 2000 that query a view made up of two joined tables. After each query is blocking for the same amount of time...
6
by: placid | last post by:
Hi all, I have been looking into non-blocking read (readline) operations on PIPES on windows XP and there seems to be no way of doing this. Ive read that you could use a Thread to read from the...
1
by: Antoon Pardon | last post by:
In the RawIOBase class I read the following: ..read(n: int) -bytes Read up to n bytes from the object and return them. Fewer than n bytes may be returned if the operating system call returns...
3
by: Panda | last post by:
Hi All, Any help greatly appreciated before I finish pulling the rest of my hair out. I'm making a program that allows a user to record their hours. There are five rows (monday to friday)....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.