473,543 Members | 3,588 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 2588

"Lauren Quantrell" <la************ *@hotmail.com> wrote in message
news:47******** *************** ***@posting.goo gle.com...
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.goo gle.com...
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,fld 23, 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 tblAppointmentH istory */

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

"Simon Hayes" <sq*@hayes.ch > wrote in message news:<40******* ***@news.bluewi n.ch>...
"Lauren Quantrell" <la************ *@hotmail.com> wrote in message
news:47******** *************** ***@posting.goo gle.com...
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,fld 23, 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 tblAppointmentH istory */

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

"Simon Hayes" <sq*@hayes.ch > wrote in message news:<40******* ***@news.bluewi n.ch>...
"Lauren Quantrell" <la************ *@hotmail.com> wrote in message
news:47******** *************** ***@posting.goo gle.com...
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.goo gle.com...
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.goo gle.com...
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
2193
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 might have some thoughts on this perplexing .NET blocking issue. (see below) ===== Hi,
3
12196
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 nonblocking mode in c++? I did something ugly like --- c/c++ mixture --- mkfifo( "testpipe", 777);
23
6476
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 know if Window.Open has been redefined? BACKGROUND:
2
2248
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 QueryComplete event. This will set the result and flag 'the query is finished' in the array. In my WaitForResult() method I wait till the flag 'query is...
20
1998
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 as soon as the return packet has been received. It needs to wait for the return packet, but time out if one is not received. Whilst packets are...
3
1741
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 they all return. Further identical queries of this type work in 3-4 seconds (caching?) until hours later where it happens again. If I query the...
6
6200
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 pipe, but if you still use readline() wouldnt the Thread block too? What i need to do is, create a process using subprocess.Popen, where the...
1
1088
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 fewer than n bytes. If 0 bytes are returned, this indicates end of file. If the object is in non-blocking mode and no bytes are available, the call...
3
1292
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). When a user clicks submit the programme checks if the row has got user data. If it does it checks if that day already has data held against it and...
0
7402
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
1
7344
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7684
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5883
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5264
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4890
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3388
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1814
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
633
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.